반응형

오라클 커서 및 벌크 콜렉트 사용 예제


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;

/


반응형
LIST

+ Recent posts