Cursors as Parameters and Variables

Contact Us or call 1-877-932-8228
Cursors as Parameters and Variables

Cursors as Parameters and Variables

A cursor is tied to a specific query. A cursor variable is not tied to a specific query. It is also known as a REF CURSOR. A REF CURSOR is considered strong if it includes a return type. A strong cursor variable type declaration is usually followed by the declaration of the cursor itself.

TYPE employeeCursorType IS REF CURSOR RETURN employees%ROWTYPE; cursor1 employeeCursorType;

It is considered weak if it does not include a return type.

TYPE weakCursorType IS REF CURSOR; cursor2 weakCursorType;

Strong REF CURSOR types are safer (less error prone) because you are limited to associating them with queries that return a specified set of columns. This is somewhat analogous to coding in a strongly typed language where variable types are clearly defined and casting is very controlled. Weak REF CURSOR types are more flexible because this limitation is not enforced. Any query can be referenced - there is no type checking. This flexibility can lead to errors if an unexpected query is referenced by the cursor and the results contain unexpected values.

It is common to see legacy Oracle PL/SQL packages with REF CURSOR type declarations. Because a weak REF CURSOR has no declared structure, recent versions of Oracle include a the predefined type SYS_REFCURSOR that can be used wherever a weak ref cursor is needed. You can use this in recent Oracle versions instead of declaring your own REF CURSOR type.

Code Sample:

Data-Retrieval-Techniques/Demos/employees_sys_refcursor_and_rowtype.sql
declare
  rc sys_refcursor;
  rec employees%rowtype;
begin

  open rc for 
    select * from employees;

  loop
    fetch rc into rec;
    exit when rc%notfound;
    dbms_output.put_line(rec.first_name ||
                         ' ' ||
                         rec.last_name);
  end loop;

  close rc;
  
  open rc for
    select * from departments;
    dbms_output.put_line('Using cursor again.');
  close rc;
end;
/

Code Explanation

This is a simple example showing the use of %ROWTYPE and SYS_REFCURSOR.

Code Sample:

Data-Retrieval-Techniques/Demos/cursor_variable_pointing_to_different_cursors.sql
declare
  rc sys_refcursor;
  
  v_string_data varchar2(400);
  v_switch NUMBER := 2;
begin
 
  IF v_switch=1 THEN  
    open rc for 
         select first_name 
         from employees;
  ELSE
    open rc for 
         select department_name 
         from departments;
  END IF;
  
  loop
    fetch rc into v_string_data;
    exit when rc%notfound;
    dbms_output.put_line(v_string_data);
  end loop;

  close rc;
end;

Code Explanation

This demonstration shows the flexibility available through the use of a weak REF CURSOR. A SYS_REFCURSOR is used to open one of two possible queries with different rows as results. Depending upon the value of v_switch, a particular cursor is opened and the value fetched and displayed.

Next