CURSOR Expressions

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::=

Text description of 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:

• 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

• If the enclosing statement is not a SELECT statement, nested cursors can appear only as REF CURSOR 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:

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