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, nested cursors can appear only as REFCURSOR arguments of a procedure.

If the enclosing statement is a SELECT statement, 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:

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