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.
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 asREF
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
andEXECUTE
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