Cursor Expressions in PL/SQL

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 sub queries.

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