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

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;

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

标签：