Friday, December 05, 2008

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.

No comments: