커서의 내용을 미리 정의 해 놓고 사용하는 방법.
비추천
커서의 내용을 정할 때 select 문제 동적으로 parameter가 넘어가야 할 경우 사용이 불가능 하다. 왜냐하면 BEGIN 전에 정의하기 때문이다.
커서를 미리 정의 할 필요도 없고, 변수를 미리 만들어 놓을 필요도 없다.
출처: https://blog.kjslab.com/20 [새 보다 자유롭게 - 자바 프로그래머 갈매기S(Jonathan Livingston Seagull)]
오라클 커서 및 벌크 콜렉트 사용 예제
CREATE OR REPLACE PROCEDURE SP_OGNI_REC_DATA_CREATE
AS
-- 커서의 내용을 미리 정의 해 놓고 사용하는 방법.
CURSOR CUR_OGNI IS SELECT * FROM TB_OGNI;
-- 커서 변수를 미리 만들어 놓고 불러서 사용하는 방법.
-- CUR_OGNI SYS_REFCURSOR;
ROW_OGNI TB_OGNI%ROWTYPE;
CURSOR CUR_CODE IS SELECT CODE_ID FROM TB_CODE WHERE CODE_INI = 'OGNI_REC_ITEM';
COL_CODE_ID TB_CODE.CODE_ID%TYPE;
ROW_OGNI_REC TB_OGNI_REC%ROWTYPE;
P_APLC_ST_DATE TB_OGNI_REC.APLC_ST_DATE%TYPE;
P_IS_INSERT CHAR(1);
-- BULK COLLECT 사용을 위해
TYPE TBL_CLSE_OGNI_TYPE IS TABLE OF TB_CLSE_OGNI%ROWTYPE INDEX BY BINARY_INTEGER;
TBL_CLSE_OGNI TBL_CLSE_OGNI_TYPE;
-- //BULK COLLECT 사용을 위해
BEGIN
/*
-- EXECUTE TB_OGNI_REC_MIG_P
-- SET SERVEROUTPUT ON -- DBMS_OUTPUT.PUT_LINE 출력하려면 실행.
-- DROP TABLE TB_OGNI_REC_TEMP
CREATE TABLE TB_OGNI_REC_TEMP
(
OGNI_NO NUMBER(11, 0)
, OGNI_NAME VARCHAR2(60 BYTE)
, OGNI_TYPE VARCHAR2(3 BYTE)
, OGNI_ST_DATE VARCHAR2(8 BYTE)
, OGNI_ED_DATE VARCHAR2(8 BYTE) DEFAULT '20991231'
)
-- 템프테이블 사용하려면 미리 생성하여 사용.
-- 이용이 끝나면 하단에서 truncate 처리
*/
DBMS_OUTPUT.PUT_LINE('== START');
DBMS_OUTPUT.PUT_LINE('==== CURSOR START');
OPEN CUR_OGNI;
-- CUR_OGNI 를 상단 주석처럼 SYS_REFCURSOR 로 사용한 경우
-- OPEN CUR_OGNI FOR SELECT * FROM TB_OGNI;
-- //CUR_OGNI 를 상단 주석처럼 SYS_REFCURSOR 로 사용한 경우
LOOP
FETCH CUR_OGNI INTO ROW_OGNI;
EXIT WHEN CUR_OGNI%NOTFOUND;
OPEN CUR_CODE;
LOOP
FETCH CUR_CODE INTO COL_CODE_ID;
EXIT WHEN CUR_CODE%NOTFOUND;
P_IS_INSERT := 'N';
BEGIN
SELECT * INTO ROW_OGNI_REC
FROM (SELECT * FROM TB_OGNI_REC WHERE OGNI_NO = ROW_OGNI.OGNI_NO AND OGNI_REC_ITEM = COL_CODE_ID ORDER BY OGNI_REC_ITEM_SEQ DESC)
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_IS_INSERT := 'Y';
END;
IF P_IS_INSERT = 'Y' THEN
-- DBMS_OUTPUT.PUT_LINE('====== INSERT:' || COL_CODE_ID);
CASE COL_CODE_ID
WHEN 'NAME' THEN
INSERT INTO TB_OGNI_REC ( OGNI_NO, OGNI_REC_ITEM, OGNI_REC_ITEM_SEQ, OGNI_REC_VAL, APLC_ST_DATE, APLC_ED_DATE, RGST_DATE, RGST_USER_NO)
VALUES(ROW_OGNI.OGNI_NO, COL_CODE_ID, 1, ROW_OGNI.OGNI_NAME, ROW_OGNI.OGNI_ST_DATE, ROW_OGNI.OGNI_ED_DATE, ROW_OGNI.RGST_DATE, ROW_OGNI.RGST_USER_NO);
WHEN 'TYPE' THEN
INSERT INTO TB_OGNI_REC ( OGNI_NO, OGNI_REC_ITEM, OGNI_REC_ITEM_SEQ, OGNI_REC_VAL, APLC_ST_DATE, APLC_ED_DATE, RGST_DATE, RGST_USER_NO)
VALUES(ROW_OGNI.OGNI_NO, COL_CODE_ID, 1, ROW_OGNI.OGNI_TYPE, ROW_OGNI.OGNI_ST_DATE, ROW_OGNI.OGNI_ED_DATE, ROW_OGNI.RGST_DATE, ROW_OGNI.RGST_USER_NO);
END CASE;
SELECT * INTO ROW_OGNI_REC
FROM (SELECT * FROM TB_OGNI_REC WHERE OGNI_NO = ROW_OGNI.OGNI_NO AND OGNI_REC_ITEM = COL_CODE_ID ORDER BY OGNI_REC_ITEM_SEQ DESC)
WHERE ROWNUM = 1;
END IF;
SELECT * BULK COLLECT INTO TBL_CLSE_OGNI FROM TB_CLSE_OGNI WHERE OGNI_NO = ROW_OGNI.OGNI_NO ORDER BY CLSE_YM;
FOR i IN 1..TBL_CLSE_OGNI.COUNT() LOOP
IF i = 1 THEN
CASE COL_CODE_ID
WHEN 'NAME' THEN
ROW_OGNI_REC.OGNI_REC_VAL := TBL_CLSE_OGNI(i).OGNI_NAME;
WHEN 'TYPE' THEN
ROW_OGNI_REC.OGNI_REC_VAL := TBL_CLSE_OGNI(i).OGNI_TYPE;
END CASE;
UPDATE TB_OGNI_REC
SET OGNI_REC_VAL = ROW_OGNI_REC.OGNI_REC_VAL
WHERE OGNI_NO = ROW_OGNI_REC.OGNI_NO
AND OGNI_REC_ITEM = COL_CODE_ID
AND OGNI_REC_ITEM_SEQ = 1;
ELSE
P_IS_INSERT := 'N';
CASE COL_CODE_ID
WHEN 'NAME' THEN
IF (ROW_OGNI_REC.OGNI_REC_VAL <> TBL_CLSE_OGNI(i).OGNI_NAME) THEN
P_IS_INSERT:='Y';
ROW_OGNI_REC.OGNI_REC_VAL := TBL_CLSE_OGNI(i).OGNI_NAME;
END IF;
WHEN 'TYPE' THEN
IF (ROW_OGNI_REC.OGNI_REC_VAL <> TBL_CLSE_OGNI(i).OGNI_TYPE) THEN
P_IS_INSERT:='Y';
ROW_OGNI_REC.OGNI_REC_VAL := TBL_CLSE_OGNI(i).OGNI_TYPE;
END IF;
END CASE;
IF P_IS_INSERT = 'Y' THEN
-- 기존 데이터 종료 날짜 수정
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(TBL_CLSE_OGNI(i).CLSE_YMD, 'YYYYMMDD'), -1), 'YYYYMMDD') INTO P_APLC_ST_DATE FROM DUAL;
IF ROW_OGNI_REC.APLC_ED_DATE <= P_APLC_ST_DATE THEN
UPDATE TB_OGNI_REC
SET OGNI_REC_VAL = ROW_OGNI_REC.OGNI_REC_VAL
WHERE OGNI_NO = ROW_OGNI_REC.OGNI_NO
AND OGNI_REC_ITEM = COL_CODE_ID
AND OGNI_REC_ITEM_SEQ = ROW_OGNI_REC.OGNI_REC_ITEM_SEQ;
ELSE
UPDATE TB_OGNI_REC
SET APLC_ED_DATE = (SELECT TO_CHAR(ADD_MONTHS(TO_DATE(TBL_CLSE_OGNI(i).CLSE_YM||'01', 'YYYYMMDD'), -1) - 1, 'YYYYMMDD') FROM DUAL)
WHERE OGNI_NO = ROW_OGNI_REC.OGNI_NO
AND OGNI_REC_ITEM = COL_CODE_ID
AND OGNI_REC_ITEM_SEQ = ROW_OGNI_REC.OGNI_REC_ITEM_SEQ;
-- 신규데이터 입력
ROW_OGNI_REC.APLC_ST_DATE := P_APLC_ST_DATE;
ROW_OGNI_REC.OGNI_REC_ITEM_SEQ := ROW_OGNI_REC.OGNI_REC_ITEM_SEQ + 1;
INSERT INTO TB_OGNI_REC ( OGNI_NO, OGNI_REC_ITEM, OGNI_REC_ITEM_SEQ, OGNI_REC_VAL, APLC_ST_DATE, APLC_ED_DATE, RGST_DATE, RGST_USER_NO)
VALUES(ROW_OGNI_REC.OGNI_NO, COL_CODE_ID, ROW_OGNI_REC.OGNI_REC_ITEM_SEQ, ROW_OGNI_REC.OGNI_REC_VAL, ROW_OGNI_REC.APLC_ST_DATE, ROW_OGNI_REC.APLC_ED_DATE, ROW_OGNI_REC.RGST_DATE, ROW_OGNI_REC.RGST_USER_NO);
END IF;
END IF;
END IF;
END LOOP;
END LOOP;
CLOSE CUR_CODE;
END LOOP;
CLOSE CUR_OGNI;
DBMS_OUTPUT.PUT_LINE('==== CURSOR END');
-- EXECUTE IMMEDIATE 'TRUNCATE TABLE TB_OGNI_REC_TEMP';
DBMS_OUTPUT.PUT_LINE('== END');
END;
/