获取唯一编号的存储过程如何写?

lynx286

/***********得到最小缺号函数***********
唯一编号要求：需要是int类型
--------by /gmlxf/csdn/2003.11.20/-----******/

if exists(select 1 from dbo.sysobjects where id=object_id(N'dbo.f_getMinShortNum') and objectproperty(id,N'IsInlineFunction')=0)
        drop function f_getMinShortNum
go
create function f_getMinShortNum() returns int
as
begin
        declare @MinShortNum int
        select @MinShortNum=min(id)+1 from (select id from test union select 0) a where not exists (select 1 from test where id=a.id+1)
        --也可：select @MinShortNum=min(id)+1 from (select id from test union select 0) a where id not in (select id-1 from test)
        return @MinShortNum
end
go

--示例(创建一个表test,里面的id号码存在缺号：1，2，3，5，7，9，11...)
create table test(id int)
insert test
select 1
union select 2
union select 3
union select 5
union select 7
union select 9
union select 11
go
select id from test

--调用
select dbo.f_getMinShortNum()
--插入数据
insert test values(dbo.f_getMinShortNum())        --插入了id=4的一行
insert test values(dbo.f_getMinShortNum())        --插入了id=6的一行
select id from test order by id
drop table test

oracle_gb

收茯

landlubber

非常感谢各位,

特别是 lynx286陈永仁