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')