Cursor Variables - Recap

This PL/SQL language feature was available pre-Oracle9i. A cursor variable is a pointer (declared as type ref cursor) to an actual cursor. Code which is written to manipulate a cursor variable can be reused for successive assignments to different actual cursors. The understanding of the PL/SQL features introduced in Oracle9i for cursor expressions and table functions depends on understanding cursor variables.

Consider this procedure…

create or replace procedure Fetch_From_Cursor
  ( p_cursor in sys_refcursor )
is

  the_name varchar2(4000);
begin
  loop
    fetch p_cursor into the_name;
    exit when p_cursor%notfound;
    Dbms_Output.Put_Line ( the_name );
  end loop;

end Fetch_From_Cursor;
/

It can be invoked with a cursor variable which has been assigned to any SELECT statement against any table whose select list is a single VARCHAR2, for example…

declare
  the_cursor sys_refcursor;
begin

  open the_cursor for
    select last_name from employees order by last_name;
  Fetch_From_Cursor ( the_cursor );
  close the_cursor;


  open the_cursor for
    select department_name from departments order by department_name;
  Fetch_From_Cursor ( the_cursor );

  close the_cursor;
end;
/

Note: the available type sys_refcursor, defining a generic weak cursor, is a usability enhancement, new at Oracle9i. Pre-Oracle9i it would be necessary to define at type, for example…

create or replace package My_Types is
  type Weak_Cursor is ref cursor;
  ...
end My_Types;

/

…and then to declare p_cursor in My_Types.Weak_Cursor and the_cursor My_Types.Weak_Cursor.