Simple Example of Ref Cursor

CREATE OR REPLACE PACKAGE test_ref
IS
   TYPE ref_cur IS REF CURSOR;

   PROCEDURE print_project;

END;





CREATE OR REPLACE PACKAGE BODY test_ref
IS
   PROCEDURE print_project
   IS

      c_test         ref_cur;
      l_project_id   NUMBER;
      l_pm           VARCHAR2 (100);
      c_pm           ref_cur;
      l_name         VARCHAR2 (200);
   BEGIN
      OPEN c_test -- We can open this ref cursor dynamically using any query here
       FOR
          SELECT project_id, NAME
            FROM pa_projects_all
           WHERE TRUNC(SYSDATE) BETWEEN TRUNC(start_date) AND TRUNC(NVL(completion_date,SYSDATE))
           AND   project_id = 530;

      FETCH c_test
       INTO l_project_id, l_name;                           

      DBMS_OUTPUT.put_line (' Project ID  : ' || l_project_id);
      DBMS_OUTPUT.put_line (' Project Name: ' || l_name);

      OPEN c_pm -- We can open ref cursor at runtime dynamically using any query
       FOR
          SELECT papf.full_name
            FROM pa_project_players ppp,
                 pa_project_role_types pprt,
                 per_all_people_f papf
           WHERE project_id = l_project_id
             AND papf.person_id = ppp.person_id
             AND ppp.project_role_type = pprt.project_role_type
             AND TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(NVL(papf.effective_end_date,SYSDATE))
             AND TRUNC(SYSDATE) BETWEEN TRUNC(ppp.start_date_active) AND TRUNC(NVL(ppp.end_date_active,SYSDATE))
             AND TRUNC(SYSDATE) BETWEEN TRUNC(ppp.start_date_active) AND TRUNC(NVL(ppp.end_date_active,SYSDATE))
             AND pprt.meaning LIKE 'Project Manager';

      FETCH c_pm
       INTO l_pm;

      DBMS_OUTPUT.put_line (' Project Manager : ' || l_pm);

      CLOSE c_pm;

      CLOSE c_test;
   END print_project;
END test_ref;