PLSQL Ref Cursor

  Ref Cursor

Ref Cursor :- Ref Cursor is a Type of cursor type. This is dynamic cursor ,we can use single ref cursor variable for multiple queries in a same PLSQL block. Ref cursor is used to processed records of multiple queries

Types of ref cursors:-
1) weak ref cursor :-this hasn't the return datatype so it can return any datatype
2) strong ref cursor:- This has the return datatype

Weak Ref Cursor:- Variable of ref cursor type with no return datatype is called Weak Ref Cursor

Example:-
declare
type c1 is ref cursor; --No return type
c2 c1;
v emp%rowtype;
begin
open c2 for select * from emp;
loop
fetch c2 into v;
exit when c2%notfound;
dbms_output.put_line(v.empno||' '||v.ename);
end loop;
close c2;
end;
/



Note:- For loop cursor is not working in ref cursor

More examples for weak ref cursor:-
declare
type c1 is ref cursor;
c2 c1;
v emp%rowtype;
v1 dept%rowtype;
begin
open c2 for select * from emp;
loop
fetch c2 into v;
exit when c2%notfound;
dbms_output.put_line(v.empno||' '||v.ename);
end loop;
close c2;
dbms_output.put_line('---------------------');
open c2 for select * from dept;
loop
fetch c2 into v1;
exit when c2%notfound;
dbms_output.put_line(v1.deptno||' '||v1.dname);
end loop;
close c2;
end;
/


declare
type c1 is ref cursor;
c2 c1;
v emp%rowtype;
v1 dept%rowtype;
qno number:=&qno;
begin
if qno=1 then
open c2 for select * from emp;
loop
fetch c2 into v;
exit when c2%notfound;
dbms_output.put_line(v.empno||' '||v.ename);
end loop;
close c2;
elsif qno=2 then
open c2 for select * from dept;
loop
fetch c2 into v1;
exit when c2%notfound;
dbms_output.put_line(v1.deptno||' '||v1.dname);
end loop;
close c2;
end if;
end;
/


Declare
type rc is ref cursor;
l_cursor rc;
v emp%rowtype;
v1 dept%rowtype;
x varchar2(20);
begin
if (to_char(sysdate,'dd') = 15 ) then
open l_cursor for select * from emp;
loop
fetch l_cursor into v;
exit when l_cursor%notfound;
dbms_output.put_line(v.empno);
end loop;
close l_cursor;
elsif ( to_char(sysdate,'dd') = 17 ) then
open l_cursor for select * from dept;
loop
fetch l_cursor into v1;
exit when l_cursor%notfound;
dbms_output.put_line(v1.deptno);
end loop;
close l_cursor;
else
open l_cursor for select dummy from dual;
loop
fetch l_cursor into x;
exit when l_cursor%notfound;
dbms_output.put_line(x);
end loop;
close l_cursor;
end if;
end;
/

Strong Ref Cursor:- Type of Ref Cursor with return datatype is called Strong Ref Cursor
Example:-

DECLARE
  erec emp%ROWTYPE;
  TYPE strong_type IS REF CURSOR RETURN emp%ROWTYPE;
  c STRONG_TYPE;
BEGIN
  OPEN c FOR SELECT * FROM emp;
  LOOP
    FETCH c INTO erec;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line(erec.ename);
  END LOOP;
  CLOSE c;
END;
/