DECLARE
V_SEQ_NM VARCHAR2(200);
V_EVENT_NO VARCHAR2(200);
BEGIN
V_SEQ_NM := NULL;
SRIMS_AD_20130807.SP_GET_EVENT_NO( 'SQ_SMP_RESULT_EVENT', V_EVENT_NO);
DBMS_OUTPUT.PUT_LINE('V_SEQ_NM = ' || V_SEQ_NM );
DBMS_OUTPUT.PUT_LINE('V_EVENT_NO = ' || V_EVENT_NO );
COMMIT;
END;
/*------------------------------------------------------------------------------
-- Object Name: SRIMS_AD_20130807.SP_GET_EVENT_NO
-- Creation Date : 2013-08-07 오후 5:54:14
-- Last Modified : 2013-08-08 오전 9:23:30
-- Status : VALID
------------------------------------------------------------------------------*/
CREATE OR REPLACE PROCEDURE SRIMS_AD_20130807.SP_GET_EVENT_NO
(
V_SEQ_NM IN VARCHAR2,
V_EVENT_NO OUT VARCHAR2
)
IS
V_SQL VARCHAR2(32767);
V_CUR_DATE6 VARCHAR2(6);
N_CNT NUMBER;
BEGIN
--DBMS_OUTPUT.ENABLE;
V_CUR_DATE6 := TO_CHAR(SYSDATE, 'YYYYMM');
SELECT COUNT(*) INTO N_CNT
FROM EVENT_NO_MSG;
IF N_CNT > 0 THEN
UPDATE EVENT_NO_MSG
SET CRT_YM_B = V_CUR_DATE6;
ELSE
INSERT INTO EVENT_NO_MSG (CRT_YM_B)
VALUES (V_CUR_DATE6);
END IF;
SELECT COUNT(*) INTO N_CNT
FROM USER_OBJECTS
WHERE OBJECT_NAME = V_SEQ_NM
AND OBJECT_TYPE = 'SEQUENCE';
IF N_CNT = 0 THEN
V_SQL := 'CREATE SEQUENCE ' || V_SEQ_NM || ' INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE';
EXECUTE IMMEDIATE V_SQL;
UPDATE EVENT_NO_MSG
SET CRT_YM_A = V_CUR_DATE6;
ELSE
SELECT COUNT(*) INTO N_CNT
FROM EVENT_NO_MSG
WHERE CRT_YM_B <> CRT_YM_A;
IF N_CNT > 0 THEN
V_SQL := 'DROP SEQUENCE ' || V_SEQ_NM;
EXECUTE IMMEDIATE V_SQL;
UPDATE EVENT_NO_MSG
SET CRT_YM_A = V_CUR_DATE6;
V_SQL := 'CREATE SEQUENCE ' || V_SEQ_NM || ' INCREMENT BY 1 START WITH 1';
EXECUTE IMMEDIATE V_SQL;
END IF;
END IF;
V_SQL := ' SELECT ''' || V_CUR_DATE6 || ''' || LPAD(TO_CHAR(' || V_SEQ_NM || '.NEXTVAL), 7, ''0'') AS EVENT_NO FROM DUAL';
DBMS_OUTPUT.PUT_LINE('V_SQL = ' || V_SQL );
EXECUTE IMMEDIATE V_SQL INTO V_EVENT_NO;
RETURN;
END;
댓글 없음:
댓글 쓰기