Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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.
cursor_expression::=
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:
SELECT
statement, nested cursors can appear only as REF
CURSOR
arguments of a procedure.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.BIND
and EXECUTE
operations 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 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; LAST_NAME ------------------------- De Haan Mourgos Cambrault Zlotkey Higgens