Ref Cursor

    This is unconstrained cursor which will return different types depends upon the user input.
    Ref cursors cannot be closed implicitly.
    Ref cursor with return type is called strong cursor.
    Ref cursor without return type is called weak cursor.
    You can declare ref cursor type in package spec as well as body.
    You can declare ref cursor types in local subprograms or anonymous blocks.


CREATE OR REPLACE PROCEDURE ref_c_sp(dep_det_c IN OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN dep_det_c
  FOR
  SELECT * FROM dep_details d;
END;
/


DECLARE
   dep_det_c  SYS_REFCURSOR;
   tab_data   dep_details%ROWTYPE;
BEGIN
   ref_c_sp(dep_det_c);
 
   LOOP
      FETCH dep_det_c INTO tab_data;
      EXIT WHEN dep_det_c%NOTFOUND;
      dbms_output.put_line('Department Name : '|| tab_data.d_name);
   END LOOP;
   CLOSE dep_det_c;
END;


/*sample output*/

Department Name : Admin
Department Name : HR
Department Name : Sales
Department Name : Marketing


DROP PROCEDURE ref_c_sp;