REF CURSORS in Oracle

REF CURSORS in Oracle

=================================================================
==================================================================

create or replace procedure sp_basic_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;

begin

open emp_ref for select * from emp;
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;

==================================================================
==================================================================

create or replace procedure sp_reuse_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;
dept_rec dept%rowtype;
begin
dbms_output.put_line(' Employee data ');
open emp_ref for select * from emp;
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno||'   '||emp_rec.ename);
exit when emp_ref%notfound;
end loop;
close emp_ref;
dbms_output.put_line(' Dept data ');
open emp_ref for select * from dept;
loop
fetch emp_ref into dept_rec;
dbms_output.put_line(dept_rec.deptno||'   '||dept_rec.dname||'   '||dept_rec.loc);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;
/

==================================================================
==================================================================


create or replace procedure sp_Sample_output_refcur_demo
(v_dno in Number,v_emp_ref OUT SYS_REFCURSOR)
is
emp_rec emp%rowtype;
begin
dbms_output.put_line(' Employee data ');
open v_emp_ref for select * from emp where deptno=v_dno;
end;
/

==================================================================


create or replace procedure sp_Calling_refcur_demo
is
type ty_emp_ref is ref cursor;
emp_ref ty_emp_ref;
emp_rec emp%rowtype;
begin
dbms_output.put_line(' Employee data ');
sp_Sample_output_refcur_demo(20,emp_ref);
loop
fetch emp_ref into emp_rec;
dbms_output.put_line(emp_rec.empno||'   '||emp_rec.ename);
exit when emp_ref%notfound;
end loop;
close emp_ref;
end;
/


==================================================================
==================================================================



create or replace procedure sp_dno_input_refcur_demo(dept_ref in SYS_REFCURSOR)
is
v_deptno dept.deptno%type;
emp_ref sys_refcursor;
emp_rec emp%rowtype;

begin
loop
fetch dept_ref into v_deptno;
exit when dept_ref%notfound;
dbms_output.put_line(' Employee Details of ' ||v_deptno);
dbms_output.put_line(' ---------------------------- ');
open emp_ref for select * from emp where deptno=v_deptno;
loop
fetch emp_ref into emp_rec;
exit when emp_ref%notfound;
dbms_output.put_line(emp_rec.ename||'    '||emp_rec.deptno);
end loop;
end loop;
close emp_ref;
close dept_ref;
end;
/


create or replace procedure sp_dno_call_ip_refcur_demo
is
v_ref_cur sys_refcursor;
begin
open v_ref_cur for select distinct deptno from emp;
sp_dno_input_refcur_demo(v_ref_cur);
end;

==================================================================
==================================================================