[Oracle] SYS_REFCURSOR 변수값 확인하기

[Oracle] SYS_REFCURSOR 변수값 확인하기

1. Variable 이용

CREATE OR REPLACE PROCEDURE MY_PROC( P_RC OUT SYS_REFCURSOR ) AS BEGIN OPEN P_RC FOR SELECT 1 COL1 FROM DUAL; END; / VARIABLE RC REFCURSOR; EXEC MY_PROC( :RC ); PRINT RC;

2. DBMS_OUT 이용

DECLARE V_CUR SYS_REFCURSOR; V_A VARCHAR2(10); V_B VARCHAR2(10); BEGIN YOUR_PROC(V_CUR); LOOP FETCH V_CUR INTO V_A, V_B; EXIT WHEN V_CUR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(V_A || ' ' || V_B); END LOOP; CLOSE V_CUR; END;

3. 다른 프로시져 생성해서 확인

// 적용 프로시져 CREATE OR REPLACE PROCEDURE MY_PROC( V_NUMBER IN NUMBER,P_RC OUT SYS_REFCURSOR ) AS BEGIN OPEN P_RC FOR SELECT 1 COL1 FROM DUAL; END; // 확인 프로시져 CREATE OR REPLACE FUNCTION MY_PROC_TEST(V_NUMBER IN NUMBER) RETURN SYS_REFCURSOR AS P_RC SYS_REFCURSOR; BEGIN MY_PROC(V_NUMBER,P_RC); RETURN P_RC; END;