MSSqlServer伪序列_MySQL

先创建一个序列表
if exists (select 1
from sysindexeswhere 
id = object_id(&＃39;TSysSequence&＃39;)and 
name = &＃39;In_SeName&＃39;and indid > 0and indid <255)drop 
index TSysSequence.In_SeNamegoif exists 
(select 1from sysobjectswhere id = object_id(&＃39;TSysSequence&＃39;)and type = &＃39;U&＃39;)drop table TSysSequencego
/*======================================*//* Table: TSysSequence *//*=======================================*/
create table TSysSequence (SeName nvarchar(50) not null,
Increment int not null default 1,CurVal bigint not null default 0)goif exists 
(select 1 from sys.extended_propertieswhere major_id = object_id(&＃39;TSysSequence&＃39;) 
and minor_id = 0)begindeclare @CurrentUser sysnameselect @CurrentUser = user_name()execute 
sp_dropextendedproperty &＃39;MS_Description&＃39;,&＃39;user&＃39;, @CurrentUser, &＃39;table&＃39;, &＃39;TSysSequence&＃39;endselect 
@CurrentUser = user_name()execute sp_addextendedproperty &＃39;MS_Description&＃39;,&＃39;

模拟oracle序列

不允许用户维护，数据库初始化以后不允许任何人修改其中的值。

默认生成名称为“DID”和“SID”的两个序列，意义为“数据序列号”和“系统序列号”。&＃39;,

&＃39;user&＃39;, @CurrentUser, &＃39;table&＃39;, &＃39;TSysSequence&＃39;goinsert into TSysSequence (SeName,Increment,CurVal) 
values (&＃39;DID&＃39;,1,0) ;insert into TSysSequence (SeName,Increment,CurVal) values (&＃39;SID&＃39;,1,0) ;
/*=======================================*//* Index: In_SeName *//*=======================================*/
create unique index In_SeName on TSysSequence (SeName ASC)
go

再创建一个存储过程完成序列的使用

if exists (select 1from sysobjectswhere id = object_id(&＃39;PGetSequenceValue&＃39;)and type in (&＃39;P&＃39;,&＃39;PC&＃39;))drop 
procedure PGetSequenceValuegocreate 
procedure PGetSequenceValue@SeName nvarchar(50),@SeVal bigint 
outasbeginif not 
exists(select 1 from TSysSequence where SeName = @SeName)
beginraiserror(&＃39;不存在序列%s&＃39;,16,1,@SeName)returnend
update TSysSequence set @SeVal = CurVal + Increment, 
CurVal = CurVal + Increment where SeName = @SeNameendgo

使用方法

declare @ID1 intEXEC PGetSequenceValue &＃39;SID&＃39;,@ID1 OUTPUT
select @ID1
declare @ID2 intEXEC PGetSequenceValue &＃39;DID&＃39;,@ID2 OUTPUT
select @ID2

以上就是MSSqlServer伪序列_MySQL的内容，更多相关内容请关注PHP中文网（www.php1.cn）！