oracle 取出序号,［Oracle］重置序号 / 取号依日期格式化

［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-1NAME: MY_SEQPURPOSE: 处理序号对象 PACKAGE*/create or replacePACKAGE MY_SEQIS/*NAME: GET_NUM_SEQ_NAMEPURPOSE: 回传序号对象名称 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_TYPEPURPOSE: 回传序号 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_SEQPURPOSE: 回传序号RETURN: Default is (YYYYMMDDssss). If s>9999回传(YYYYMMDDsssss)s不格式化.*/FUNCTION NEXT_SEQ (pNUM_SEQ_NAME IN VARCHAR2)RETURN VARCHAR2;/*NAME: RESET_SEQ_BY_TYPEPURPOSE: 重置序号对象 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_SEQPURPOSE: 重置序号对象*/PROCEDURE RESET_SEQ (pNUM_SEQ_NAME IN VARCHAR2);/*NAME: RESET_SEQ_ALLPURPOSE: 重置序号对象*/PROCEDURE RESET_SEQ_ALL;/*NAME: REGEN_SEQ_BY_TYPEPURPOSE: 重新删除/建立序号对象 by DOC_TYPE (See MY_DOCUMENT)*/PROCEDURE REGEN_SEQ_BY_TYPE (pDOC_TYPE IN VARCHAR2);/*NAME: REGEN_SEQPURPOSE: 重新删除/建立序号对象*/PROCEDURE REGEN_SEQ (pNUM_SEQ_NAME IN VARCHAR2);/*NAME: REGEN_SEQ_ALLPURPOSE: 删除/建立序号对象 by DOC_TYPE (See MY_DOCUMENT)EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.*/PROCEDURE REGEN_SEQ_ALL;END MY_SEQ;/*PART-2NAME: MY_SEQPURPOSE: 处理序号对象 PACKAGE BODY*/create or replacePACKAGE BODY MY_SEQIS/*NAME: GET_NUM_SEQ_NAMEPURPOSE: 回传序号对象名称 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 VARCHAR2ISRTNVAL VARCHAR2(100);BEGIN-- select sequence object nameSELECT DISTINCT NUM_SEQ_NAMEINTO RTNVALFROM MY_DOCUMENTWHERE DOC_TYPE = pDOC_TYPE;RETURN RTNVAL;END;/*NAME: NEXT_SEQ_BY_TYPEPURPOSE: 回传序号 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 VARCHAR2ISpNUM_SEQ_NAME VARCHAR2(100);BEGINpNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);RETURN NEXT_SEQ (pNUM_SEQ_NAME);END;/*NAME: NEXT_SEQPURPOSE: 回传序号RETURN: Default is (YYYYMMDDssss). If s>9999回传(YYYYMMDDsssss)s不格式化.*/FUNCTION NEXT_SEQ(pNUM_SEQ_NAME IN VARCHAR2)RETURN VARCHAR2ISPRAGMA AUTONOMOUS_TRANSACTION;pNUM_SEQ NUMBER;pPREFIX VARCHAR2 (6);pRTNVAL VARCHAR2 (50);BEGIN-- YYYYMMDDSELECT TO_CHAR (MYDATE, 'YYYYMMDD') INTO pPREFIX FROM DUAL;-- sequence numberEXECUTE IMMEDIATE 'SELECT ' || pNUM_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO pNUM_SEQ;-- formatIF pNUM_SEQ > 9999 THENpRTNVAL := pPREFIX || TO_CHAR (pNUM_SEQ);ELSEpRTNVAL := pPREFIX || TO_CHAR (pNUM_SEQ, 'FM0000');END IF;RETURN pRTNVAL;END;/*NAME: RESET_SEQ_BY_TYPEPURPOSE: 重置序号对象 by DOC_TYPE (See MY_DOCUMENT)EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.*/PROCEDURE RESET_SEQ_BY_TYPE(pDOC_TYPE IN VARCHAR2)ISpNUM_SEQ_NAME VARCHAR2(100);BEGINpNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);RESET_SEQ (pNUM_SEQ_NAME);END;/*NAME: RESET_SEQPURPOSE: 重置序号对象*/PROCEDURE RESET_SEQ(pNUM_SEQ_NAME IN VARCHAR2)ISPRAGMA AUTONOMOUS_TRANSACTION;pNUM_SEQ NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('RESET SEQUENCE : ' || pNUM_SEQ_NAME);-- current sequenceEXECUTE IMMEDIATE 'SELECT ' || pNUM_SEQ_NAME || '.NEXTVAL FROM DUAL' INTO pNUM_SEQ;-- set increment negativeEXECUTE 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 positiveEXECUTE IMMEDIATE 'ALTER SEQUENCE ' || pNUM_SEQ_NAME || ' INCREMENT BY 1 MINVALUE 0';COMMIT;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);ROLLBACK;RAISE; -- reraise the current exceptionEND;/*NAME: RESET_SEQ_ALLPURPOSE: 重置序号对象*/PROCEDURE RESET_SEQ_ALLISCURSOR pCURISSELECT DISTINCT NUM_SEQ_NAMEFROM MY_DOCUMENTWHERE NUM_SEQ_NAME IS NOT NULL;pNUM_SEQ_NAME_REC pCUR%ROWTYPE;pERROR_COUNT NUMBER := 0;pERROR_NUM_SEQ_NAME VARCHAR2(2000) := '';BEGINOPEN pCUR;LOOPFETCH pCUR INTO pNUM_SEQ_NAME_REC;EXITWHEN pCUR%NOTFOUND;BEGIN-- Reset sequence objectRESET_SEQ (pNUM_SEQ_NAME_REC.NUM_SEQ_NAME);EXCEPTIONWHEN OTHERS THENpERROR_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 THENRAISE_APPLICATION_ERROR ( -20999, 'Error Count : ' || TO_CHAR (pERROR_COUNT) || ' , OBJECT : ' || pERROR_NUM_SEQ_NAME);END IF;END;/*NAME: REGEN_SEQ_BY_TYPEPURPOSE: 重新删除/建立序号对象 by DOC_TYPE (See MY_DOCUMENT)*/PROCEDURE REGEN_SEQ_BY_TYPE(pDOC_TYPE IN VARCHAR2)ISpNUM_SEQ_NAME VARCHAR2(100);BEGINpNUM_SEQ_NAME := GET_NUM_SEQ_NAME (pDOC_TYPE);REGEN_SEQ (pNUM_SEQ_NAME);END;/*NAME: REGEN_SEQPURPOSE: 重新删除/建立序号对象*/PROCEDURE REGEN_SEQ(pNUM_SEQ_NAME IN VARCHAR2)ISpDDL_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_STRFROM DUAL;SELECT REGEXP_REPLACE (pDDL_STR, 'START WITH d+', 'START WITH 1')INTO pDDL_STRFROM DUAL;DBMS_OUTPUT.PUT_LINE(pDDL_STR);EXECUTE IMMEDIATE 'DROP SEQUENCE ' || pNUM_SEQ_NAME;EXECUTE IMMEDIATE pDDL_STR;EXCEPTIONWHEN OTHERS THENRAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' >>>>' || pDDL_STR );END;/*NAME: REGEN_SEQ_ALLPURPOSE: 删除/建立序号对象 by DOC_TYPE (See MY_DOCUMENT)EXCEPTION: If DOC_TYPE not in MY_DOCUMENT.*/PROCEDURE REGEN_SEQ_ALLISCURSOR pCURISSELECT DISTINCT NUM_SEQ_NAMEFROM MY_DOCUMENTWHERE NUM_SEQ_NAME IS NOT NULL;pNUM_SEQ_NAME_REC pCUR%ROWTYPE;pERROR_COUNT NUMBER := 0;pERROR_NUM_SEQ_NAME VARCHAR2(2000) := '';BEGINOPEN pCUR;LOOPFETCH pCUR INTO pNUM_SEQ_NAME_REC;EXITWHEN pCUR%NOTFOUND;BEGIN-- Reset sequence objectREGEN_SEQ (pNUM_SEQ_NAME_REC.NUM_SEQ_NAME);EXCEPTIONWHEN OTHERS THENpERROR_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 THENRAISE_APPLICATION_ERROR ( -20999, 'Error Count : ' || TO_CHAR (pERROR_COUNT) || ' , OBJECT : ' || pERROR_NUM_SEQ_NAME);END IF;END;END MY_SEQ;/*PART-3NAME: TEST CASEPURPOSE: For Test*/GRANT CREATE SEQUENCE TO MYUSER; -- must execute before invoke REGENDECLAREVAL varchar2(50);BEGINDBMS_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;