/*
@author:BRUNOWU
@date:20080416
@desc:输入一个编号,找到下一个自动编号的值,如输入99,下一个编号就为:1A,
*/
PROCEDURE P_GEN_CUSNO
(
V_CUSNOCODE_IN VARCHAR2,--输入自编号
O_CUSNOCODE OUT VARCHAR2--下一个自动编号
)
AS
SUB_A VARCHAR2(1);
SUB_B VARCHAR2(1);
V_CUSNOCODE VARCHAR2(2);
V_ASCII_A NUMBER;
V_ASCII_B NUMBER;
BEGIN
IF LENGTH(V_CUSNOCODE_IN)<>2 THEN --如果输入的值长度不为2,直接退出
GOTO EEND;
END IF;
V_CUSNOCODE:=SUBSTR(V_CUSNOCODE_IN,1,2);
SUB_A:=SUBSTR(V_CUSNOCODE,1,1);
SUB_B:=SUBSTR(V_CUSNOCODE,2,1);
IF V_CUSNOCODE='99' THEN
O_CUSNOCODE:='1A';
GOTO EEND;
END IF;
IF V_CUSNOCODE='9Z' THEN
O_CUSNOCODE:='A1';
GOTO EEND;
END IF;
IF V_CUSNOCODE='ZZ' THEN
O_CUSNOCODE:='aa';
GOTO EEND;
END IF;
SELECT A.ASCII_A INTO V_ASCII_A FROM DUAL
LEFT JOIN
( SELECT ASCII(SUB_A) AS ASCII_A FROM DUAL
)A ON 1=1;
SELECT A.ASCII_B INTO V_ASCII_B FROM DUAL
LEFT JOIN
( SELECT ASCII(SUB_B) AS ASCII_B FROM DUAL
)A ON 1=1;
-- 00~99 1A~9Z
--1第一位为数字,第二个为数字或字母
IF (V_ASCII_A >=48 AND V_ASCII_A<=57) AND( (V_ASCII_B >=48 AND V_ASCII_B<=57) OR (V_ASCII_B >=65 AND V_ASCII_B<=90) ) THEN
-- 1.1 第二个为字母
IF V_ASCII_B>=65 AND V_ASCII_B<=90 THEN
IF V_ASCII_A =48 THEN --开为为0,直接到 "1A"
O_CUSNOCODE:='1A';
GOTO EEND;
ELSIF V_ASCII_B =90 THEN --如果尾数为'Z'
O_CUSNOCODE:=CHR(V_ASCII_A+1)||'A';
GOTO EEND;
ELSE
O_CUSNOCODE:=SUB_A||CHR(V_ASCII_B+1);
GOTO EEND;
END IF ;
-- 1.2 第二个为数字
ELSE
O_CUSNOCODE:=TO_CHAR(TO_NUMBER(V_CUSNOCODE)+1,'00');
GOTO EEND;
END IF;
END IF;
-- A1~ZZ
IF V_ASCII_A >=65 AND V_ASCII_A<=90 THEN
IF V_ASCII_B=90 THEN --如果尾数为'Z'
O_CUSNOCODE:=CHR(V_ASCII_A+1)||'1';
GOTO EEND;
ELSIF V_ASCII_B=57 THEN --如果尾数为'9'
O_CUSNOCODE:=SUB_A||'A';
GOTO EEND;
ELSE
O_CUSNOCODE:=SUB_A||CHR(V_ASCII_B+1);
GOTO EEND;
END IF;
END IF;
<<EEND>>
NULL;
END;