 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,508
# 1
May 29 '07
| |
SAMPLE CODE TO SHOW USE OF REFCURSOR
=======================================
EXAMPLE #1
---------------------- -
declare
-
--declare the fer cursor.
-
type my_ref_cur_typ is ref cursor;
-
--declare a variable of rec cursor type.
-
my_ref_cur my_ref_cur_typ;
-
my_rec dept%rowtype;
-
my_rec1 emp%rowtype;
-
-
begin
-
--open the cursor variable to select from a table.
-
open my_ref_cur for select * from dept;
-
loop
-
fetch my_ref_cur into my_rec;
-
dbms_output.put_line(my_rec.deptno||' '||
-
my_rec.dname||' '||my_rec.loc);
-
-
exit when my_ref_cur%notfound;
-
end loop;
-
--close the cursor.
-
close my_ref_cur;
-
-
--open the same cursor to select from another set of ecords.
-
open my_ref_cur for select empno,ename,job from emp;
-
loop
-
fetch my_ref_cur into my_rec1.empno,my_rec1.ename,my_rec1.job;
-
dbms_output.put_line(my_rec1.empno||' '||
-
my_rec1.ename||' '||my_rec1.job);
-
exit when my_ref_cur%notfound;
-
end loop;
-
close my_ref_cur;
-
-
end;
-
EXAMPLE #2
---------------------- -
DECLARE
-
TYPE R1_CUR IS REF CURSOR;
-
MYVAR1 R1_CUR;
-
ename1 varchar(10);
-
sall number;
-
no number;
-
begin
-
-
no:=&no;
-
-
if no=10 then
-
open myvar1 for select sal from emp1 where empno=7900;
-
fetch myvar1 into sall;
-
dbms_output.put_line(sall);
-
close myvar1;
-
else
-
open myvar1 for select ename from emp1 where empno=7900;
-
-
fetch myvar1 into ename1;
-
-
dbms_output.put_line(ename1);
-
-
close myvar1;
-
end if;
-
end;
-
-
EXAMPLE #3
---------------------- -
declare
-
type ordertype is record(
-
orderno varchar2(5),
-
odate date,
-
vencode varchar2(5),
-
ostatus char(1),
-
del_date date);
-
-
type ordercur is ref cursor return order_master%rowtype;
-
order_cv ordercur;
-
order_rec ordertype;
-
-
begin
-
-
open order_cv for select
-
orderno,odate,vencode,ostatus,del_date from order_master
-
where vencode='v001';
-
loop
-
fetch order_cv into order_rec;
-
exit when order_cv%notfound;
-
dbms_output.put_line('The values are '||order_rec.orderno|| order_rec.odate ||' '||order_rec.vencode||' '||order_rec.ostatus||' '||order_rec.del_date);
-
-
end loop;
-
close order_cv;
-
end;
-
Also check Oracle REF CURSOR - 2
Last edited by debasisdas; Feb 12 '08 at 10:33 AM.
Reason: added stuff
|
