|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
Expressions, 6 of 14
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:
Restrictions: The following restrictions apply to
SELECTstatement, nested cursors can appear only as
CURSORarguments of a procedure.
SELECTstatement, nested cursors can also appear in the outermost
SELECTlist of the query specification, or in the outermost
SELECTlist of another nested cursor.
EXECUTEoperations on nested cursors.
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;
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.