Cursor Expressions in PL/SQL

Overview

Starting with Oracle9 there is a new feature in the SQL language: the Cursor Expression. A cursor expression, denoted by the CURSOR operator, returns a nested cursor from within a query. Each row in the result set of this nested cursor can contain the usual range of values allowed in a SQL query; it can also contain other cursors as produced by subqueries.

Manipulating Cursor Expressions in PL/SQL

Consider the task: list the department names, and for each department list the names of the employees in that department. It can be simply implemented by a classical sequential programming approach.

Solution 1 - Sequential Approach

declare
  begin
  for department in (
                      select deptno, dname
                        from dept
                       order by dname
                    )

  loop
    Dbms_Output.Put_Line ('Departement:' || department.dname );
    for employee in (
                      select ename
                        from emp
                       where deptno = department.deptno
                       order by ename
                    )
    loop
      Dbms_Output.Put_Line ( employee.ename );
    end loop;
  end loop;
end;
/

Departement:ACCOUNTING
CLARK
KING
MILLER
Departement:OPERATIONS
Departement:RESEARCH
ADAMS
FORD
JONES
SCOTT
SMITH
Departement:SALES
ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD

This sequentially programmed implementation is easy to read but there are two unconnected SQL statements which must be tuned separately.

Solution 2 - With Cursor Expression

declare

  -- Declare Outer Cursor
  cursor cur_dept is

    -- Declare Inner Cursor
    select dname,
           cursor (
               select ename
                 from emp e
                where e.deptno = d.deptno
                order by ename
           )
      from dept d
     order by dname;

  -- Declare Variables to hold Values from Outer Cursor
  l_dname    dept.dname%type;
  ref_cur    sys_refcursor;    -- Hold Resultset from Inner Cursor

  -- Declare PL/SQL Table to hold Values from Inner Cursor
  type t_ename is table of emp.ename%type index by binary_integer;
  l_ename t_ename;

begin
  open cur_dept;
  loop

    -- Fetch Values from Outer Cursor
    fetch cur_dept into l_dname, ref_cur;
    exit when cur_dept%notfound;
    Dbms_Output.Put_Line ('Departement: ' || l_dname);

    -- Fetch Values from Inner Cursor
    fetch ref_cur bulk collect into l_ename;

    -- Print Employees for this Departement
    if (l_ename.last > 0)
    then
        for j in l_ename.first..l_ename.last
        loop
          Dbms_Output.Put_Line ( l_ename(j) );
        end loop;
    end if;
  end loop;
  close cur_dept;
end;
/

Departement: ACCOUNTING
CLARK
KING
MILLER
Departement: OPERATIONS
Departement: RESEARCH
ADAMS
FORD
JONES
SCOTT
SMITH
Departement: SALES
ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD

There is now only one SQL statement, and so it can be optimized more effectively than (what the SQL engine sees as) two unconnected SQL statements.

As you can see, the CURSOR EXPRESSION select statement returns two result sets. The Outer returns the Departements and the inner (the CURSOR EXPRESSION itself) returns the Employees for each of the Departements.

select dname,
           cursor (
               select ename
                 from emp e
                where e.deptno = d.deptno
                order by ename
           )
      from dept d
     order by dname;

DNAME
--------------------------------
ACCOUNTING

    ENAME
    ----------
    CLARK
    KING
    MILLER

DNAME
--------------------------------
RESEARCH

    ENAME
    ----------
    ADAMS
    FORD
    JONES
    SCOTT
    SMITH

DNAME
--------------------------------
SALES

    ENAME
    ----------
    ALLEN
    BLAKE
    JAMES
    MARTIN
    TURNER
    WARD

Cursor Expression as an actual parameter to a PL/SQL function

A cursor variable (i.e. a variable of type ref cursor) points to an actual cursor, and may be used as a formal parameter to a PL/SQL procedure or function. A cursor expression defines an actual cursor, and as we have seen is a construct that’s legal in a SQL statement. So we would expect that it would be possible to invoke a PL/SQL procedure or function which has a formal parameter of type ref cursor with a cursor expression as its actual parameter.

The next example shows the use of a CURSOR expression as a function argument. The example begins by creating a function that can accept the REF CURSOR argument.

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 EMP table, most of whose employees were hired before the manager.

CREATE OR REPLACE FUNCTION compare_date (ref_cur IN SYS_REFCURSOR,
                                         mgr_hiredate IN DATE)
RETURN NUMBER
IS
   emp_hiredate    date;
   before          number :=0;
   after           number :=0;
BEGIN
  LOOP
    FETCH ref_cur INTO emp_hiredate;
    EXIT WHEN ref_cur%NOTFOUND;
    IF (emp_hiredate > mgr_hiredate) THEN
       after := after + 1;
    ELSE
       before := before +1 ;
    END IF;
  END LOOP;
  CLOSE ref_cur;

  IF (before > after) THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;
/

Now we can use this Function in the WHERE Clause

SELECT e1.ename
  FROM emp e1
 WHERE compare_date (CURSOR (SELECT e2.hiredate    -- First Argument
                              FROM emp e2
                              WHERE e1.empno = e2.mgr),
                     e1.hiredate) = 1;             -- Second Argument

ENAME
----------
KING
FORD