代码改变世界

SQL挑战——如何高效生成编码

2015-12-25 23:49  潇湘隐者  阅读(1886)  评论(8编辑  收藏  举报

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

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

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

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;