Live ORACLE

DECLARE

 TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;

  emp  EMPCURTYP;

  -- after result set is built, process all the rows inside a single procedure

  -- rather than calling a procedure for each row

  PROCEDURE Process_emp_cv

       (emp_cv  IN EMPCURTYP)

  IS

    person  employees%ROWTYPE;

  BEGIN

    dbms_output.Put_line('-----');

  

    dbms_output.Put_line('Here are the names from the result set:');

  

    LOOP

      FETCH emp_cv INTO person;

    

      EXIT WHEN emp_cv%NOTFOUND;

    

      dbms_output.Put_line('Name = '

                           ||person.first_name

                           ||' '

                           ||person.last_name);

    END LOOP;

  END;

BEGIN

 -- First find 10 arbitrary employees.

 OPEN emp FOR

   SELECT *

   FROM   employees

   WHERE  ROWNUM < 11;



 Process_emp_cv(emp);



 CLOSE emp;



 -- find employees matching a condition.

 OPEN emp FOR

   SELECT *

   FROM   employees

   WHERE  last_name LIKE 'R%';



 Process_emp_cv(emp);



 CLOSE emp;

END;

/

-----
Here are the names from the result set:
Name = Donald OConnell
Name = Douglas Grant
Name = Jennifer Whalen
Name = Michael Hartstein
Name = Pat Fay
Name = Susan Mavris
Name = Hermann Baer
Name = Shelley Higgins
Name = William Gietz
Name = Steven King
-----
Here are the names from the result set:
Name = Trenna Rajs
Name = Den Raphaely
Name = Michael Rogers
Name = John Russell

PL/SQL procedure successfully completed.

Passing a REF CURSOR as a Parameter