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.
Let us start with a small sub-program as follows:
%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;
----------------------------------------------------------------------------------------------------
As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time. Before associating a new SELECT statement, we need to close the CURSOR. Let us have an example as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
-----------------------------------------------------------------------------------------------------------
Let us start with a small sub-program as follows:
%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;
----------------------------------------------------------------------------------------------------
As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time. Before associating a new SELECT statement, we need to close the CURSOR. Let us have an example as follows:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
close c_emp;
end;
-----------------------------------------------------------------------------------------------------------
0 comments:
Post a Comment