SqlServer 生成流水号
第一种实现流水号的方法:
注:取号全部在 RUL_Sequence 表中操作,不用管流水号所在表中的最大流水号
可以自由配置流水号规则。
前提:需要一个表和一个存储过程。
表如下图:
存储过程如下:
- CREATE PROCEDURE [dbo].[Proc_GetSeqence]
- @SeqCode varchar(60), -- 规则代码
- @ReturnNum Varchar(40) OUTPUT, -- 返回的流水号
- @MessageCode varchar(800) OUTPUT -- 异常消息等
- AS
- /* Exec Proc_GetSeqence ‘OrderNo‘,‘‘,‘‘
- *****************************************************************
- 作者: XXXXXXXXX
- 日期: 2013/06/30
- 功能描述: 获取数据表的主键流水号(INV, ASN, SO...)
- 主要思路: 1.取得最新流水号信息
- 2.把所有固定的规则信息替换成具体值,其他保持不变
- eg:
- 规则为: ASN<YYYY><YY><MM><XXX>ASN
- 当前日期为: 20130630
- 当前流水号为:12
- 最终流水号为:ASN201306013ASN
- ******************************************************************
- */
- /*
- * SET NOCOUNT ON 的作用:
- * 不返回受影响行数
- * 存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。
- * */
- SET NOCOUNT ON
- DECLARE @SeqNowNumStr VARCHAR(20) --当前值字符类型
- DECLARE @SeqNowNum BIGINT --当前值
- DECLARE @year CHAR(4) --年 YYYY
- DECLARE @month CHAR(2) --月 MM
- DECLARE @day CHAR(2) --日 DD
- DECLARE @Length INT --流水号长度
- DECLARE @DataFormat VARCHAR(50) --流水号规则
- DECLARE @IniValue INT --归零值
- DECLARE @ResetType VARCHAR(10) --归零方式
- DECLARE @LastDate CHAR(8) --日期最大值
- DECLARE @WorkFLowStr VARCHAR(20) --前一次调用流水号时的日期值
- DECLARE @DataNow CHAR(8) --当前日期
- DECLARE @i INT --转换变量,作用参照代码上下文
- /*
- * SET XACT_ABORT ON 的作用:
- * 存储中的某个地方出了问题,整个事务中的语句都会回滚
- * */
- SET XACT_ABORT ON
- BEGIN TRY
- /* 初始化变量 */
- SET @MessageCode=‘999‘
- SET @ReturnNum = ‘0‘
- SET @Length=0
- SET @SeqNowNum =0;
- SET @DataNow=CONVERT(CHAR(8),GETDATE(),112) --得到 20130704 的时间格式
- SET @year=SUBSTRING(@DataNow,1,4)
- SET @month =SUBSTRING(@DataNow,5,2)
- SET @day =SUBSTRING(@DataNow,7,2)
- Set @i=1
- /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 Start*******/
- BEGIN TRANSACTION
- wait:
- Update dbo.RUL_Sequence Set [IsRunning]=‘2‘ where SeqCode=@SeqCode and IsRunning=‘1‘
- If @@Rowcount=0
- Begin
- Waitfor Delay ‘00:00:01‘
- Set @i=@i+1
- If @i<6 goto wait
- End
- COMMIT TRANSACTION
- /***********如果有并发的正在运行,最多等待0.06秒,然后继续运行 End*******/
- Select @Length = [Length],@SeqNowNum=NowSeqValue,@LastDate=DateMax,@DataFormat=DataFormat
- ,@ResetType=ResetType,@IniValue =InitValue
- From RUL_Sequence where SeqCode=@SeqCode
- if @SeqNowNum=0 --当前值正常情况下不可能是0
- begin
- Set @MessageCode=‘100‘ --当前值 错误代码
- select @MessageCode
- return
- END
- --@ResetType=1 不归零 2 按日归零 3 按月归零 4按年归零
- If (@ResetType=2 and @DataNow<>@LastDate AND @IniValue>0)
- OR (@ResetType=3 and @year+@month<>SUBSTRING(@LastDate,1,6) AND @IniValue>0)
- OR (@ResetType=4 and @year<>SUBSTRING(@LastDate,1,4) AND @IniValue>0 )
- BEGIN
- SET @SeqNowNum=@IniValue
- END
- SET @i=@Length --@i 此时表示流水号的总长度
- /***********拼流水号格式 Start*******/
- SET @WorkFLowStr=‘<‘
- WHILE @Length>0
- BEGIN
- SET @WorkFLowStr=@WorkFLowStr+‘X‘
- SET @Length=@Length-1
- END
- SET @WorkFLowStr=@WorkFLowStr+‘>‘
- /***********拼流水号格式 End*******/
- set @SeqNowNumStr=CONVERT(VARCHAR(20),@SeqNowNum)
- SET @Length=@i-len(@SeqNowNumStr) --@Length 要补零的位数(eg:@SeqNowNumStr=148 当前流水号是五位,最后流水号为00148,00 就是需要补的两位)
- /***********补零操作 Start*******/
- WHILE @Length>0
- BEGIN
- SET @SeqNowNumStr=‘0‘+@SeqNowNumStr
- SET @Length=@Length-1
- END
- /***********补零操作 End*******/
- SET @ReturnNum=REPLACE( @DataFormat,‘<YYYY>‘,@year); -- 把规则中<YYYY>替换成相应年
- SET @ReturnNum=REPLACE( @ReturnNum,‘<MM>‘,@month); -- 把规则中<MM>替换成相应月
- SET @ReturnNum=REPLACE( @ReturnNum,‘<DD>‘,@day); -- 把规则中<DD>替换成相应日
- SET @ReturnNum=REPLACE( @ReturnNum,@WorkFLowStr,@SeqNowNumStr);-- 把规则中的形如<XXX>的替换成相应流水号,
- /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) Start*******/
- Begin transaction
- UPDATE RUL_Sequence SET NowSeqValue=@SeqNowNum+1,DateMax=@DataNow,ISRUNNING=‘1‘, EditTime=Getdate()
- WHERE IsRunning=‘2‘ AND SeqCode=@SeqCode
- --SELECT * FROM RUL_Sequence WHERE IsRunning=‘2‘ AND SeqCode=@SeqCode
- -- PRINT @SeqNowNum+1
- Commit transaction
- /***********更新当前流水值为最大流水号、上一个流水号生成时间和运行标记(运行标记置为"1"(没有运行) ) End*******/
- PRINT @ReturnNum
- RETURN
- END TRY
- --错误捕获
- BEGIN CATCH
- ROLLBACK TRANSACTION
- set @MessageCode=‘行号=‘+cast(ERROR_LINE() as varchar(10))+‘错误信息‘+ERROR_MESSAGE()
- +‘[‘+ERROR_PROCEDURE()+‘]‘
- IF @@ROWcount<=0
- set @MessageCode=‘无此编号规则‘+@MessageCode
- SELECT @MessageCode
- END CATCH
RUL_Sequence表中数据如图:
执行存储过程获得流水号:
Exec Proc_GetSeqence ‘OrderNo‘,‘‘,‘‘
第二种实现流水号的方法:
注:取流水号所在表中的最大流水号 + 1
待研究问题:Shopping_Pay_Basic表中的流水号字段PayOrderNo遇到并发时,是否会影响用户操作?
注:第一种实现只管取号,可能造成浪费,但没有并发问题。
- --得到新编号的函数
- ALTER FUNCTION [dbo].[GetNextPayOrderNo](@Type char(2))
- RETURNS char(16)
- AS
- BEGIN
- DECLARE @dt CHAR(8)
- SELECT @dt=CONVERT(CHAR(8),GETDATE(),112)
- RETURN(
- SELECT @Type + (@dt+RIGHT(1000001+ISNULL(RIGHT(MAX(PayOrderNo),6),0),6))
- FROM Shopping_Pay_Basic WITH(XLOCK,PAGLOCK)
- WHERE PayOrderNo like @Type + @dt+‘%‘)
- END
- -- SELECT dbo.GetNextPayOrderNo(‘TT‘)
执行函数获得流水号:
- SELECT dbo.GetNextPayOrderNo(‘AA‘
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。