SQLServer存储过程生成任意进制的顺序号流水号
SQLServer存储过程生成任意进制的顺序号流水号
SQLServer存储过程生成任意进制的顺序号流水号
需求背景：在设计流水码的时候，之前用10进制，一旦数据量过大，位数就就得很多，而且光秃秃一串数字也不美观。后来想起车牌规则，就想将流水码也换成类似的。由于1，0和I，O很相似，所有去掉I，O，换成34进制。
功能：设计了一张表，用来存储十进制的数字，每次调用的时候加1，在方法返回的参数中，转为34进制。即，用sqlserver 实现带英文字母的流水码。
代码1：
ALTER PROCEDURE [dbo].[TentoSerial]
@num int,
@ret nvarchar(10) output
AS
declare @StringXL nvarchar(50)
declare @CharXL nvarchar(10)
declare @modnum int
set @StringXL='0123456789ABCDEFGHJKLMNPRSTUVWXYZ'
set @CharXL=''
if @num>35936
begin
raiserror('流水号已超过上限35936',16,-1)
return -1
end
if (@num<=32)
begin
set @CharXL=SUBSTRING(@stringxl,@num+1,1)
end
while @num>32
begin
set @modnum=@num%33
set @num=@num/33
set @CharXL=SUBSTRING(@stringxl,@modnum+1,1)+@CharXL
if (@num<=32)
begin
set @CharXL=SUBSTRING(@stringxl,@num+1,1)+@CharXL
end
end
select @ret=right('000'+@CharXL,3)
return 0
代码2：
SQL Server中实现34进制流水码
-- 34 进制 CREATE FUNCTION [dbo].[int2Char](@id bigint) RETURNS char(4) AS BEGIN; DECLARE @char_return varchar(10) = '', @v_count int; WHILE @id > 0 SELECT @v_count = @id % 34, @id = @id / 34, @char_return = CASE WHEN @v_count < 10 THEN RTRIM(@v_count) WHEN @v_count >=10 AND @v_count <18 THEN CHAR(55+@v_count) WHEN @v_count >= 18 AND @v_count< 23 THEN CHAR(55+@v_count + CASE WHEN @v_count > 17 THEN 1 ELSE 0 END) ELSE CHAR(55+@v_count + CASE WHEN @v_count >= 23 THEN 2 ELSE 0 END) END + @char_return RETURN(RIGHT('0000000000' + @char_return, 4)) END;
代码3：
--下面函数实现在sqlserver中产生带字母流水号,如ABC的下一个数为ABD, 而ABCD递增一个数为ABCE 。
create function dbo.f_IncIdent(@p_AIdent varchar(36))
returns varchar(20)
as
begin
declare @v_cChars varchar(40),
@v_J integer,
@v_K Integer,
@v_result varchar(40);
set @v_cChars = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
set @v_Result = @p_AIdent;
set @v_j= Len(@p_AIdent)
while @v_J >1
begin
SET @v_K = CHARINDEX(SUBSTRING(@P_AIdent,@V_J,1), @V_cChars);
if @V_K < Len(@V_cChars)
begin
set @v_result=substring(@v_result,1,@v_J-1)+SUBSTRING(@V_cChars,@v_k+1,1)+substring(@v_result,@v_J+1,40)
SET @V_K=Len(@P_AIdent)
while @v_k>@V_J
begin
set @v_result=substring(@v_result,1,@v_k-1)+substring(@v_cChars,1,1)+substring(@v_result,@v_k+1,40)
set @v_k=@v_k-1;
end;
return @v_result;
break;
end;
set @v_J=@v_J-1;
end;
return @v_result;
end;
代码4：
以如下表为例，表如下图：
方法1的实施思路：
取号全部在 RUL_Sequence 表中操作，不用管流水号所在表中的最大流水号。可以自由配置流水号规则。
通过一个表和一个存储过程来实现序列号生成。
/* 存储过程 dbo.[Proc_GetSeqence] */ 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
执行存储过程获得流水号：
Exec Proc_GetSeqence 'OrderNo','',''
方法2的实施思路：
取流水号所在表中的最大流水号 + 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')
···
人生只若初见................
浙公网安备 33010602011771号