A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).
Let us start with a small sub-program as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
en emp.ename%type;
begin
open c_emp for select ename from emp;
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
close c_emp;
end;
Let me explain step by step. The following is the first statement you need to understand:
type r_cursor is REF CURSOR;
The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR. We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows:
c_emp r_cursor;
Every cursor variable must be opened with an associated SELECT statement as follows:
open c_emp for select ename from emp;
To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows:
loop
fetch c_emp into en;
exit when c_emp%notfound;
dbms_output.put_line(en);
end loop;
I finally closed the cursor using the following statement:
close c_emp;
%ROWTYPE with REF CURSOR
In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
end;
In the above example, the only crucial declaration is the following:
er emp%rowtype;
The above declares a variable named "er," which can hold an entire row from the "emp" table. To retrieve the values (of each column) from that variable, we use the dot notation as follows:
dbms_output.put_line(er.ename || ' - ' || er.sal);
