SQLServer根据不同前缀生成多套流水号
1 --种子表 2 --@prefix 前缀 3 --@seed 种子值 4 create table RefNoSeed( 5 prefix char(4) unique, 6 seed int 7 ) 8 go 9 10 --测试表 11 --@insertTime 插入时间（仅测试用途） 12 --@RefNo 流水号 13 create table RefNoTable( 14 insertTime datetime unique, 15 RefNo varchar(20) unique 16 ) 17 go 18 19 if OBJECT_ID('F_FormattingSerialNumber','FN') is not null 20 drop function dbo.F_FormattingSerialNumber 21 go 22 --格式化数值，用于把种子数值转换为10个字符长度的字符串，不足位数前面添加0 23 --@sn 种子值 24 --returns 10个字符长度字符串 25 create function dbo.F_FormattingSerialNumber(@sn int) 26 returns varchar(10) 27 begin 28 return cast(replicate('0',10-len(cast(@sn as varchar(10)))) + cast(@sn as varchar(10)) as varchar(10)) 29 end 30 go 31 32 if OBJECT_ID('P_GenerateRefNo','P') is not null 33 drop proc dbo.P_GenerateRefNo 34 go 35 --获取前缀，生成流水号 36 --@prefix 前缀 37 --@timeNow 时间（仅测试用途） 38 create proc dbo.P_GenerateRefNo 39 @prefix varchar(20), 40 @timeNow datetime 41 as 42 begin try 43 --开启事务 44 begin tran 45 set nocount on; 46 declare @newSeed int 47 --根据前缀获取现在的种子值并+1为新种子值 48 set @newSeed=(select seed from RefNoSeed where prefix=@prefix)+1 49 --若不存在种子值，则按照前缀添加种子值，并把新种子值设置为1 50 if @newSeed is null 51 begin 52 insert into RefNoSeed values(@prefix,0) 53 set @newSeed=1 54 end 55 --生成流水号 56 declare @sn varchar(20) 57 set @sn=@prefix + cast(YEAR(@timeNow) as CHAR(4))+ dbo.F_FormattingSerialNumber(@newSeed) 58 --插入数据表中 59 insert into RefNoTable (insertTime,RefNo)values(@timeNow,@sn) 60 --插入成功后更新种子值 61 update RefNoSeed set seed=@newSeed where prefix=@prefix 62 --无错则提交事务 63 commit 64 end try 65 begin catch 66 --出错则进行回滚 67 rollback 68 end catch 69 go 70 71 --插入测试数据 72 declare @timenow datetime 73 set @timenow=GETDATE() 74 exec P_GenerateRefNo 'AB',@timenow 75 waitfor delay '00:00:00.02' 76 set @timenow=GETDATE() 77 exec P_GenerateRefNo 'AB',@timenow 78 waitfor delay '00:00:00.02' 79 set @timenow=GETDATE() 80 exec P_GenerateRefNo 'AB',@timenow 81 waitfor delay '00:00:00.02' 82 set @timenow=GETDATE() 83 exec P_GenerateRefNo 'AB',@timenow 84 waitfor delay '00:00:00.02' 85 set @timenow=GETDATE() 86 exec P_GenerateRefNo 'BF',@timenow 87 waitfor delay '00:00:00.02' 88 set @timenow=GETDATE() 89 exec P_GenerateRefNo 'BF',@timenow 90 waitfor delay '00:00:00.02' 91 set @timenow=GETDATE() 92 exec P_GenerateRefNo 'PF',@timenow 93 waitfor delay '00:00:00.02' 94 set @timenow=GETDATE() 95 exec P_GenerateRefNo 'PF',@timenow 96 waitfor delay '00:00:00.02' 97 set @timenow=GETDATE() 98 exec P_GenerateRefNo 'PF',@timenow 99 waitfor delay '00:00:00.02' 100 set @timenow=GETDATE() 101 exec P_GenerateRefNo 'RG',@timenow 102 103 --查看数据 104 select*from RefNoSeed order by prefix 105 select*from RefNoTable order by REFNO 106 107 --清除数据 108 delete RefNoSeed 109 delete RefNoTable 110 111 drop table RefNoSeed 112 drop table RefNoTable 113 114 if OBJECT_ID('F_FormattingSerialNumber','FN') is not null 115 drop function dbo.F_FormattingSerialNumber 116 go 117 if OBJECT_ID('P_GenerateRefNo','P') is not null 118 drop proc dbo.P_GenerateRefNo 119 go
