SqlServer 生成流水号

第一种实现流水号的方法: 

 注:取号全部在 RUL_Sequence 表中操作,不用管流水号所在表中的最大流水号

          可以自由配置流水号规则。

 前提:需要一个表和一个存储过程。

表如下图:

技术分享

 

存储过程如下:

 

  1. CREATE PROCEDURE [dbo].[Proc_GetSeqence]  
  2.           @SeqCode varchar(60),             -- 规则代码  
  3.           @ReturnNum Varchar(40) OUTPUT,    -- 返回的流水号  
  4.           @MessageCode varchar(800) OUTPUT  -- 异常消息等  
  5.   
  6. AS  
  7. /* Exec Proc_GetSeqence ‘OrderNo‘,‘‘,‘‘  
  8. *****************************************************************  
  9. 作者:     XXXXXXXXX  
  10. 日期:     2013/06/30   
  11. 功能描述:   获取数据表的主键流水号(INV, ASN, SO...)  
  12.       
  13. 主要思路:   1.取得最新流水号信息  
  14.             2.把所有固定的规则信息替换成具体值,其他保持不变  
  15.             eg:  
  16.             规则为:        ASN<YYYY><YY><MM><XXX>ASN  
  17.             当前日期为:  20130630  
  18.             当前流水号为:12  
  19.             最终流水号为:ASN201306013ASN  
  20.               
  21. ******************************************************************  
  22. */  
  23.   
  24.   
  25. /*  
  26. SET NOCOUNT ON 的作用:  
  27. * 不返回受影响行数  
  28. * 存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。  
  29. * */  
  30. SET NOCOUNT ON  
  31.   
  32. DECLARE @SeqNowNumStr VARCHAR(20)   --当前值字符类型     
  33. DECLARE @SeqNowNum BIGINT           --当前值     
  34. DECLARE @year CHAR(4)               --年 YYYY  
  35. DECLARE @month CHAR(2)              --月 MM  
  36. DECLARE @day CHAR(2)                --日 DD  
  37. DECLARE @Length INT                 --流水号长度  
  38. DECLARE @DataFormat VARCHAR(50)     --流水号规则  
  39. DECLARE @IniValue INT               --归零值  
  40. DECLARE @ResetType VARCHAR(10)      --归零方式  
  41. DECLARE @LastDate   CHAR(8)         --日期最大值           
  42. DECLARE @WorkFLowStr VARCHAR(20)    --前一次调用流水号时的日期值  
  43. DECLARE @DataNow CHAR(8)            --当前日期  
  44. DECLARE @i INT                      --转换变量,作用参照代码上下文  
  45.       
  46.   
  47. /*  
  48. SET XACT_ABORT ON 的作用:  
  49. * 存储中的某个地方出了问题,整个事务中的语句都会回滚  
  50. * */  
  51. SET XACT_ABORT ON  
  52. BEGIN TRY  
  53.   
  54.     /* 初始化变量 */  
  55.      SET @MessageCode=‘999‘  
  56.      SET @ReturnNum = ‘0‘  
  57.      SET @Length=0  
  58.      SET @SeqNowNum =0;  
  59.      SET @DataNow=CONVERT(CHAR(8),GETDATE(),112) --得到 20130704 的时间格式  
  60.      SET @year=SUBSTRING(@DataNow,1,4)  
  61.      SET @month =SUBSTRING(@DataNow,5,2)  
  62.      SET @day =SUBSTRING(@DataNow,7,2)  
  63.        
  64.      Set @i=1   
  65.        
  66.      /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 Start*******/             
  67.      BEGIN TRANSACTION   
  68.          wait:  
  69.          Update dbo.RUL_Sequence Set [IsRunning]=‘2‘ where SeqCode=@SeqCode and IsRunning=‘1‘  
  70.         If @@Rowcount=0   
  71.         Begin  
  72.             Waitfor Delay ‘00:00:01‘  
  73.             Set @i=@i+1  
  74.             If @i<6 goto wait  
  75.         End  
  76.         
  77.      COMMIT TRANSACTION     
  78.      /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 End*******/             
  79.   
  80.     Select @Length = [Length],@SeqNowNum=NowSeqValue,@LastDate=DateMax,@DataFormat=DataFormat  
  81.         ,@ResetType=ResetType,@IniValue =InitValue  
  82.             From RUL_Sequence where SeqCode=@SeqCode  
  83.                         if @SeqNowNum=0  --当前值正常情况下不可能是0  
  84.                         begin  
  85.                            Set @MessageCode=‘100‘  --当前值 错误代码  
  86.                            select @MessageCode  
  87.                            return  
  88.                         END  
  89.                         --@ResetType=1 不归零 2 按日归零   3 按月归零   4按年归零  
  90.     If (@ResetType=2 and @DataNow<>@LastDate  AND @IniValue>0)  
  91.         OR (@ResetType=3 and @year+@month<>SUBSTRING(@LastDate,1,6) AND @IniValue>0)  
  92.         OR (@ResetType=4 and @year<>SUBSTRING(@LastDate,1,4) AND @IniValue>0 )  
  93.        BEGIN  
  94.         SET @SeqNowNum=@IniValue  
  95.        END   
  96.      SET  @i=@Length --@i 此时表示流水号的总长度  
  97.             
  98.      /***********拼流水号格式 Start*******/             
  99.      SET @WorkFLowStr=‘<‘  
  100.      WHILE @Length>0   
  101.      BEGIN  
  102.         SET @WorkFLowStr=@WorkFLowStr+‘X‘  
  103.         SET @Length=@Length-1  
  104.      END    
  105.      SET @WorkFLowStr=@WorkFLowStr+‘>‘   
  106.      /***********拼流水号格式 End*******/  
  107.        
  108.      set @SeqNowNumStr=CONVERT(VARCHAR(20),@SeqNowNum)  
  109.      SET @Length=@i-len(@SeqNowNumStr)  --@Length 要补零的位数(eg:@SeqNowNumStr=148 当前流水号是五位,最后流水号为00148,00 就是需要补的两位)  
  110.        
  111.      /***********补零操作 Start*******/             
  112.      WHILE @Length>0   
  113.      BEGIN  
  114.         SET @SeqNowNumStr=‘0‘+@SeqNowNumStr  
  115.         SET @Length=@Length-1  
  116.      END  
  117.      /***********补零操作 End*******/             
  118.        
  119.      SET @ReturnNum=REPLACE( @DataFormat,‘<YYYY>‘,@year);         -- 把规则中<YYYY>替换成相应年  
  120.      SET @ReturnNum=REPLACE( @ReturnNum,‘<MM>‘,@month);               -- 把规则中<MM>替换成相应月  
  121.      SET @ReturnNum=REPLACE( @ReturnNum,‘<DD>‘,@day);             -- 把规则中<DD>替换成相应日  
  122.      SET @ReturnNum=REPLACE( @ReturnNum,@WorkFLowStr,@SeqNowNumStr);-- 把规则中的形如<XXX>的替换成相应流水号,  
  123.        
  124.        
  125.      /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) Start*******/  
  126.     Begin transaction  
  127.        
  128.      UPDATE RUL_Sequence SET NowSeqValue=@SeqNowNum+1,DateMax=@DataNow,ISRUNNING=‘1‘, EditTime=Getdate()  
  129.      WHERE IsRunning=‘2‘ AND  SeqCode=@SeqCode  
  130.      --SELECT * FROM RUL_Sequence WHERE IsRunning=‘2‘ AND SeqCode=@SeqCode  
  131.     -- PRINT @SeqNowNum+1  
  132.     Commit transaction   
  133.      /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) End*******/  
  134.    PRINT @ReturnNum  
  135.     RETURN  
  136.   
  137. END TRY  
  138.   
  139. --错误捕获  
  140. BEGIN   CATCH     
  141.      ROLLBACK TRANSACTION    
  142.   set @MessageCode=‘行号=‘+cast(ERROR_LINE() as varchar(10))+‘错误信息‘+ERROR_MESSAGE()   
  143.     +‘[‘+ERROR_PROCEDURE()+‘]‘  
  144.    IF @@ROWcount<=0   
  145.       set @MessageCode=‘无此编号规则‘+@MessageCode  
  146.     SELECT  @MessageCode  
  147. END   CATCH  

 

RUL_Sequence表中数据如图:

 

技术分享

 

执行存储过程获得流水号:

 

Exec Proc_GetSeqence ‘OrderNo‘,‘‘,‘‘

 

 

 

第二种实现流水号的方法:

注:取流水号所在表中的最大流水号 + 1 

待研究问题:Shopping_Pay_Basic表中的流水号字段PayOrderNo遇到并发时,是否会影响用户操作?

注:第一种实现只管取号,可能造成浪费,但没有并发问题。

 

  1. --得到新编号的函数  
  2. ALTER FUNCTION [dbo].[GetNextPayOrderNo](@Type char(2))  
  3. RETURNS char(16)  
  4. AS  
  5. BEGIN  
  6.     DECLARE @dt CHAR(8)  
  7.     SELECT @dt=CONVERT(CHAR(8),GETDATE(),112)  
  8.     RETURN(  
  9.         SELECT @Type + (@dt+RIGHT(1000001+ISNULL(RIGHT(MAX(PayOrderNo),6),0),6))   
  10.         FROM Shopping_Pay_Basic WITH(XLOCK,PAGLOCK)  
  11.         WHERE PayOrderNo like @Type + @dt+‘%‘)  
  12. END  
  13. -- SELECT  dbo.GetNextPayOrderNo(‘TT‘)  



 

执行函数获得流水号:

 

    1. SELECT  dbo.GetNextPayOrderNo(‘AA‘

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。