3.9 REF CURSORs and Cursor Variables

Table of Contents Previous Next


3 Stored Procedure Language : 3.9 REF CURSORs and Cursor Variables

A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable.
A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable.
REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs.
SPL supports the declaration of a cursor variable 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 like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types. For the rest of this chapter our 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.
The following is the syntax for declaring a SYS_REFCURSOR cursor variable:
name SYS_REFCURSOR;
name is an identifier assigned to the cursor variable.
The following is an example of a SYS_REFCURSOR variable declaration.
The syntax for creating a user defined REF CURSOR type is as follows:
TYPE cursor_type_name IS REF CURSOR [RETURN return_type];
Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set.
OPEN name FOR query;
name is the identifier of a previously declared cursor variable. query is a SELECT command that determines the result set when the statement is executed. The value of the cursor variable after the OPEN FOR statement is executed identifies the result set.
In the following example, the result set is a list of employee numbers and names from a selected department. Note that a variable or parameter can be used in the SELECT command anywhere an expression can normally appear. In this case a parameter is used in the equality test for department number.
After a cursor variable is opened, rows may be retrieved from the result set using the FETCH statement. See Section 3.8.3 for details on using the FETCH statement to retrieve rows from a result set.
In the example below, a FETCH statement has been added to the previous example so now the result set is returned into two variables and then displayed. Note that the cursor attributes used to determine cursor state of static cursors can also be used with cursor variables. See Section 3.8.6 for details on cursor attributes.
Use the CLOSE statement described in Section 3.8.4 to release the result set.
Note: Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost.
So for example, if a procedure performs all three operations, OPEN FOR, FETCH, and CLOSE on a cursor variable declared as the procedure’s formal parameter, then that parameter must be declared with IN OUT mode.
3.9.7 Examples
This variation opens the given cursor variable with a SELECT command that retrieves all rows, but of a given department.
This third variation opens the given cursor variable with a SELECT command that retrieves all rows, but from a different table. Also note that the function’s return value is the opened cursor variable.
Advanced Server also supports dynamic queries via the OPEN FOR USING statement. A string literal or string variable is supplied in the OPEN FOR USING statement to the SELECT command.
OPEN name FOR dynamic_string
[ USING bind_arg [, bind_arg_2 ] ...];
name is the identifier of a previously declared cursor variable. dynamic_string is a string literal or string variable containing a SELECT command (without the terminating semi-colon). bind_arg, bind_arg_2... are bind arguments that are used to pass variables to corresponding placeholders in the SELECT command when the cursor variable is opened. The placeholders are identifiers prefixed by a colon character.
Finally, a string variable is used to pass the SELECT providing the most flexibility.

3 Stored Procedure Language : 3.9 REF CURSORs and Cursor Variables

Table of Contents Previous Next