Oracle中使用游标表达式（CURSOR expression）及动态游标（SYS_REFCURSOR）

ゞ浴缸里的玫瑰 2022-04-04 03:12 449阅读 0赞

查看Oracle官方文档：

[https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CURSOR-Expressions.html\#GUID-B28362BE-8831-4687-89CF-9F77DB3698D2][https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_CURSOR-Expressions.html_GUID-B28362BE-8831-4687-89CF-9F77DB3698D2]

# CURSOR Expressions #

A `CURSOR` expression returns a nested cursor. This form of expression is equivalent to the PL/SQL `REF` `CURSOR` and can be passed as a `REF` `CURSOR` argument to a function.

![Description of cursor\_expression.eps follows][Description of cursor_expression.eps follows]  
[Description of the illustration cursor\_expression.eps][Description of the illustration cursor_expression.eps]

A nested cursor is implicitly opened when the cursor expression is evaluated. For example, if the cursor expression appears in a select list, a nested cursor will be opened for each row fetched by the query. The nested cursor is closed only when:

*  The nested cursor is explicitly closed by the user
 *  The parent cursor is reexecuted
 *  The parent cursor is closed
 *  The parent cursor is cancelled
 *  An error arises during fetch on one of its parent cursors (it is closed as part of the clean-up)

Restrictions on CURSOR Expressions

The following restrictions apply to `CURSOR` expressions:

*  If the enclosing statement is not a `SELECT` statement, then nested cursors can appear only as `REF` `CURSOR` arguments of a procedure.
 *  If the enclosing statement is a `SELECT` statement, then nested cursors can also appear in the outermost select list of the query specification or in the outermost select list of another nested cursor.
 *  Nested cursors cannot appear in views.
 *  You cannot perform `BIND` and `EXECUTE` operations on nested cursors.

Examples

The following example shows the use of a `CURSOR` expression in the select list of a query:

SELECT department_name, CURSOR(SELECT salary, commission_pct 
       FROM employees e
       WHERE e.department_id = d.department_id)
       FROM departments d
       ORDER BY department_name;

The next example shows the use of a `CURSOR` expression as a function argument. The example begins by creating a function in the sample `OE` schema that can accept the `REF` `CURSOR` argument. (The PL/SQL function body is shown in italics.)

CREATE FUNCTION f(cur SYS_REFCURSOR, mgr_hiredate DATE) 
       RETURN NUMBER IS
       emp_hiredate DATE;
       before number :=0;
       after number:=0;
    begin
      loop
        fetch cur into emp_hiredate;
        exit when cur%NOTFOUND;
        if emp_hiredate > mgr_hiredate then
          after:=after+1;
        else
          before:=before+1;
        end if;
      end loop;
      close cur;
      if before > after then
        return 1;
      else
        return 0;
      end if;
    end;
    /

The function accepts a cursor and a date. The function expects the cursor to be a query returning a set of dates. The following query uses the function to find those managers in the sample `employees` table, most of whose employees were hired before the manager.

SELECT e1.last_name FROM employees e1
       WHERE f(
       CURSOR(SELECT e2.hire_date FROM employees e2
       WHERE e1.employee_id = e2.manager_id),
       e1.hire_date) = 1
       ORDER BY last_name;
     
    LAST_NAME
    -------------------------
    Cambrault
    Higgins
    Hunold
    Kochhar
    Mourgos
    Zlotkey

*  [Previous][]
 *  [Next][]

# 调试经验——Oracle中使用游标表达式（CURSOR expression）及动态游标（SYS\_REFCURSOR） #

2018年10月19日 14:35:02 [预见未来to50][to50] 阅读数：45

### 问题描述： ###

在Oracle示例程序库的emp表中，想要查询入职日期早于其manager的员工，如何查询？

### 解决方法： ###

可自定义一个函数，用于判断员工的入职日期是否早于其manager的入职日期。

1.  `CREATE OR REPLACE FUNCTION F_TEMP (CUR SYS_REFCURSOR, MGR_HIREDATE DATE)`
2.  `RETURN NUMBER`
3.  `IS`
4.  `EMP_HIREDATE DATE;`
5.  `BEFORE NUMBER := 0;`
6.  `AFTER NUMBER := 0;`
7.  `BEGIN`
8.  `LOOP`
9.  `FETCH CUR INTO EMP_HIREDATE;`
10.  
11. `EXIT WHEN CUR%NOTFOUND;`
12.  
13. `IF EMP_HIREDATE < MGR_HIREDATE`
14. `THEN`
15. `AFTER := AFTER + 1;`
16. `ELSE`
17. `BEFORE := BEFORE + 1;`
18. `END IF;`
19. `END LOOP;`
20.  
21. `CLOSE CUR;`
22.  
23. `IF BEFORE > AFTER`
24. `THEN`
25. `RETURN 1;`
26. `ELSE`
27. `RETURN 0;`
28. `END IF;`
29. `END;`

然后，在子查询中调用这个定义好的函数（F\_TEMP）。

1.  `SELECT E1.EMPNO,`
2.  `E1.ENAME,`
3.  `E1.HIREDATE,`
4.  `E1.MGR,`
5.  `E3.ENAME "Mgr Name",`
6.  `E3.HIREDATE "Mgr's Hire Date",`
7.  `CASE WHEN E3.HIREDATE < E1.HIREDATE THEN 'No' ELSE 'Yes' END`
8.  `AS "Hired earlier than mgr?"`
9.  `FROM EMP E1, EMP E3`
10. `WHERE F_TEMP (CURSOR (SELECT E2.HIREDATE`
11. `FROM EMP E2`
12. `WHERE E1.MGR = E2.EMPNO),`
13. `E1.HIREDATE) = 1`
14. `AND E1.MGR = E3.EMPNO`
15. `ORDER BY E1.ENAME;`

查询结果：

![70][]

### 彩蛋： ###

以上示例仅用于说明CURSOR表达式。

其实，对于这个需求，不使用函数，而直接用以下子查询即可实现。

1.  `SELECT E1.EMPNO,`
2.  `E1.ENAME,`
3.  `E1.HIREDATE,`
4.  `E1.MGR,`
5.  `E2.ENAME "Mgr Name",`
6.  `E2.HIREDATE "Mgr's Hire Date",`
7.  `CASE WHEN E2.HIREDATE < E1.HIREDATE THEN 'No' ELSE 'Yes' END`
8.  `AS "Hired earlier than mgr?"`
9.  `FROM EMP E1, EMP E2`
10. `WHERE E1.HIREDATE < E2.HIREDATE AND E1.MGR = E2.EMPNO`

示例中的基础数据设置，请参考：

[https://blog.csdn.net/hpdlzu80100/article/details/83180714][https_blog.csdn.net_hpdlzu80100_article_details_83180714]

原文：[https://blog.csdn.net/hpdlzu80100/article/details/83182004][https_blog.csdn.net_hpdlzu80100_article_details_83182004]

参考文章：

[https://blog.csdn.net/mydreamneverstop/article/details/78604033][https_blog.csdn.net_mydreamneverstop_article_details_78604033]

[https_docs.oracle.com_en_database_oracle_oracle-database_18_sqlrf_CURSOR-Expressions.html_GUID-B28362BE-8831-4687-89CF-9F77DB3698D2]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CURSOR-Expressions.html#GUID-B28362BE-8831-4687-89CF-9F77DB3698D2
[Description of cursor_expression.eps follows]: /images/20220404/db0e102c76ec4b3fbf0e174e1fe58dc4.png
[Description of the illustration cursor_expression.eps]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/img_text/cursor_expression.html
[Previous]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Column-Expressions.html
[Next]: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Datetime-Expressions.html
[to50]: https://me.csdn.net/hpdlzu80100
[70]: /images/20220404/dad83b59f9da4acebe43f99687bb5950.png
[https_blog.csdn.net_hpdlzu80100_article_details_83180714]: https://blog.csdn.net/hpdlzu80100/article/details/83180714
[https_blog.csdn.net_hpdlzu80100_article_details_83182004]: https://blog.csdn.net/hpdlzu80100/article/details/83182004
[https_blog.csdn.net_mydreamneverstop_article_details_78604033]: https://blog.csdn.net/mydreamneverstop/article/details/78604033