SYS_REFCURSOR系统游标的使用

1.函数返回系统游标

CREATE OR REPLACE FUNCTION F_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER) RETURN SYS_REFCURSOR IS
    CUR_SYS SYS_REFCURSOR;
  BEGIN
    OPEN CUR_SYS FOR
      SELECT LEVEL P_LEVEL,
             T.EMPNO,
             T.ENAME,
             T.MGR,
             (LPAD(' ', 6 * (LEVEL - 1)) || LEVEL || ':' || T.ENAME || '(' ||
             T.EMPNO || ')') NAME_ALL,
             SUBSTR(SYS_CONNECT_BY_PATH(T.ENAME, '=>'), 3) ALL_NAME_LEVEL,
             CONNECT_BY_ROOT(T.ENAME) ROOT,
             DECODE(CONNECT_BY_ISLEAF, 1, 'Y', 0, 'N') IS_LEAF
        FROM SCOTT.EMP T
       START WITH T.EMPNO=P_EMPNO
      CONNECT BY NOCYCLE MGR = PRIOR EMPNO;
    RETURN CUR_SYS;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
查询：
SELECT F_GET_SYS_REFCURSOR_LHR(7566)  FROM   DUAL;

2.存储返回系统游标
单个表

CREATE OR REPLACE PROCEDURE PRO_GET_SYS_REFCURSOR_LHR(P_EMPNO NUMBER, SYS_CURSOR OUT SYS_REFCURSOR)    IS
      
  BEGIN
    OPEN SYS_CURSOR FOR
      SELECT *
        FROM SCOTT.EMP T
        WHERE T.EMPNO=P_EMPNO;
      
  END;
/
  
SET SERVEROUTPUT ON
DECLARE
    V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;
    V_ROWS           SCOTT.EMP%ROWTYPE;
BEGIN
    PRO_GET_SYS_REFCURSOR_LHR(7369, V_SYS_REFCURSOR_ROWS); 
    LOOP
        FETCH V_SYS_REFCURSOR_ROWS
            INTO V_ROWS;
        EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(V_ROWS.EMPNO);
    END LOOP;
    CLOSE V_SYS_REFCURSOR_ROWS;
END;
/

多个表查询

CREATE OR REPLACE PROCEDURE PRO_GET_SYS_REFCURSOR2_LHR(P_EMPNO NUMBER,
SYS_CURSOR OUT SYS_REFCURSOR) IS

BEGIN
OPEN SYS_CURSOR FOR
SELECT A.EMPNO,B.DEPTNO,B.DNAME
FROM SCOTT.EMP A,
SCOTT.DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND A.EMPNO = P_EMPNO;

END;
/

CREATE VIEW SCOTT.V_TMP AS
SELECT A.EMPNO,B.DEPTNO,B.DNAME
FROM SCOTT.EMP A,
SCOTT.DEPT B
WHERE A.DEPTNO = B.DEPTNO
AND 1=2;

SELECT * FROM SCOTT.V_TMP;

DECLARE
V_SYS_REFCURSOR_ROWS SYS_REFCURSOR;
V_ROWS SCOTT.V_TMP%ROWTYPE;
BEGIN
PRO_GET_SYS_REFCURSOR2_LHR(7369, V_SYS_REFCURSOR_ROWS);
LOOP
FETCH V_SYS_REFCURSOR_ROWS
INTO V_ROWS;
EXIT WHEN V_SYS_REFCURSOR_ROWS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(V_ROWS.EMPNO||’,’||V_ROWS.DEPTNO);
END LOOP;
CLOSE V_SYS_REFCURSOR_ROWS;
END;