CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL
CURSOR and can be passed as a
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)
If the enclosing statement is not a
SELECT statement, then nested cursors can appear only as
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
EXECUTE operations on nested cursors.
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
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