73.十六进制-蒲公英云

73.十六进制

た入场券 2024-02-19 18:02 160阅读 0赞

--创建自定义函数,得到新的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