rem script: refcur_pkg.sql
rem   --  Understand how to use ref cursor in Java 
rem 
rem   ref: http://www.databasedesign-resource.com/ref-cursor.html
rem 


CREATE OR REPLACE PACKAGE GetRefCursors IS
-- ***********************************************************************
-- ** Author: Denis  
-- **
-- ***********************************************************************
-- ** General global cursor for all functions returning result sets.

TYPE csGetResultSet is REF CURSOR;

-- ***********************************************************************
-- ** Get all Employees for a given Department_Id 
-- ** In parameters:
-- **   Department_id 
-- ** Returns:
-- **   Ref Cursor for the given Department_Id.
-- ***********************************************************************

function sfGetEmpDept
( pDeptID in EMPLOYEES.DEPARTMENT_ID%type)
return csGetResultSet;
end GetRefCursors;
/

CREATE OR REPLACE package body GetRefCursors is
-- ***********************************************************************
-- ** Author: Denis  
-- **
-- ***********************************************************************

-- ***********************************************************************
-- ** Get all Employees for a given Department_Id 
-- ** In parameters:
-- **   Department_id 
-- ** Returns:
-- **   Ref Cursor for the given Department_Id.
-- ***********************************************************************

function sfGetEmpDept
( pDeptID in EMPLOYEES.DEPARTMENT_ID%type)
return csGetResultSet is

  csGetEmp csGetResultSet;

begin
  open csGetEmp for
    SELECT e.last_name, e.first_name, e.department_id
      FROM employees e 
     WHERE e.department_id = pDeptID 
     ORDER BY e.last_name; 

   return csGetEmp;
end sfGetEmpDept;

end GetRefCursors;
/