先创建一个序列表
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('PGetSequenceva lue')
and type in ('P','PC'))
drop procedure PGetSequenceva lue
go
create procedure PGetSequenceva lue
@SeName nvarchar(50),
@Seva l 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 @Seva l = CurVal + Increment, CurVal = CurVal + Increment where SeName = @SeName
end
go
使用方法
declare @ID1 int
EXEC PGetSequenceva lue 'SID',@ID1 OUTPUT
select @ID1
declare @ID2 int
EXEC PGetSequenceva lue 'DID',@ID2 OUTPUT
select @ID2