--创建自定义函数,得到新的ID
create function f_getid()
returns varchar(12)
as
begin
declare @id varchar(12)
declare @st varchar(16),@id1 varchar(1),@id2 varchar(1),@i int
set @st='0123456789ABCDEF'
select @id=max(主键) from tb
if @id is null
set @id='03000001-P01'
else
select @id1=substring(@id,11,1),@id2=right(@id,1)
,@id=left(@id,10)
,@i=charindex(@id2,@st)
,@id2=case @i when 16 then '0' else substring(@st,@i+1,1) end
,@id1=case @i when 16 then substring(@st,charindex(@id1,@st)+1,1)
else @id1 end
,@id=@id+@id1+@id2
return(@id)
end
go
--创建测试表
create table tb(主键 varchar(12) not null primary key default dbo.f_getid()
,aa int)
--插入数据测试
insert into tb(aa) values(1)
insert into tb(aa) values(2)
insert into tb(aa) values(3)
insert into tb(aa) values(4)
insert into tb(aa) values(5)
insert into tb(aa) values(6)
insert into tb(aa) values(7)
insert into tb(aa) values(8)
insert into tb(aa) values(9)
insert into tb(aa) values(10)
insert into tb(aa) values(11)
insert into tb(aa) values(12)
insert into tb(aa) values(13)
insert into tb(aa) values(14)
insert into tb(aa) values(15)
insert into tb(aa) values(16)
insert into tb(aa) values(17)
insert into tb(aa) values(18)
--显示结果
select * from tb
go
--删除测试环境
drop table tb
drop function f_getid
73.十六进制
最新推荐文章于 2024-07-08 03:19:57 发布