This topic covers meaning and step by step use of Ref Cursor, %RowType attribute and Record types with examples. HR schema is used throughout this tutorial for examples and demos.
Ref Cursor
Ref Cursor is a data type used to declare a cursor variable. Cursor variables are used mainly to create a cursor data type and pass it as parameter between sub-programs.
Example 1 : Ref Cursor using RECORD
/** Create a package **/
create or replace package refcursor_demo is
/** Declare a cursor variable refcur of REF CURSOR type.
Return key-word binds REFCUR with EREC_TYPE record**/
type refcur is ref cursor return erec_type;
/** create a RECORD type **/
type erec_type is record (empno employees.employee_id%type,
ename employees.first_name%type,
salary employees.salary%type,
dept departments.department_name%type);
/** declare a variable of erec_type RECORD type **/
emprecord erec_type;
/** Use the cursor variable as a parameter **/
procedure get_emp_details (res in out refcur, deptid in number);
end;
create or replace package body refcursor_demo is
/** This procedure takes REF CURSOR as in parameter
which is declared and opened in the calling procedure
get_emp_details ****/
procedure print_emp_details(res in refcur) is
begin
loop
/** RECORD type variable emprecord is declared in package specification**/
fetch res into emprecord;
exit when res%notfound;
dbms_output.put_line(emprecord.empno||' '
||emprecord.dept);
end loop;
end;
/** Procedure GET_EMP_DETAILS takes REFCUR as IN parameter,
opens the cursor using a query and pass it to print_emp_details as in parameter ***/
procedure get_emp_details(res in out refcur,
deptid in number) is
begin
open res for
select a.employee_id, a.first_name,
a.salary, b.department_name
from employees a, departments b
where b.department_id = deptid
and a.department_id = b.department_id;
print_emp_details(res);
end;
end;
/** Use the package to print values **/
declare
my_refcur refcursor_demo.refcur;
begin
refcursor_demo.get_emp_details(my_refcur, 20);
end;
In the above example get_emp_details procedure is used to associate a query to the ref cursor passed as IN parameter. This procedure calles print_emp_details and the ref cursor is passed as a parameter. As the ref cursor is declared in package specification, it can be called from out side the package also.
In this example I have used a RECORD type to receive values from cursor. This can be done using %TYPE attribute in case column selected in the associated query is one and %ROWTYPE in case column selected in the associated query is more than one.
Difference between %ROWTYPE and RECORD type :
Main difference between %ROWTYPE and RECORD is %ROWTYPE is a table, view or cursor attribute. It's data structure is same as the table, view or cursor which it refers. RECORD type is a user defined object created in the data base. Data structure of a RECORD object is decided at the time of creating the object.
declare
emprecord employees%rowtype;
type refcur is ref cursor;
my_refcur refcur;
procedure print_emp_details(res in refcur) is
begin
loop
fetch res into emprecord;
exit when res%notfound;
dbms_output.put_line(emprecord.Employee_id||' '||
emprecord.Last_Name||' '||
emprecord.Department_id);
end loop;
end;
procedure get_emp_details(res in out refcur,
deptid in number) is
begin
open res for select * from employees
where department_id = deptid;
print_emp_details(res);
end;
begin
get_emp_details(my_refcur, 30);
end;
/
