［Oracle］重置序號 / 取號依日期格式化
1) 以Package + StoredProcedure + Function 實作
2) 重置序號實作方式分為 A. 原物件倒序 B. DROP/CREATE 重新建立
3) 取號時則以日期格式(YYYYMMDD) + 數字格式(ssss) 回傳
記載系統用的序號物件名稱與表單的關聯
CREATE TABLE MY_DOCUMENT (
DOC_TYPE VARCHAR2(128),
NUM_SEQ_NAME VARCHAR2(100)
)
/*************************************************************************
PART-1
NAME: MY_SEQ
PURPOSE: 處理序號物件 PACKAGE
*************************************************************************/
create or replace
PACKAGE MY_SEQ
IS
/*************************************************************************
NAME: GET_NUM_SEQ_NAME
PURPOSE: 回傳序號物件名稱 by DOC_TYPE (See MY_DOCUMENT)
RETURN: 序號物件名稱
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
FUNCTION GET_NUM_SEQ_NAME (pDOC_TYPE IN VARCHAR2)
RETURN VARCHAR2;
/*************************************************************************
NAME: NEXT_SEQ_BY_TYPE
PURPOSE: 回傳序號 by DOC_TYPE (See MY_DOCUMENT)
RETURN: Default is (YYYYMMDDssss). If s>9999回傳(YYYYMMDDsssss)s不格式化.
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
FUNCTION NEXT_SEQ_BY_TYPE (pDOC_TYPE IN VARCHAR2)
RETURN VARCHAR2;
/*************************************************************************
NAME: NEXT_SEQ
PURPOSE: 回傳序號
RETURN: Default is (YYYYMMDDssss). If s>9999回傳(YYYYMMDDsssss)s不格式化.
*************************************************************************/
FUNCTION NEXT_SEQ (pNUM_SEQ_NAME IN VARCHAR2)
RETURN VARCHAR2;
/*************************************************************************
NAME: RESET_SEQ_BY_TYPE
PURPOSE: 重置序號物件 by DOC_TYPE (See MY_DOCUMENT)
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
PROCEDURE RESET_SEQ_BY_TYPE (pDOC_TYPE IN VARCHAR2);
/*************************************************************************
NAME: RESET_SEQ
PURPOSE: 重置序號物件
*************************************************************************/
PROCEDURE RESET_SEQ (pNUM_SEQ_NAME IN VARCHAR2);
/*************************************************************************
NAME: RESET_SEQ_ALL
PURPOSE: 重置序號物件
*************************************************************************/
PROCEDURE RESET_SEQ_ALL;
/*************************************************************************
NAME: REGEN_SEQ_BY_TYPE
PURPOSE: 重新刪除/建立序號物件 by DOC_TYPE (See MY_DOCUMENT)
*************************************************************************/
PROCEDURE REGEN_SEQ_BY_TYPE (pDOC_TYPE IN VARCHAR2);
/*************************************************************************
NAME: REGEN_SEQ
PURPOSE: 重新刪除/建立序號物件
*************************************************************************/
PROCEDURE REGEN_SEQ (pNUM_SEQ_NAME IN VARCHAR2);
/*************************************************************************
NAME: REGEN_SEQ_ALL
PURPOSE: 刪除/建立序號物件 by DOC_TYPE (See MY_DOCUMENT)
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
PROCEDURE REGEN_SEQ_ALL;
END MY_SEQ;
/*************************************************************************
PART-2
NAME: MY_SEQ
PURPOSE: 處理序號物件 PACKAGE BODY
*************************************************************************/
create or replace
PACKAGE BODY MY_SEQ
IS
/*************************************************************************
NAME: GET_NUM_SEQ_NAME
PURPOSE: 回傳序號物件名稱 by DOC_TYPE (See MY_DOCUMENT)
RETURN: 序號物件名稱
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
FUNCTION GET_NUM_SEQ_NAME
(
pDOC_TYPE IN VARCHAR2)
RETURN VARCHAR2
IS
RTNVAL VARCHAR2(100);
BEGIN
-- select sequence object name
SELECT DISTINCT NUM_SEQ_NAME
INTO RTNVAL
FROM MY_DOCUMENT
WHERE DOC_TYPE = pDOC_TYPE;
RETURN RTNVAL;
END;
/*************************************************************************
NAME: NEXT_SEQ_BY_TYPE
PURPOSE: 回傳序號 by DOC_TYPE (See MY_DOCUMENT)
RETURN: Default is (YYYYMMDDssss). If s>9999回傳(YYYYMMDDsssss)s不格式化.
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
FUNCTION NEXT_SEQ_BY_TYPE
(
pDOC_TYPE IN VARCHAR2)
RETURN VARCHAR2
IS
pNUM_SEQ_NAME VARCHAR2(100);
BEGIN
pNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);
RETURN NEXT_SEQ (pNUM_SEQ_NAME);
END;
/*************************************************************************
NAME: NEXT_SEQ
PURPOSE: 回傳序號
RETURN: Default is (YYYYMMDDssss). If s>9999回傳(YYYYMMDDsssss)s不格式化.
*************************************************************************/
FUNCTION NEXT_SEQ
(
pNUM_SEQ_NAME IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
pNUM_SEQ NUMBER;
pPREFIX VARCHAR2 (6);
pRTNVAL VARCHAR2 (50);
BEGIN
-- YYYYMMDD
SELECT TO_CHAR (MYDATE, 'YYYYMMDD') INTO pPREFIX FROM DUAL;
-- sequence number
EXECUTE IMMEDIATE 'SELECT ' || pNUM_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO pNUM_SEQ;
-- format
IF pNUM_SEQ > 9999 THEN
pRTNVAL := pPREFIX || TO_CHAR (pNUM_SEQ);
ELSE
pRTNVAL := pPREFIX || TO_CHAR (pNUM_SEQ, 'FM0000');
END IF;
RETURN pRTNVAL;
END;
/*************************************************************************
NAME: RESET_SEQ_BY_TYPE
PURPOSE: 重置序號物件 by DOC_TYPE (See MY_DOCUMENT)
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
PROCEDURE RESET_SEQ_BY_TYPE
(
pDOC_TYPE IN VARCHAR2)
IS
pNUM_SEQ_NAME VARCHAR2(100);
BEGIN
pNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);
RESET_SEQ (pNUM_SEQ_NAME);
END;
/*************************************************************************
NAME: RESET_SEQ
PURPOSE: 重置序號物件
*************************************************************************/
PROCEDURE RESET_SEQ
(
pNUM_SEQ_NAME IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
pNUM_SEQ NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('RESET SEQUENCE : ' || pNUM_SEQ_NAME);
-- current sequence
EXECUTE IMMEDIATE 'SELECT ' || pNUM_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO pNUM_SEQ;
-- set increment negative
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pNUM_SEQ_NAME || ' INCREMENT BY -' || pNUM_SEQ || ' MINVALUE 0';
-- next sequence (reset to 0)
EXECUTE IMMEDIATE 'SELECT ' || pNUM_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO pNUM_SEQ;
-- set increment positive
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pNUM_SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
ROLLBACK;
RAISE; -- reraise the current exception
END;
/*************************************************************************
NAME: RESET_SEQ_ALL
PURPOSE: 重置序號物件
*************************************************************************/
PROCEDURE RESET_SEQ_ALL
IS
CURSOR pCUR
IS
SELECT DISTINCT NUM_SEQ_NAME
FROM MY_DOCUMENT
WHERE NUM_SEQ_NAME IS NOT NULL;
pNUM_SEQ_NAME_REC pCUR%ROWTYPE;
pERROR_COUNT NUMBER := 0;
pERROR_NUM_SEQ_NAME VARCHAR2(2000) := '';
BEGIN
OPEN pCUR;
LOOP
FETCH pCUR INTO pNUM_SEQ_NAME_REC;
EXIT
WHEN pCUR%NOTFOUND;
BEGIN
-- Reset sequence object
RESET_SEQ (pNUM_SEQ_NAME_REC.NUM_SEQ_NAME);
EXCEPTION
WHEN OTHERS THEN
pERROR_COUNT := pERROR_COUNT + 1;
pERROR_NUM_SEQ_NAME := pERROR_NUM_SEQ_NAME || ',' || pNUM_SEQ_NAME_REC.NUM_SEQ_NAME;
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END LOOP;
CLOSE pCUR;
IF pERROR_COUNT > 0 THEN
RAISE_APPLICATION_ERROR ( -20999, 'Error Count : ' || TO_CHAR (pERROR_COUNT) || ' , OBJECT : ' || pERROR_NUM_SEQ_NAME);
END IF;
END;
/*************************************************************************
NAME: REGEN_SEQ_BY_TYPE
PURPOSE: 重新刪除/建立序號物件 by DOC_TYPE (See MY_DOCUMENT)
*************************************************************************/
PROCEDURE REGEN_SEQ_BY_TYPE
(
pDOC_TYPE IN VARCHAR2)
IS
pNUM_SEQ_NAME VARCHAR2(100);
BEGIN
pNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);
REGEN_SEQ (pNUM_SEQ_NAME);
END;
/*************************************************************************
NAME: REGEN_SEQ
PURPOSE: 重新刪除/建立序號物件
*************************************************************************/
PROCEDURE REGEN_SEQ
(
pNUM_SEQ_NAME IN VARCHAR2)
IS
pDDL_STR VARCHAR2(1000);
BEGIN
-- GET sequence object and remove start with argument. (cause the ddl include start with curval)
SELECT DBMS_METADATA.GET_DDL ('SEQUENCE', pNUM_SEQ_NAME)
INTO pDDL_STR
FROM DUAL;
SELECT REGEXP_REPLACE (pDDL_STR, 'START WITH \d+', 'START WITH 1')
INTO pDDL_STR
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(pDDL_STR);
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || pNUM_SEQ_NAME;
EXECUTE IMMEDIATE pDDL_STR;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' >>>>' || pDDL_STR );
END;
/*************************************************************************
NAME: REGEN_SEQ_ALL
PURPOSE: 刪除/建立序號物件 by DOC_TYPE (See MY_DOCUMENT)
EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.
*************************************************************************/
PROCEDURE REGEN_SEQ_ALL
IS
CURSOR pCUR
IS
SELECT DISTINCT NUM_SEQ_NAME
FROM MY_DOCUMENT
WHERE NUM_SEQ_NAME IS NOT NULL;
pNUM_SEQ_NAME_REC pCUR%ROWTYPE;
pERROR_COUNT NUMBER := 0;
pERROR_NUM_SEQ_NAME VARCHAR2(2000) := '';
BEGIN
OPEN pCUR;
LOOP
FETCH pCUR INTO pNUM_SEQ_NAME_REC;
EXIT
WHEN pCUR%NOTFOUND;
BEGIN
-- Reset sequence object
REGEN_SEQ (pNUM_SEQ_NAME_REC.NUM_SEQ_NAME);
EXCEPTION
WHEN OTHERS THEN
pERROR_COUNT := pERROR_COUNT + 1;
pERROR_NUM_SEQ_NAME := pERROR_NUM_SEQ_NAME || ',' || pNUM_SEQ_NAME_REC.NUM_SEQ_NAME;
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;
END LOOP;
CLOSE pCUR;
IF pERROR_COUNT > 0 THEN
RAISE_APPLICATION_ERROR ( -20999, 'Error Count : ' || TO_CHAR (pERROR_COUNT) || ' , OBJECT : ' || pERROR_NUM_SEQ_NAME);
END IF;
END;
END MY_SEQ;
/*************************************************************************
PART-3
NAME: TEST CASE
PURPOSE: For Test
*************************************************************************/
GRANT CREATE SEQUENCE TO MYUSER; -- must execute before invoke REGEN
DECLARE
VAL varchar2(50);
BEGIN
DBMS_OUTPUT.PUT_LINE('--------' );
VAL := MYUSER.MY_SEQ.GET_NUM_SEQ_NAME('BUTN') ;
DBMS_OUTPUT.PUT_LINE('GET_NUM_SEQ_NAME : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
VAL := MYUSER.MY_SEQ.NEXT_SEQ('MY_BULLETIN_NUMBER_S');
DBMS_OUTPUT.PUT_LINE('NEXT_SEQ : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
VAL := MYUSER.MY_SEQ.NEXT_SEQ_BY_TYPE('BUTN');
DBMS_OUTPUT.PUT_LINE('NEXT_SEQ_BY_TYPE : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.RESET_SEQ('MY_BULLETIN_NUMBER_S');
VAL := MYUSER.MY_SEQ.NEXT_SEQ('MY_BULLETIN_NUMBER_S');
DBMS_OUTPUT.PUT_LINE('RESET_SEQ > NEXT_SEQ : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.RESET_SEQ_BY_TYPE('BUTN');
VAL := MYUSER.MY_SEQ.NEXT_SEQ_BY_TYPE('BUTN');
DBMS_OUTPUT.PUT_LINE('RESET_SEQ_BY_TYPE > NEXT_SEQ_BY_TYPE : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.RESET_SEQ_ALL;
VAL := MYUSER.MY_SEQ.NEXT_SEQ('MY_BULLETIN_NUMBER_S');
DBMS_OUTPUT.PUT_LINE('RESET_SEQ_ALL > NEXT_SEQ : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.REGEN_SEQ ('MY_BULLETIN_NUMBER_S');
VAL := MYUSER.MY_SEQ.NEXT_SEQ('MY_BULLETIN_NUMBER_S');
DBMS_OUTPUT.PUT_LINE('REGEN_SEQ > NEXT_SEQ : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.REGEN_SEQ_BY_TYPE ('BUTN');
VAL := MYUSER.MY_SEQ.NEXT_SEQ_BY_TYPE('BUTN');
DBMS_OUTPUT.PUT_LINE('REGEN_SEQ_BY_TYPE > NEXT_SEQ_BY_TYPE : ' || VAL );
DBMS_OUTPUT.PUT_LINE('--------' );
MYUSER.MY_SEQ.REGEN_SEQ_ALL;
VAL := MYUSER.MY_SEQ.NEXT_SEQ('MY_BULLETIN_NUMBER_S');
DBMS_OUTPUT.PUT_LINE('REGEN_SEQ_ALL > NEXT_SEQ : ' || VAL );
END;