Associating a Cursor with a Prepared Statement

The PREPARE statement lets you assemble the text of an SQL statement at runtime and pass the statement text to the database server for execution. If you anticipate that a dynamically prepared SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that returns values could produce more than one row of data, the prepared statement must be associated with a cursor. (See PREPARE.)

The result of a PREPARE statement is a statement identifier (statement id or id variable), which is a data structure that represents the prepared statement text. To declare a cursor for the statement text, associate a cursor with the statement identifier.

You can associate a sequential cursor with any prepared SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. You cannot associate a scroll cursor with a prepared INSERT statement or with a SELECT statement that was prepared to include a FOR UPDATE clause.

After a cursor is opened, used, and closed, a different statement can be prepared under the same statement identifier. In this way, it is possible to use a single cursor with different statements at different times. The cursor must be redeclared before you use it again.

The following example contains code that prepares a SELECT statement and declares a sequential cursor for the prepared statement text. The statement identifier st_1 is first prepared from a SELECT statement that returns values; then the cursor c_detail is declared for st_1.

EXEC SQL prepare st_1 from
   'select order_date 
      from orders where customer_num = ?';
EXEC SQL declare c_detail cursor for st_1;

If you want to use a prepared SELECT statement to modify data, add a FOR UPDATE clause to the statement text that you want to prepare, as the following example shows:

EXEC SQL prepare sel_1 from 
   'select * from customer for update';
EXEC SQL declare sel_curs cursor for sel_1;

DDL operations that change the schema of a table can invalidate a cursor whose associated prepared statement or associated routine references the modified table, unless the prepared objects are recompiled, or unless the routine is reoptimized. For more information, see the section DDL Operations on Tables Referenced by Cursors.

The DECLARE statement allows you to declare a cursor for the collection variable. Such a cursor is called a Collection cursor. You use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.

The Collection-Derived Table segment identifies the collection variable for which to declare the cursor. For more information, see Collection-Derived Table.