REF Cursor & Normal Cursor

What is difference between Cursor and Ref Cursor or Cursor variable in PL/SQL?

1) Ref cursors are used for dynamic query building and explicit cursors are used for static queries.

2) Ref cursors can be passed as parameters in procedures/functions where as explicit
cursors cannot be passed.

3) Explicit cursors should be defined at design time, whereas ref cursors can be defined at run time.

4. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened
based on logic.  Cursor C will always be select * from dual.   The ref cursor can be anything.

5. Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure /function).

6. Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

7.  Another difference is a ref cursor can be returned to a client.  a plsql "cursor cursor"
cannot be returned to a client.


Ex:-

DECLARE
   TYPErcIS REFCURSOR;
   CURSORcIS SELECT*FROMdual;
   l_cursor rc;
BEGIN
      IF  (to_char(SYSDATE,''dd'')=30)THEN
OPENl_cursor FORSELECT* FROMemp;
ELSIF(to_char(SYSDATE,''dd'')=29)THEN
OPENl_cursor FORSELECT* FROMdept;
      ELSE
OPENl_cursorFOR SELECT*FROMdual;
      ENDIF;
      OPENc;
      CLOSEc;
END;