14.7.1 Using Cursor Expressions

The following procedure demonstrates the use of nested CURSOR expressions. The top-level query fetches just two pieces of data: the city location and a nested cursor containing departments in that city. This nested cursor, in turn, fetches a nested cursor with a CURSOR expression—in this case, one containing the names of all the employees in each department.

I could have performed this same retrieval with separate explicit cursors, opened and processed in a nested fashion. The CURSOR expression gives us the option of a different approach, and one that can be much more concise and efficient, given that all the processing takes place in the SQL statement executor and thus reduces context switching.

CREATE OR REPLACE PROCEDURE emp_report (p_locid NUMBER)

IS

TYPE refcursor IS REF CURSOR;

-- The query returns only 2 columns, but the second column is

-- a cursor that lets us traverse a set of related information.

CURSOR all_in_one_cur is

SELECT l.city,

CURSOR (SELECT d.department_name,

CURSOR(SELECT e.last_name

FROM employees e

WHERE e.department_id =

d.department_id)

AS ename

FROM departments d

WHERE l.location_id = d.location_id) AS dname

FROM locations l

WHERE l.location_id = p_locid;

departments_cur refcursor;

employees_cur refcursor;

v_city locations.city%TYPE;

v_dname departments.department_name%TYPE;

v_ename employees.last_name%TYPE;

BEGIN

OPEN all_in_one_cur;

LOOP

FETCH all_in_one_cur INTO v_city, departments_cur;

EXIT WHEN all_in_one_cur%NOTFOUND;

-- Now I can loop through departments and I do NOT need to

-- explicitly open that cursor. Oracle did it for me.

LOOP

FETCH departments_cur INTO v_dname, employees_cur;

EXIT WHEN departments_cur%NOTFOUND;

-- Now I can loop through employees for that department.

-- Again, I do not need to open the cursor explicitly.

LOOP

FETCH employees_cur INTO v_ename;

EXIT WHEN employees_cur%NOTFOUND;

DBMS_OUTPUT.put_line (

v_city

|| ' '

|| v_dname

|| ' '

|| v_ename

);

END LOOP;

END LOOP;

END LOOP;

CLOSE all_in_one_cur;

END;

14.7.2 Restrictions on Cursor Expressions

There are a number of restrictions on the use of cursor expressions:

• You cannot use a cursor expression with animplicit cursor because no mechanism is available to fetch the nested cursor INTO a PL/SQL data structure.

• Cursor expressions can appear only in the outermostSELECT list of the query specification.

• You can place cursor expressions only in a SELECT statement that is not nested in any other query expression, except when it is defined as a subquery of the cursor expression itself.

• A cursor expression can be used as an argument to table functions, called within the FROM clause of a SELECT statement.

• Cursor expressions cannot be used when declaring a view.

• You cannot perform BIND and EXECUTE operations on cursor expressions when using the CURSOR expression in dynamic SQL.