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 thus…

declare

  begin
  for department in (
                      select department_id, department_name
                        from departments
                        order by department_name
                    )

  loop
    Dbms_Output.Put_Line ( department.department_name );
    for employee in (
                      select last_name
                        from employees
                        where department_id = department.department_id
                        order by last_name

                     )
    loop
      Dbms_Output.Put_Line ( employee.last_name );
    end loop;
  end loop;
end;
/

The following SELECT expresses the query requirement in a single SQL statement …

select
  department_name,
  cursor (
           select last_name
             from employees e
             where e.department_id = d.department_id
             order by last_name
          ) the_employees

  from departments d
  order by department_name;

…and runs at SQL*Plus pre-Oracle9i. (This implies of course that a corresponding cursor can be manipulated in the programming language used to implement SQL*Plus.) However, an attempt to associate such a SELECT statement with a PL/SQL cursor pre-Oracle9i fails to compile (with PLS-00103). Oracle9i introduces support for this thus…

declare
  cursor the_departments is
    select

      department_name,
      cursor (
               select last_name
                 from employees e
                 where e.department_id = d.department_id
                 order by last_name
              )
      from departments d
      where department_name in ( 'Executive', 'Marketing' )
      order by department_name;


  v_department_name departments.department_name%type;
  the_employees sys_refcursor;

  type employee_last_names_t is table of employees.last_name%type
                               index by binary_integer;
  v_employee_last_names employee_last_names_t;
begin
  open the_departments;
  loop
    fetch the_departments into v_department_name, the_employees;
    exit when the_departments%notfound;
    Dbms_Output.Put_Line ( v_department_name );

    fetch the_employees bulk collect into v_employee_last_names;
    for j in v_employee_last_names.first..v_employee_last_names.last
    loop
      Dbms_Output.Put_Line ( v_employee_last_names(j) );
    end loop;
  end loop;
  close the_departments;
end;
/

Though this is more lines of code, and arguably less easy to proof read, than the sequentially programmed implementation it has this advantage: there is only one SQL statement, and so it can be optimized more effectively than (what the SQL engine sees as) two unconnected SQL statements.

Note: Bulk fetch is used for the_employees cursor. This is not currently available for the_departments cursor because the appropriate collection type cannot be declared…

declare
  type department_r is record
    ( department_name departments.department_name%type,
      the_employees sys_refcursor );
begin 
  null;
end;
/

…causes “PLS-00989: Cursor Variable in record, object, or collection is not supported by this release”.