[Oracle - PL_SQL] SYS_REFCURSOR 커서 출력

CREATE OR REPLACE PROCEDURE TEST
(
	pSystemSe IN varchar2, 
	pTableName IN varchar2, 
	oCursor OUT SYS_REFCURSOR
	--oErrCode OUT Number
)
IS 
BEGIN 
	OPEN oCursor FOR
		SELECT COLUMN_NAME, COLUMN_COMMENTS FROM TEST_TABLE
		WHERE SYSTEM_SE = pSystemSe AND TABLE_NAME = pTableName;
	EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQL ERROR MESSAGE: ' || SQLERRM);
END TEST;

 

DECLARE
    COLUMN_NAME        VARCHAR2(100);
    COLUMN_COMMENTS    VARCHAR2(100);
    v_test_refcur    SYS_REFCURSOR;
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('COLUMN_NAME', 50, ' ') || '| COLUMN_COMMENTS');
    DBMS_OUTPUT.PUT_LINE(RPAD('-', 100, '-'));
    TEST('AA','AAAA', v_test_refcur);
    LOOP
        FETCH v_test_refcur INTO COLUMN_NAME, COLUMN_COMMENTS;
        EXIT WHEN v_test_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(RPAD(COLUMN_NAME, 50, ' ') || '| ' || COLUMN_COMMENTS);
    END LOOP;
    CLOSE v_test_refcur;
END;

SYS_REFCURSOR 루프돌리기