先創建一個序列表
if exists (select 1
from sysindexes
where id = object_id('TSysSequence')
and name = 'In_SeName'
and indid > 0
and indid < 255)
drop index TSysSequence.In_SeName
go
if exists (select 1
from sysobjects
where id = object_id('TSysSequence')
and type = 'U')
drop table TSysSequence
go
/*==============================================================*/
/* Table: TSysSequence */
/*==============================================================*/
create table TSysSequence (
SeName nvarchar(50) not null,
Increment int not null default 1,
CurVal bigint not null default 0
)
go
if exists (select 1 from sys.extended_properties
where major_id = object_id('TSysSequence') and minor_id = 0)
begin
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_dropextendedproperty 'MS_Description',
'user', @CurrentUser, 'table', 'TSysSequence'
end
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'
模擬oracle序列
不允許用戶維護，數據庫初始化以後不允許任何人修改其中的值。
默認生成名稱為“DID”和“SID”的兩個序列，意義為“數據序列號”和“系統序列號”。',
'user', @CurrentUser, 'table', 'TSysSequence'
go
insert into TSysSequence (SeName,Increment,CurVal) values ('DID',1,0) ;
insert into TSysSequence (SeName,Increment,CurVal) values ('SID',1,0) ;
/*==============================================================*/
/* Index: In_SeName */
/*==============================================================*/
create unique index In_SeName on TSysSequence (
SeName ASC
)
go
再創建一個存儲過程完成序列的使用
if exists (select 1
from sysobjects
where id = object_id('PGetSequenceValue')
and type in ('P','PC'))
drop procedure PGetSequenceValue
go
create procedure PGetSequenceValue
@SeName nvarchar(50),
@SeVal bigint out
as
begin
if not exists(select 1 from TSysSequence where SeName = @SeName)
begin
raiserror('不存在序列%s',16,1,@SeName)
return
end
update TSysSequence set @SeVal = CurVal + Increment, CurVal = CurVal + Increment where SeName = @SeName
end
go
使用方法
declare @ID1 int
EXEC PGetSequenceValue 'SID',@ID1 OUTPUT
select @ID1
declare @ID2 int
EXEC PGetSequenceValue 'DID',@ID2 OUTPUT
select @ID2
