PL/SQL Ref Cursor

 PL/SQL 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:-  

Sr.No	cursors Name	Description
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.

 Examples :-  

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

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

 Strong Ref Cursor:-  

  Type of Ref Cursor with return datatype is called Strong Ref Cursor.

 Examples :-  

 declare
    v_emp emp%ROWTYPE;
  TYPE strong_type IS REF CURSOR RETURN emp%ROWTYPE; 
  c2 STRONG_TYPE;
 begin
    open c2 for select * from emp;
    loop
    fetch c2 into v_emp;
        exit when c2%notfound;
        dbms_output.put_line( v_emp.empno||' '||v_emp.ename);
    end loop; ; 
    close c2;
 end;