Cursor 사용 방법 3가지

1.커서의 내용을 미리 정의

%ROWTYPE: 테이블의 행을 나타내는 레코드 타입

DECLARE
TYPE MY_TYPE IS RECORD(
    NUM NUMBER,
    NAME VARCHAR2(12)
);
MY_REC MY_TYPE;
CURSOR MY_CUR IS SELECT 1 NUM, '홍길동' NAME FROM DUAL
                UNION ALL
                SELECT 2 NUM, '신데렐라' NAME FROM DUAL;
BEGIN
    OPEN MY_CUR;
    LOOP
        FETCH MY_CUR INTO MY_REC;
        EXIT WHEN MY_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(MY_REC.NUM || '_' || MY_REC.NAME);
    END LOOP;
    CLOSE MY_CUR;  
END;

DECLARE
MY_REC MY_TABLE%ROWTYPE;
CURSOR MY_CUR IS SELECT * FROM MY_TABLE;
BEGIN
    OPEN MY_CUR;
    LOOP
        FETCH MY_CUR INTO MY_REC;
        EXIT WHEN MY_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(MY_REC.NUM || '_' || MY_REC.NAME);
    END LOOP;
    CLOSE MY_CUR;  
END;

2.SYS_REFCURSOR를 활용, 커서만 선언 후 내용은 나중에 정의

DECLARE
TYPE MY_TYPE IS RECORD(
    NUM NUMBER,
    NAME VARCHAR2(12)
);
MY_REC MY_TYPE;
SYS_CUR SYS_REFCURSOR;
BEGIN
    OPEN SYS_CUR FOR SELECT 1 NUM, '홍길동' NAME FROM DUAL
                    UNION ALL
                    SELECT 2 NUM, '신데렐라' NAME FROM DUAL;
    LOOP
        FETCH SYS_CUR INTO MY_REC;
        EXIT WHEN SYS_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(MY_REC.NUM || '_' || MY_REC.NAME);
    END LOOP;
    CLOSE SYS_CUR;
END;

DECLARE
MY_REC MY_TABLE%ROWTYPE;
SYS_CUR SYS_REFCURSOR;
BEGIN
    OPEN SYS_CUR FOR SELECT * FROM MY_TABLE;
    LOOP
        FETCH SYS_CUR INTO MY_REC;
        EXIT WHEN SYS_CUR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(MY_REC.NUM || '_' || MY_REC.NAME);
    END LOOP;
    CLOSE SYS_CUR;
END;

3.커서를 따로 선언하지 않고 바로 사용

BEGIN
    FOR MY_REC2 IN (SELECT 1 NUM, '홍길동' NAME FROM DUAL
                UNION ALL
                SELECT 2 NUM, '신데렐라' NAME FROM DUAL)
    LOOP
        DBMS_OUTPUT.PUT_LINE(MY_REC2.NUM || '_' || MY_REC2.NAME);
    END LOOP;
END;