Ref cursor Summary

Using ref cursor, you canProgramInter-pass result set (a program opens the cursor variable and processes data in another program ).
You can also use ref cursor to implement bulk SQL to improve SQL Performance.

There are two types of ref cursor: Strong ref cursor and weak ref cursor.

Strong ref cursor: Specify the retrun type. The cursor variable must be of the same type as the return type.
Declare
Type strongcurtyp is ref cursor return EMP % rowtype;
Emp_cv strongcurtyp;

Weak ref cursor: return type is not specified and can match any type of cursor variable.
Declare
Type weakcurtyp is ref cursor;
Weak_cv weakcurtyp;
Any_cv sys_refcursor; -- use sys_refcursor to match the any_cv variable with any weak ref cursor type.

Example of using strong ref cursor

Create or replace package emp_data
Type empcurtyp is ref cursor return EMP % rowtype; -- Define type as strong ref cursor
Procedure open_emp_cv (emp_cv in out empcurtyp, choice INT); -- select different cursor based on different choice
Procedure retrieve_data (choice INT); -- return the specified result set by calling procedure open_emp_cv.
End emp_data;

========================================================== =====

 

Create or replace package body emp_data

-- Procedure open_emp_cv -----------------------------

Procedure open_emp_cv (emp_cv in out empcurtyp, choice INT) is -- emp_cv as the incoming/outgoing cursor Parameter
Begin
If choice = 1 then
Open emp_cv
Select * from EMP where empno <7800;
Elsif choice = 2 then
Open emp_cv
Select * from EMP where Sal <1000;
Elsif choice = 3 then
Open emp_cv
Select * from EMP where ename like 'J % ';
End if;
End;

-- Procedure retrieve_data ----------------------------------

Procedure retrieve_data (choice INT) is
Return_cv empcurtyp; -- defines the cursor variable passed in open_emp_cv.
Return_row EMP % rowtype;
Invalid_choice exception;
Begin
Open_emp_cv (return_cv, choice); -- call procedure open_emp_cv

If choice = 1 then
Dbms_output.put_line ('ployees with empno less than 100 ');
Elsif choice = 2 then
Dbms_output.put_line ('ployees with salary less than 100 ');
Elsif choice = 3 then
Dbms_output.put_line ('ployees with name starts with ''j ''');
Else
Raise invalid_choice;
End if;

Loop
Fetch return_cv
Into return_row;
Exit when return_cv % notfound;

Dbms_output.put_line (return_row.empno | '--' | return_row.ename | '--' |
Return_row.sal );
End loop;

Exception
When invalid_choice then
Dbms_output.put_line ('The Choice shocould be in one of (, 3 )! ');
When others then
Dbms_output.put_line ('errors in procedure retrieve_data! ');
End;

End emp_data;

======================================

 

Run:
SQL> exec emp_data.retrieve_data (1 );
Employees with empno less than 7800
7369--Smith-800
7499--allen--1600
7521--ward-1250
7566--jones--2975
7654--Martin--1250
7698--blake--2850
7782--clark--2450
7788--scott--3000
PL/SQL procedure successfully completed

SQL> exec emp_data.retrieve_data (2 );
Employees with salary less than 1000
7369--Smith-800
7900--james--950
PL/SQL procedure successfully completed

SQL> exec emp_data.retrieve_data (3 );
Employees with name starts with 'J'
7566--jones--2975
7900--james--950
PL/SQL procedure successfully completed

SQL> exec emp_data.retrieve_data (34 );
The choice shoshould be in one of (1, 2, 3 )!
PL/SQL procedure successfully completed

Example of using weak ref cursor

-- Procedure open_cv ---------------------------------------

Create or replace procedure open_cv (choice in int,
Return_cv out sys_refcursor) is
-- The return_cv parameter is weak ref cursor, which is defined by sys_cursor.
Begin
If choice = 1 then
Open return_cv for 'select * From emp ';
Elsif choice = 2 then
Open return_cv for 'select * from Dept ';
End if;
End open_cv;

-- Procedure retrieve_data ------------------------------------

Create or replace procedure retrieve_data (choice in INT) is
emp_rec EMP % rowtype;
dept_rec dept % rowtype;
return_cv sys_refcursor;
invalid_choice exception;
begin
If choice = 1 then
dbms_output.put_line ('employee information');
open_cv (1, return_cv ); -- call procedure open_cv;
loop
fetch return_cv into emp_rec;
exit when return_cv % notfound;
dbms_output.put_line (emp_rec.empno | '-' | emp_rec.ename | '-' | emp_rec.sal);
end loop;
elsif choice = 2 then
dbms_output.put_line ('department information');
open_cv (2, return_cv );
loop
fetch return_cv into dept_rec;
exit when return_cv % notfound;
Reset (dept_rec.deptno | '-' | dept_rec.dname | '-' | dept_rec.loc);
end loop;
else
raise invalid_choice;
end if;

Exception
When invalid_choice then
Dbms_output.put_line ('The Choice shocould be one of 1 and 2! ');
When others then
Dbms_output.put_line ('errors in procedure retrieve_data ');
End retrieve_data;

-----------------------------------------------------------------

Run:
SQL> exec retrieve_data (1 );
Employee information
7369-Smith-800
7499-allen-1600
7521-ward-1250
7566-jones-2975
December 7654-Martin-1250
7698-blake-2850
......
PL/SQL procedure successfully completed

SQL> exec retrieve_data (2 );
Department Information
10-accounting-New York
20-research-dallas
30-sales-Chicago
40-operations-Boston
PL/SQL procedure successfully completed

Use ref cursor to implement the bulk Function

1. To speed up insert, update, and delete statements, enclose the SQL statement within a PL/SQL forall statement instead of a loop construct.
2. To speed up select statements, include the bulk collect into clause in the SELECT statement instead of using.

 

SQL> Create Table tab2 as select empno ID, ename name, Sal salary from EMP where 1 = 2;
Table created

-------------------------------------------------------

Create or replace procedure ref_bulk is
Type empcurtyp is ref cursor;
Type idlist is table of EMP. empno % type;
Type namelist is table of EMP. ename % type;
Type sallist is table of EMP. Sal % type;

Emp_cv empcurtyp;
IDS idlist;
Names namelist;
SALS sallist;

Row_cnt number;
Begin
Open emp_cv
Select empno, ename, Sal from EMP;
Fetch emp_cv bulk collect
Into IDs, names, SalS; -- bulk collect into instead of
Close emp_cv;

For I in IDs. First... IDs. Last Loop
Dbms_output.put_line ('Id = '| IDS (I) | 'name =' | names (I) |
'Salary = '| SALS (I ));
End loop;

Forall I in IDs. First... IDs. Last -- forall instead of for... loop
Insert into tab2 values (IDS (I), names (I), SALS (I ));
Commit;

Select count (*) into row_cnt from tab2;
Dbms_output.put_line ('-----------------------------------');
Dbms_output.put_line ('the row number of tab2 is '| row_cnt );
End ref_bulk;

------------------------------------------------------------

Run:

SQL> exec ref_bulk;

Id = 7369 name = Smith salary = 800
Id = 7499 name = Allen salary = 1600
Id = 7521 name = ward salary = 1250
Id = 7566 name = Jones salary = 2975
Id = 7654 name = Martin salary = 1250
Id = 7698 name = Blake salary = 2850
Id = 7782 name = Clark salary = 2450
Id = 7788 name = Scott salary = 3000
Id = 7839 name = King salary = 5000
Id = 7844 name = Turner salary = 1500
Id = 7876 name = Adams salary = 1100
Id = 7900 name = James salary = 950
Id = 7902 name = Ford salary = 3000
Id = 7934 name = Miller salary = 1300
-----------------------------------
The row number of tab2 is 14

PL/SQL procedure successfully completed