MSSqlServer伪序列 - mysql数据库栏目 - 自学php

先创建一个序列表

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

declare @ID2 int EXEC PGetSequenceValue 'DID',@ID2 OUTPUT

查看更多关于MSSqlServer伪序列 - mysql数据库栏目 - 自学php的详细内容...