-->

02 October 2020

PL/SQL Ref Cursor

  Asp.Net CS By Example       02 October 2020
 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;     

logoblog

Thanks for reading PL/SQL Ref Cursor

Previous
« Prev Post

No comments:

Post a Comment

Please do not enter any spam link in the comment box.