Live ORACLE

DECLARE

 TYPE emp_cur_typ IS REF CURSOR;

  emp_cur    EMP_CUR_TYP;

  dept_name  departments.department_name%TYPE;

  emp_name   employees.last_name%TYPE;

  CURSOR c1 IS

    SELECT department_name,

           -- second item in the result set is another result set,

           -- which is represented as a ref cursor and labelled "employees".

           CURSOR (SELECT e.last_name

                   FROM   employees e

                   WHERE  e.department_id = d.department_id) employees

    FROM   departments d

    WHERE  department_name LIKE 'A%';

BEGIN

 OPEN c1;



 LOOP

   FETCH c1 INTO dept_name,emp_cur;

 

   EXIT WHEN c1%NOTFOUND;

 

   dbms_output.Put_line('Department: '

                        ||dept_name);

 

   -- for each row in the result set, the result set from a subquery is processed

   -- the set could be passed to a procedure for processing rather than the loop

   LOOP

     FETCH emp_cur INTO emp_name;

   

     EXIT WHEN emp_cur%NOTFOUND;

   

     dbms_output.Put_line('-- Employee: '

                          ||emp_name);

   END LOOP;

 END LOOP;



 CLOSE c1;

END;

/

Department: Administration
-- Employee: Whalen
Department: Accounting
-- Employee: Higgins
-- Employee: Gietz

PL/SQL procedure successfully completed.

Using a Cursor Expression