Demonstrate the Usage of Cursor Variable in PL/SQL

Reference: Cursor Variables

denis@TEST10G> @cursor_variables.sql
denis@TEST10G> rem Fectching Data
denis@TEST10G> rem
denis@TEST10G> rem Purpose: demonstrate the usage of cursor variable in PL/SQL
denis@TEST10G> rem
denis@TEST10G> rem   A cursor variable is a pointer to a cursor. Because cursor
denis@TEST10G> rem   variables are pointers, they can be passed and returned as
denis@TEST10G> rem   parameters to procedures and functions. A cursor variable
denis@TEST10G> rem   can also refer to different cursors in its life time
denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> drop table emp_tab purge;

Table dropped.

denis@TEST10G> 
denis@TEST10G> create table emp_tab
  2  as
  3  select
  4    rownum empno,
  5    object_name ename,
  6    mod(rownum, 10) + 1 deptno ,
  7    rownum * 100 sal
  8  from all_objects
  9  where rownum < 100;

Table created.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> drop table dept_tab purge;

Table dropped.

denis@TEST10G> create table dept_tab
  2  as
  3  select
  4    rownum deptno,
  5    'd_' || rownum  deptname
  6  from all_objects
  7  where rownum <=10;

Table created.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> -- the following package defines a PL/SQL cursor variable type Emp_Val_cv_type,
denis@TEST10G> -- and two procedures
denis@TEST10G> -- The first procedure, Open_emp_cv, opens the cursor variable using a bind
denis@TEST10G> -- variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches
denis@TEST10G> -- rows from the Emp_tab table using the cursor variable
denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> create or replace package emp_data as
  2    type emp_val_cv_type is ref cursor return emp_tab%rowtype;
  3    procedure open_emp_cv (emp_cv  in out emp_val_cv_type,
  4           dept_number in integer);
  5    procedure fetch_emp_data (emp_cv in emp_val_cv_type,
  6       emp_row out emp_tab%rowtype);
  7  end emp_data;
  8  /

Package created.

denis@TEST10G> 
denis@TEST10G> create or replace package body emp_data as
  2    procedure open_emp_cv (emp_cv  in out emp_val_cv_type,
  3           dept_number in integer) is
  4    begin
  5    open emp_cv for select * from emp_tab where deptno = dept_number;
  6    end open_emp_cv;
  7    procedure fetch_emp_data (emp_cv in emp_val_cv_type,
  8       emp_row out emp_tab%rowtype) is
  9    begin
 10    fetch emp_cv into emp_row;
 11    end fetch_emp_data;
 12  end emp_data;
 13  /

Package body created.

denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> -- The following code shows how to call emp_data package
denis@TEST10G> -- procedures from a PL/SQL bock:
denis@TEST10G> 
denis@TEST10G> 
denis@TEST10G> declare
  2  -- delcare a cursor variable
  3   emp_curs emp_data.emp_val_cv_type;
  4   dept_number dept_tab.deptno%type;
  5   emp_row emp_tab%rowtype;
  6  begin
  7   dept_number := 6;
  8  -- open the cursor using a variable
  9   emp_data.open_emp_cv(emp_curs, dept_number);
 10  -- fetch the data and display it
 11   loop
 12      emp_data.fetch_emp_data(emp_curs, emp_row);
 13      exit when emp_curs%notfound;
 14      dbms_output.put_line(rpad(emp_row.ename, 20) || ' ' || lpad(emp_row.sal, 20));
 15   end loop;
 16  end;
 17  /
C_COBJ#                               500
I_CON1                               1500
I_UNDO2                              2500
C_USER#                              3500
I_COL3                               4500
I_PROXY_ROLE_DATA$_2                 5500
I_SUPEROBJ1                          6500
IDL_SB4$                             7500
TRIGGERJAVAM$                        8500
PROPS$                               9500

PL/SQL procedure successfully completed.