REF Cursor

                  Ref Cursor is the method to returns result sets to client applications (like C, VB, etc).
You cannot define ref cursors outside of a procedure or function in a package specification or body. Ref cursors can only be processed in the defining procedure or returned to a client application. Also, a ref cursor can be passed from subroutine to subroutine and a cursor cannot. To share a static cursor like that, you would have to define it globally in a package specification or body. Because using global variables is not a very good coding practice in general, Ref cursors can be used to share a cursor in PL/SQL without having global variables getting into the mix. 


Last, using static cursors—with static SQL (and not using a ref cursor) —is much more efficient than using ref cursors, and the use of ref cursors should be limited to

• Returning result sets to clients
• Sharing cursors across many subroutines (very similar to the above point, actually)
• Achieving your goal when there is no other efficient/effective means of doing so, such as when dynamic SQL must be used 

Example 1:

DECLARE
type refcursor is REF CURSOR;
stud_cursor refcursor;
stud_desc student%rowtype;
BEGIN
open stud_cursor for select * from student;
loop
    fetch stud_cursor into stud_desc;
    exit when stud_cursor%notfound;
    dbms_output.put_line(stud_desc.rollno ||'|'||stud_desc.name || '|' || stud_desc.total);
end loop;
END 

Example 2:

CREATE OR REPLACE PACKAGE CURSPKG AS 

TYPE T_CURSOR IS REF CURSOR;  
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,IO_CURSOR IN OUT T_CURSOR); 
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,DEPTCURSOR OUT T_CURSOR);
 
END  CURSPKG; / CREATE OR REPLACE PACKAGE BODY CURSPKG AS 
PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,IO_CURSOR IN OUT T_CURSOR) IS V_CURSOR T_CURSOR;
BEGIN     
     IF N_EMPNO <> 0 THEN 

      OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT           WHERE   EMP.DEPTNO = DEPT.DEPTNO AND EMP.EMPNO = N_EMPNO;

    ELSE 

    OPEN V_CURSOR FOR SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; 

      END IF;
    IO_CURSOR := V_CURSOR; 
END OPEN_ONE_CURSOR;


PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, DEPTCURSOROUT T_CURSOR) IS

V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN 

 OPEN V_CURSOR1 FOR SELECT * FROM EMP; OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END OPEN_TWO_CURSORS; END CURSPKG; 


                     Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privileges of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on. Here is an example:


create or replace package types
as
    type cursorType is ref cursor;
end;
/

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;
/
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
      open p_cursor for select ename, empno from emp order by ename;
end;
/