SQL挑战如何快速生成编码,群裡提問的水流序號產生情势

CREATE OR REPLACE FUNCTION FUN_GEN_NEXT_CODE(MAX_DEMENSION_NO VARCHAR2)

RETURN VARCHAR2

IS

  CodeValue  NUMBER(5);

  CodeChar   VARCHAR(4);

  CharValue  VARCHAR2(5);

  ReturnCode VARCHAR2(5);

BEGIN

 

  IF LENGTH(MAX_DEMENSION_NO) >=6 OR LENGTH(MAX_DEMENSION_NO) < 5 THEN

    RETURN '';

  END IF;

  

  

  SELECT REGEXP_SUBSTR(MAX_DEMENSION_NO,'[[:digit:]]+') INTO CodeValue FROM DUAL;

  

  IF LENGTH(CodeValue)= 4 THEN

    IF CodeValue= 9999 THEN

       IF SUBSTR(MAX_DEMENSION_NO,1,1)='Z' THEN

          CharValue :='ZA';

          CodeChar := '000';

       ELSE

          CharValue :=CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,1,1)) +1);

          CodeChar := '0000';

       END IF;

    

    ELSE

       CharValue :=SUBSTR(MAX_DEMENSION_NO,0,1);

       CodeChar :=TRIM(TO_CHAR(CodeValue+1,'0000'));

    END IF;

 

     ReturnCode :=CharValue || CodeChar;

     

  ELSIF LENGTH(CodeValue)=3 THEN

    IF CodeValue= 999 THEN

       IF SUBSTR(MAX_DEMENSION_NO,1,2)='ZZ' THEN

          ReturnCode :='ZZA' || '00';

       ELSE

          IF SUBSTR(MAX_DEMENSION_NO,2,1) ='Z' THEN

              ReturnCode := CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,1,1)) +1) || '0000';

          ELSE

              ReturnCode :=SUBSTR(MAX_DEMENSION_NO,1,1) || CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,2,1)) +1) || '000';

          END IF;

       END IF;

    ELSE

       ReturnCode :=SUBSTR(MAX_DEMENSION_NO,1,1) || TRIM(TO_CHAR(CodeValue+1,'000'));

    END IF;

    

     

  ELSIF LENGTH(CodeValue)=2 THEN

    IF CodeValue= 99 THEN

       IF  SUBSTR(MAX_DEMENSION_NO,1,3) ='ZZZ' THEN

          ReturnCode :='ZZZA0';

       ELSE

          IF SUBSTR(MAX_DEMENSION_NO,3,1) ='Z' THEN

              ReturnCode := SUBSTR(MAX_DEMENSION_NO,1,1) || CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,2,1)) +1) + '000';

          ELSE 

              ReturnCode := SUBSTR(MAX_DEMENSION_NO,1,2) || CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,3,1)) +1) || '00';

          END IF;

       END IF;

    ELSE

       ReturnCode :=SUBSTR(MAX_DEMENSION_NO,1,3) + TRIM(TO_CHAR(CodeValue+1,'00'));

    END IF;

  ELSIF LENGTH(CodeValue)=1 THEN

     IF CodeValue= 9 THEN

        IF SUBSTR(MAX_DEMENSION_NO, 1,4) ='ZZZZ' THEN

            ReturnCode := 'ZZZZA';

        ELSE 

            ReturnCode := SUBSTR(MAX_DEMENSION_NO, 1,3) || CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,4,1)) +1) || '0';

        END IF;

    ELSE

            ReturnCode :=SUBSTR(MAX_DEMENSION_NO,0,4) || TRIM(TO_CHAR(CodeValue+1,'0'));

    END IF;

  ELSE 

    IF MAX_DEMENSION_NO='ZZZZZZ' THEN

       ReturnCode :='ZZZZZ';

    ELSE

       ReturnCode :=SUBSTR(MAX_DEMENSION_NO, 1,4) || CHR( ASCII(SUBSTR(MAX_DEMENSION_NO,5,1)) +1);

    END IF;

    

  END IF;

     RETURN ReturnCode;

   

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN ('');

END FUN_GEN_NEXT_CODE;

流程大致是這樣的

有这样一个需求:需要根据输入的编码(这个编码值来自于数据库的一个表)生成下一个编码,编码规则如下所示(我们暂且不关心这个逻辑是否合理,只关心如何实现):

 1: 最小值为A0000, 最大值为ZZZZZ

   1: 最小值为A0000, 最大值为ZZZZZ
   2:编码A0000的下一个值为A0001, 编码A9999的下一个值为B0000,
编码AB999的下一个值为AC000,编码AC999的下一个值为AD000,依此规则内推。
  
3:不用担心输入值为类似A09BC这样的值,应用程序会从表里面取编码的最大值。应用程序也会检查、控制输入参数,不用在数据库的函数(FUNCTION)里面做检查控制。
  
4:不用担心输入值为ac908这种值(大小写问题),应用程序从表里获取编码的值(不接受用户输入)。所以这个的检查、控制也不用纳入数据库函数考虑范围。

2:编码A0000的下一个值为A0001, 编码A9999的下一个值为B0000,
编码AB999的下一个值为AC000,编码AC999的下一个值为AD000,依此内推。

看到同事用ASCII循环判断字符是否为数字,大量的逻辑处理,我觉得并不是如何高效而且有些弄复杂了,写了下面FUN_GEN_NEXT_CODE,用正则表达式获取数字部分,然后根据数字部分进行判断处理。
写完感觉也有点臃肿,因为要花大量的判断处理边界值(A9999
AZ999之类的边界值),但是暂时也没有更好的思路想法。 (ORACLE数据库实现)


DECLARE @Serial VARCHAR( 5)='ZA000' --啟始的編號
DECLARE @Header VARCHAR( 5)=''
DECLARE @Count int= 50000; --要產生多少筆流水號
WHILE(@Count !=0)
BEGIN
        SELECT @Serial=dbo.GeneratorKey( @Serial);
        PRINT @Serial ;
        SET @Count =@Count- 1;
END

進位的動作是這樣的,我們要判斷的是目前最大的英文是否到Z如果是到Z了,就要補上新的頭編號。
e.g.   Z9999 下一位會是ZA000

透過這個思路,我稍微修改了一下。

e.g. A的ASCII碼為65
,我們可以先轉成65後做相加變成66,最後再轉回來英文字母,就會變成B  😀

--拆分傳入值為頭(字母 ) 尾( 數字)
    WHILE((ASCII (CAST( @Serial as CHAR)))>= 65)
    BEGIN
         SET @Header=@Header +SUBSTRING( @Serial,1 ,1);
         SET @Serial=SUBSTRING (@Serial, 2,LEN (@Serial)- 1)
    END
    --這裡@Serial 會只存放截出的[數字],但因為下面計算@Serial實際的長度
    --需要用到原始數字的長度,所以多一個@Number來做存放
    SET @Number = @Serial;
--拆分結束
    --開始進行編號+1動作
    IF CAST (@Number as int)=CAST (REPLICATE( '9',LEN (@Number)) AS INT) --當編號準備要進位時
    BEGIN
         IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))=90 -- 判斷目前最大英文是否到 Z
         BEGIN
            SET @Header = @Header+'A'
         END
         ELSE -- 如果沒有到 Z則英文數字就進位
         BEGIN
            SET @Header = SUBSTRING(@Header ,1, LEN(@Header )-1) + CHAR(ASCII (CAST( SUBSTRING(@Header ,LEN( @Header),1 ) as CHAR))+ 1)
         END
    SET @Number =REPLICATE( '0',LEN (@Number))
    SET @FullNumber =@Header+ @Number; --設定最新流水號
    END
    ELSE
    BEGIN
         SET @Number =CAST( @Number as int)+ 1;
         --這裡就需要依照@Serial實際的長度來補多少個
         --因為@Number 轉成int後就會去掉零
         SET @Number = REPLICATE('0' ,LEN( @Serial)-LEN (@Number)) + @Number -- 補
         SET @FullNumber =@Header+ @Number; --設定最新流水號
    END
    --PRINT @FullNumber
    RETURN SUBSTRING(@FullNumber,1,@SerialLength) --這裡改為截出我們預期長度的字串

END

2.回傳時我們依照當下應該回傳序號的長度,做截取字串的動作(SUBSTRING)  

以我的程序來說當序號為Z9999時,其實是會組合成ZA0000,但剛好因為VARCHAR(5),所以最後就變成了ZA000了。

總結,我的思路是這樣的,透過英文可以轉成對應的ASCII碼,其實就可以利用ASCII碼做相加的動作。得到我們下一個要用到的英文字母

1.我們將編號拆分成頭編號(英文)與尾編號(數字)分別存放,最後再合併成為一個完整的流水號

所以我們利用,原本的尾編號長度(0001) 扣掉我們運算後的尾編號長度(1)
。就知道我們需要重複3個0。

3.因為我們編號已經拆為頭尾了,所以就很容易做相加的動作(因為我們要取得下一位的流水號)

CREATE FUNCTION [dbo].[GeneratorKey]
(
    -- Add the parameters for the function here
    @Serial VARCHAR(5)
)
RETURNS VARCHAR(5)
AS
BEGIN
    DECLARE @Header VARCHAR( 5) =''; --頭編號
    DECLARE @Number VARCHAR( 5)='' ; -- 尾編號
    DECLARE @FullNumber VARCHAR( 5)='' ; --最後完整的序號
IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))=90 
         AND LEN (@Header)<>@SerialLength -- 判斷目前最大英文是否到Z 與 判斷頭英文字母長度是否已經到了我們序號長度
         BEGIN
            SET @Header = @Header+'A'
         END
         IF ASCII(CAST (SUBSTRING( @Header,LEN (@Header), 1) as CHAR ))<>90 -- 如果沒有到 Z則英文數字就進位
         BEGIN
            SET @Header = SUBSTRING(@Header ,1, LEN(@Header )-1) + CHAR(ASCII (CAST( SUBSTRING(@Header ,LEN( @Header),1 ) as CHAR))+ 1)
         END

    IF @SerialLength-LEN(@Header)>=0 --如果目前序號未被英文字母占滿 e.g ZZZZ0(未被占滿) ZZZZZ(占滿)
        SET @Number =REPLICATE('0',@SerialLength-LEN(@Header))
    ELSE
        SET @Number=''

4.如果不需要進位的話,就簡單的將尾編號做加1的動作,都做完後就可以準備組成我們下一個流水號了。
這裡我用了一個REPLICATE函數,他可以依照你設定的值,重複某一段文字

2015.12.30
照了@剑走江湖 提出的問題,我稍微用了點小技巧,解決了
😀  。 小於5位會出現異常的問題的原因在於


2.在14行我們做了將傳入的編號,拆分成頭編號與尾編號的動作。這裡就利用到ASCII函數永遠只會取第一碼的特性。假設傳入的是A0001就會回傳65,B0001則是66

发表评论

电子邮件地址不会被公开。 必填项已用*标注