Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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 expressions3.gif follows
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:

Restrictions on CURSOR Expressions
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