CURSOR VARIABLES & REF CURSORS

SYNTAX: 


-- Define a Cursor type
TYPE cursor_type_name IS REF CURSOR [ RETURN return_type ];


-- Declare a Cursor variable
cursor_name cursor_type_name;


-- Open a Cursor object and attach to a Cursor variable
OPEN cursor_name FOR select_statement;


-- Fetch data from Cursor 
FETCH cursor_variable_name INTO [record_name|variable_name, variable_name,..];


A cursor variable is a reference to the work area, but the explicit cursor names the work area.

A cursor varaiable can be opened of any query.


Similar to collections, we need to define the type and then declare a variable of that type.

Example:

Declare
  Type my_ref is ref cursor return source_daily%rowtype;
  my_ref_var my_ref;
Begin
  null;
End;
/


We can classify into two catergories.


1. Strong Ref Cursor :  The return type is defined at the type declaration level

Example:


Declare
  Type my_ref is ref cursor return source_daily%rowtype;
  my_ref_var my_ref;
Begin
  Open my_ref_var for select * from source_daily;
End ;
/



2. Weak Ref Cursor : This can be opened for any query.

Example:

Declare
  Type my_ref is ref cursor;
  my_ref_var my_ref;
Begin
  Open my_ref_var for select * from source_daily;
  Open my_ref_var for select * from target_monthly;
End ;
/



SYS_REFCURSOR:

Starting with Oracle9i Database, Oracle provides a predefined weak REF CURSOR type named SYS_REFCURSOR.

we no longer need to define our own weak type; just use Oracle's

DECLARE
       my_cursor SYS_REFCURSOR;


Note : No need Type statement (Defining Ref Cursor Type).


When ever a cursor variable is opened for the first time, it create a cursor object, which points to the query(result set) & when we re-open the same variable for the second time, it will use the same object, but it changes the query(result set).

If we open a cursor variable and then do assignment to new cursor variable,the newly assigned variable points to the same object.
What ever is done on one variable will reflect to the second.

Example:

Declare
  my_ref_var     sys_refcursor;
  my_ref_var_dup sys_refcursor;
  source_daily_rec source_daily%rowtype;
Begin
  open my_ref_var for select * from source_daily;
  my_ref_var_dup := my_ref_var;
  fetch my_ref_var into source_daily_rec;
  dbms_output.put_line(my_ref_var%rowcount);
  dbms_output.put_line(my_ref_var_dup%rowcount);
  fetch my_ref_var_dup into source_daily_rec;
  dbms_output.put_line(my_ref_var%rowcount);
  dbms_output.put_line(my_ref_var_dup%rowcount);
  close my_ref_var_dup;
  if my_ref_var%isopen then
    dbms_output.put_line('my_ref_var is still open');
  else
    dbms_output.put_line('my_ref_var was closed');
  end if;
End ;
/

Note: The assigned cursor variable acts as alias to the first variable.


ROWTYPE_MISMATCH Exception:

If the query and the INTO clause do not structurally match, then the PL/SQL runtime engine will raise the predefined ROWTYPE_MISMATCH exception.



SCOPE OF CURSOR OBJECT:

Once a OPEN FOR creates a cursor object, the cursor object remains accessible as long as atleast one
cursor variable refers to that cursor object.

This means that we can create a cursor object in one scope (PL/SQL block) and assign it to a cursor variable. Then, by assigning that cursor variable to another cursor variable with a different scope,
the cursor object remains accessible even if the original cursor variable has gone out of scope.

Example:

Create or Replace Function return_cursorobject (my_query varchar2)
 Return SYS_REFCURSOR
IS
  my_local_cur sys_refcursor;
Begin
  Open my_local_cur for my_query;
  return my_local_cur;
End;
/
-- MAIN BLOCK

Declare
  my_local_cursor sys_refcursor;
  my_fectch_rec target_monthly%rowtype;
Begin
  my_local_cursor := return_cursorobject ('select * from target_monthly');
  fetch my_local_cursor into my_fectch_rec;
  dbms_output.put_line(my_local_cursor%rowcount);
End;
/