SQL系列流水号

实现效果：流水号由三部分组成，"流水号头部"+"日期"+"产生的序号"
流水号序号产生按照每天一个周期来算，即每天序号都由0开始
设计原理：我用一个专门的表记录流水号的相关信息，例如流水号头，流水号名称，当前流水号的序号等等，如下：

 

 

view plaincopy to clipboardprint?

1. create table tb_BH   
2. (   
3. [ID] int identity(1,1),    
4. MingChen nvarchar(50),--编号表的名称    
5. TouBu  char(2) not null,--流水号的头部    
6. RiQi datetime default getdate(),--保存当前日期    
7. XuHao int default 0,--当前日期流水号的最大值    
8. MiaoShu nvarchar(100),   
9. C_Tb nvarchar(50),--当前流水号的对应表    
10. C_Tb_Field nvarchar(50),--当前流水号对应表的字段    
11.  primary key(TouBu)   
12. )   
13. Go  

create table tb_BH([ID] int identity(1,1),MingChen nvarchar(50),--编号表的名称TouBu char(2) not null,--流水号的头部RiQi datetime default getdate(),--保存当前日期XuHao int default 0,--当前日期流水号的最大值MiaoShu nvarchar(100),C_Tb nvarchar(50),--当前流水号的对应表C_Tb_Field nvarchar(50),--当前流水号对应表的字段primary key(TouBu))Go
 

 

编号表对应的实体表：

view plaincopy to clipboardprint?

1. create table demo(bh char(17) primary key(bh))    
2.  insert demo select'CC20100429000028'  
3.  union all select 'CC20100429000029'  
4. union all select 'CC20100429000030'  
5. union all select 'CC20100429000031'  

create table demo(bh char(17) primary key(bh)) insert demo select'CC20100429000028'union all select 'CC20100429000029'union all select 'CC20100429000030'union all select 'CC20100429000031'

 

如果tb_BH保存的日期和当前日期相同，则从tb_BH的XuHao中读取数据，如果读取的日期小于当前日期，则从对应表中获取，例如从demo表中获得指定日期流水号的最大值
 

view plaincopy to clipboardprint?

1. create proc Pro_BH   
2. @TouBu nvarchar(2),   
3. @RiQi datetime=null,   
4. @BH char(17)=null output  
5. as  
6. if @RiQi is null set @RiQi=convert(char(8),getdate(),112)   
7. begin tran   
8. declare @s nvarchar(4000)   
9. declare @C_Tb_Field varchar(50)   
10. declare @C_Tb varchar(50)   
11. update tb_BH with(rowlock) set  
12. @BH=case when convert(char(8),RiQi,112)>@RiQi then '0'  
13. else TouBu+convert(char(8),getdate(),112)+right('00000'+cast(XuHao as nvarchar),6) end ,   
14. XuHao=case when convert(char(8),RiQi,112)<@RiQi then 0   
15. when convert(char(8),RiQi,112)=@RiQi then XuHao+1 end,   
16. RiQi=getdate(),   
17. @C_Tb_Field=C_Tb_Field,   
18. @C_Tb=C_Tb   
19. where TouBu=@TouBu   
20. if @BH='0'  
21. begin  
22. select @s=N'select top 1  @BH='+@C_Tb_Field+' from '+@C_Tb+' with(xlock,paglock) where '+@C_Tb_Field+' like '''+@TouBu+convert(char(8),@RiQi,112)+'%'' order by '+ @C_Tb_Field +' desc'  
23. exec sp_executesql @s,N'@BH char(17) output',@BH output  
24. set @BH=left(@BH,10)+right('00000'+cast(cast(right(@BH,7) as int)+1 as nvarchar),6)   
25. end  
26. print @BH   
27. commit tran   
28. Go  

create proc Pro_BH@TouBu nvarchar(2),@RiQi datetime=null,@BH char(17)=null outputasif @RiQi is null set @RiQi=convert(char(8),getdate(),112)begin trandeclare @s nvarchar(4000)declare @C_Tb_Field varchar(50)declare @C_Tb varchar(50)update tb_BH with(rowlock) set@BH=case when convert(char(8),RiQi,112)>@RiQi then '0'else TouBu+convert(char(8),getdate(),112)+right('00000'+cast(XuHao as nvarchar),6) end ,XuHao=case when convert(char(8),RiQi,112)<@RiQi then 0when convert(char(8),RiQi,112)=@RiQi then XuHao+1 end,RiQi=getdate(),@C_Tb_Field=C_Tb_Field,@C_Tb=C_Tbwhere TouBu=@TouBuif @BH='0'beginselect @s=N'select top 1 @BH='+@C_Tb_Field+' from '+@C_Tb+' with(xlock,paglock) where '+@C_Tb_Field+' like '''+@TouBu+convert(char(8),@RiQi,112)+'%'' order by '+ @C_Tb_Field +' desc'exec sp_executesql @s,N'@BH char(17) output',@BH outputset @BH=left(@BH,10)+right('00000'+cast(cast(right(@BH,7) as int)+1 as nvarchar),6)endprint @BHcommit tranGo

 

接下来按两个方面测试：
1、获取当前编号：exec Pro_BH 'CC' 。这时从编号表读取

2、获取指定日期的流水号：exec Pro_BH 'CC','2010-4-29'。这时从插入的对应表中获取：

 

 下载：流水号