Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
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 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