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

 

查看Oracle官方文档：

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

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CURSOR-Expressions.html#GUID-B28362BE-8831-4687-89CF-9F77DB3698D2

 

 

 

 

 

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

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

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/hpdlzu80100/article/details/83182004

问题描述：

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

查询结果：

彩蛋：

以上示例仅用于说明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/83182004

参考文章：

https://blog.csdn.net/mydreamneverstop/article/details/78604033