채번 SP - 날짜 + SEQUECNE 조합

CREATE OR REPLACE PROCEDURE SP_GET_DAY_SEQ
(
    P_SEQ_NM    IN VARCHAR2        -- 이용할 SEQUENCE NAME
    ,P_YYYYMMDD    IN VARCHAR2        -- YYYYMMDD
    ,P_OUT_VAL    OUT VARCHAR2
      ,P_SQLCODE  OUT INTEGER    -- SQLCODE : 출력_에러코드
    ,P_SQLMSG   OUT VARCHAR2    --ERROR MESSAGE : 출력_에러코드
)
/***********************************************************************
PROGRAM NAME    : SP_GET_DAY_SEQ                         
CREATOR         : jeeta
CREATION DATE   : 2009.09.05
PROGRAM DESC.      : 특정 일자 SEQ 를 조회하여 NEXTVAL RETURN
CONDITION        :
    * SEQUENCE NAME FORMAT : TABLE_NAME || DD || 'S'
                            ( LIKE 'T_JOB_BASE_01S' )
------------------------------------------------------------------------
e.g)
DECLARE
    P_OUT_VAL VARCHAR2(100);
    P_SQLCODE INTEGER;
    P_SQLMSG VARCHAR2(200);

BEGIN
    SP_GET_DAY_SEQ( 'T_JOB_BASE', '20090905', P_OUT_VAL, P_SQLCODE, P_SQLMSG);
    DBMS_OUTPUT.PUT_LINE('P_OUT_VAL:' || P_OUT_VAL );
END;
==> return value is like '2009090500000014'
--***********************************************************************/
IS
       
V_SEQ_NM    VARCHAR2(40);
V_SEQ_LEN    NUMBER(2);
V_SQL        VARCHAR2(200);

V_NEXT_SEQ  NUMBER(13);
V_CURSOR        NUMBER;
V_EXEC       INTEGER;

V_OUT_VAL VARCHAR2(30);

BEGIN

    V_OUT_VAL := '';
    P_OUT_VAL := '';

    -- GET SEQUENCE NAME   
    BEGIN
        /*
        SELECT
        FROM ALL_SEQUENCES
        WHERE 0 = 0
        AND SEQUENCE_NAME LIKE 'T_JOB_BASE' || '%'
        AND SUBSTR(SEQUENCE_NAME, LENGTH(SEQUENCE_NAME)-2,2) = SUBSTR('20090905', LENGTH('20090905')-1,2)
        */
                       
        SELECT SEQUENCE_NAME, LENGTH(MAX_VALUE)
        INTO V_SEQ_NM, V_SEQ_LEN
        FROM ALL_SEQUENCES
        WHERE 0 = 0
        AND SEQUENCE_NAME LIKE P_SEQ_NM || '%'
        AND SUBSTR(SEQUENCE_NAME, LENGTH(SEQUENCE_NAME)-2,2) = SUBSTR(P_YYYYMMDD, LENGTH(P_YYYYMMDD)-1,2);
       
        EXCEPTION WHEN OTHERS THEN
            P_SQLCODE := SQLCODE;
            P_SQLMSG  := SUBSTR(SQLERRM,1,200);
            RETURN;
    END;                                            
   
    -- GET NEXT VAL OF SEQUENCE
    BEGIN
        V_SQL := 'SELECT ' || V_SEQ_NM || '.NEXTVAL FROM DUAL';

        V_CURSOR := DBMS_SQL.OPEN_CURSOR;

        DBMS_SQL.PARSE(V_CURSOR,V_SQL,DBMS_SQL.NATIVE);
        DBMS_SQL.DEFINE_COLUMN(V_CURSOR,1,V_NEXT_SEQ);
        V_EXEC := DBMS_SQL.EXECUTE(V_CURSOR);
       
        IF DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0  THEN
            DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_NEXT_SEQ);
        END IF;
               
        EXCEPTION WHEN OTHERS THEN
            P_SQLCODE := SQLCODE;
            P_SQLMSG  := SUBSTR(SQLERRM,1,200);
            RETURN;
    END;
   
    -- REMAKE VALUE USING SEQ.NEXTVAL
    BEGIN         
       
        IF LENGTH(V_NEXT_SEQ) > 0 THEN
            V_OUT_VAL := P_YYYYMMDD || LPAD(V_NEXT_SEQ, V_SEQ_LEN, '0');
        END IF;

    END;
   
    P_OUT_VAL := V_OUT_VAL;
   
END;