SYS_REFCURSOR系统游标的使用

最新推荐文章于 2023-08-06 19:45:48 发布

转载最新推荐文章于 2023-08-06 19:45:48 发布 · 835 阅读

标签

#数据库 #面试

本文介绍了如何在Oracle中使用SYS_REFCURSOR系统游标，包括函数和存储过程返回系统游标的详细步骤，涵盖了单个表及多个表的查询操作。

AI 时代程序员必备技能

Codex、Claude Code、Cursor、Hermes Agent、OpenClaw等工程化实战专栏，讲透 AI 如何接管脏活累活

一键订阅

SYS_REFCURSOR系统游标的使用

http://blog.itpub.net/26736162/viewspace-2131977/

1.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;

结果如下所示：

1.2 存储过程返回系统游标

1.2.1 单个表

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;
/

1.2.2 多个表查询

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;

About Me

  ........................................................................................................................  
  ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除  
  ● 本文在itpub（  http://blog.itpub.net/26736162  ）、博客园（  http://www.cnblogs.com/lhrbest  ）和个人weixin公众号（  xiaomaimiaolhr  ）上有同步更新  
  ● 本文itpub地址：  http://blog.itpub.net/26736162   
  ● 本文博客园地址：  http://www.cnblogs.com/lhrbest   
  ● 本文pdf版、个人简介及小麦苗云盘地址：  http://blog.itpub.net/26736162/viewspace-1624453/   
  ● 数据库笔试面试题库及解答：  http://blog.itpub.net/26736162/viewspace-2134706/   
  ● DBA宝典今日头条号地址：  http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826   
  ........................................................................................................................  
  ● QQ群号：  230161599  （满）  、618766405   
  ● weixin群：可加我weixin，我拉大家进群，非诚勿扰  
  ● 联系我请加QQ好友  （   646634621   ）  ，注明添加缘由  
  ● 于 2019-03-01 06:00 ~ 2019-03-31 24:00 在魔都完成  
  ● 最新修改时间：2019-03-01 06:00 ~ 2019-03-31 24:00  
  ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解  
  ● 版权所有，欢迎分享本文，转载请保留出处  
  ........................................................................................................................  
  ●  小麦苗的微店  ：  https://weidian.com/s/793741433?wfr=c&ifr=shopdetail   
  ●  小麦苗出版的数据库类丛书  ：  http://blog.itpub.net/26736162/viewspace-2142121/   
  ●  小麦苗OCP、OCM、高可用网络班  ：  http://blog.itpub.net/26736162/viewspace-2148098/   
  ●  小麦苗腾讯课堂主页  ：  https://lhr.ke.qq.com/   
  ........................................................................................................................  
 使用   weixin客户端   扫描下面的二维码来关注小麦苗的weixin公众号（  xiaomaimiaolhr  ）及QQ群（DBA宝典）、添加小麦苗weixin，  学习最实用的数据库技术。  
 
  ........................................................................................................................  