8,127
=======================================
EXAMPLE #1
----------------------
Expand|Select|Wrap|Line Numbers
- 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;
----------------------
Expand|Select|Wrap|Line Numbers
- 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;
----------------------
Expand|Select|Wrap|Line Numbers
- 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;
