The following example illustrates this declaration:

DECLARE
TYPE stocks_cur_type IS REF CURSOR RETURN stocks%rowtype;
                           -- strong cursor type creation
TYPE stocks_cur_price IS REF CURSOR;
                             -- weak cursor type creation

stocks_cur  stocks_cur_type;
      -- creation of cursor variable based on cursor type

BEGIN
...
END;

Remember, the cursor variable declaration does not create a cursor object, but rather a pointer to a cursor object. As such, you cannot substitute a cursor variable where a proper cursor is expected and required.

Cursor Usage with Cursor Variables

After the cursor variable is declared, you can use the variable in three different statements: OPEN...FOR, FETCH, and CLOSE. You can assign a value to it through the OPEN...FOR cursor statement.

The Syntax for the OPEN...FOR Cursor Statement

OPEN cursor_name FOR select_statement

In this syntax, cursor_name is the name for the cursor, the cursor variable, or the host cursor variable, and select_statement is the appropriate SQL statement. This statement cannot use the FOR UPDATE clause.

The OPEN...FOR statement executes the multirow query associated with the declared cursor variable. The OPEN...FOR statement also identifies the result set, which consists of all rows that meet the query search criteria. Other OPEN...FOR statements can open the same cursor variable for different queries as needed. The following is an example of opening a cursor variable that is a bind variable:

BEGIN
OPEN :stocks_quote FOR SELECT * FROM stocks;
END;

After the cursor is opened, you can perform a typical FETCH using the cursor variable. The syntax for the FETCH statement using a cursor variable is the same as for a normal static cursor.

The Syntax for the FETCH Statement Using a Cursor Variable

FETCH cursor_variable_name INTO record_name or variable_name

In this syntax, cursor_variable_name is the variable you declared in the local block, package specification, or host environment such as Pro*C, and record_name or variable_name is the object where the FETCH will place the data from the cursor.

PL/SQL will make sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding variable in the INTO clause. Also, the number of fields or variables must equal the number of column values.

The following example pulls together the concepts of declaring, opening, and fetching cursors:

DECLARE
TYPE stocks_cur_type IS REF cursor RETURN stocks%rowtype
stocks_cur   stocks_cur_type;

BEGIN
OPEN stocks_cur for
SELECT stock_name, stock_type, stock_quote from stocks;
FETCH stocks_cur INTO stocks_rec;
END;

After you are finished using the cursor variable in your logic, you need to close the variable. The syntax follows your normal cursor syntax as illustrated in the following:

CLOSE stocks_cur;

The following example illustrates the use of different INTO clauses on different fetches that happen to use the same cursor variable. Each of the fetches retrieves another row from the same result set:

BEGIN
...
FETCH stock_quote_cur INTO stock_rec1;
...
FETCH stock_quote_cur INTO stock_rec2:
...
END;

Cursor Variable Assignments

The cursor variable can be included in any assignment statement.

The Syntax for Cursor Variable Assignments

assignment statement := cursor variable name

In this syntax, cursor variable name is a PL/SQL cursor variable that has been previously defined and is in the scope of the assignment statement. This can also be a host cursor variable name. The host cursor variable name is declared in a PL/SQL host environment such as Pro*C. This variable is passed into the assignment statement as a bind variable.

An example of the assignment statement using a cursor variable (bind variable) is

current_stock_price := :host_cur_stock_quote;

Cursor Variables as Arguments

As mentioned earlier, Oracle allows you to pass Oracle cursor variables as arguments to procedures and functions. When your parameter list contains a cursor variable, the mode of the parameter and the datatype must be specified. The datatype for the cursor variable used as an argument will be the REF cursor type.

The following example illustrates the use of the REF cursor syntax and the use of a cursor variable as a parameter:

DECLARE
TYPE cur_var_type IS REF CURSOR RETURN employee%rowtype;

PROCEDURE SELECT emp_query (cur_var_out  out cur_var_type) IS
BEGIN
...
END;

This example works well in local modules with a program. However, if you are creating a standalone procedure or function, then you must reference a pre-existing REF cursor type that exists in a package specification. As you recall from Day 8, "Procedures, Packages, Errors, and Exceptions," all variables declared in a package specification are global to the package body. Hence you can reference a cursor type using the standard package dot notation.

To use a cursor variable in a package, you must first declare the REF cursor type in the specification, as illustrated in the next example:

PACKAGE stocks
IS
TYPE cur_var_type IS REF CURSOR RETURN stocks%rowtype;
END package;

To use the cursor variable declared in the preceding example, simply reference the REF cursor type using the dot notation as shown following:

PROCEDURE obtain_stock_quotes (cur_var_out in stocks.cur_var_type) IS
BEGIN
...
END;

You can use the preceding notation for any of the function and procedure calls within that package.

The cursor variable is a true variable and global in nature to the package or local module. It does not reflect the state of the object but rather is the reference to the cursor object. To change the value of a cursor variable, you must change the cursor object to which the variable points.

The cursor variable argument can have one of three different modes. These modes are

• IN—The program can have read-only abilities with the parameter. In other words, the cursor argument is passed only to the procedure or function.