MSSqlServer伪序列_MySQL-mysql教程-PHP中文网

MSSqlServer伪序列_MySQL

PHP中文网

发布： 2016-05-27 14:12:33

原创

1294 人浏览过

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;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.php.cn）！