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.
Declare a SYS_REFCURSOR cursor variable
The following code is the syntax for declaring a SYS_REFCURSOR cursor variable:
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; ...