SPL supports the declaration of a cursor variable by using both the SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.

Only the declaration of SYS_REFCURSOR and user-defined REF CURSOR variables are different. The remaining usage such as opening the cursor, selecting into the cursor, and closing the cursor is the same across both the cursor types. For the rest of this topic, examples will primarily be making use of the SYS_REFCURSOR cursors. All you need to change in the examples to make them work for user-defined REF CURSORs is the declaration section.

Note Strongly-typed REF CURSORs require the result set to conform to a declared number and order of fields with compatible data types and can also optionally return a result set.

Declare a SYS_REFCURSOR cursor variable

The following code is the syntax for declaring a SYS_REFCURSOR cursor variable:

name SYS_REFCURSOR;

name is an identifier assigned to the cursor variable.

The following code is an example of a SYS_REFCURSOR variable declaration:

DECLARE
    emp_refcur      SYS_REFCURSOR;
        ...

Declare a user-defined REF CURSOR type variable

You must perform two distinct declaration steps to use a user-defined REF CURSOR variable:

  • Create a referenced cursor TYPE.
  • Declare the actual cursor variable based on that TYPE.

The syntax for creating a user defined REF CURSOR type is as follows:

TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

The following code is an example of a cursor variable declaration:

DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    my_rec emp_cur_type;
        ...