Associates a cursor with a static query or a prepared dynamic query statement. The query or the prepared statement can have references to host variables.

Syntax

DECLARE cursor_name CURSOR FOR
  {query_expression[ ORDER BY clause]
  [ FOR UPDATE clause] |prepared_statement_name} ;

Parameters

cursor_name

A name you assign to the cursor. The name must meet the requirements for an identifier.

query_expression [ ORDER BY clause ] [ FOR UPDATE clause ]

A complete query expression.

prepared_statement_name

The name assigned to a prepared SQL statement in an earlier PREPARE statement.

Notes

  • You must declare a cursor before any OPEN, FETCH, or CLOSE statement.
  • The scope of the cursor declaration is the entire source file in which it is declared. The operations on the cursor, such as OPEN, CLOSE, and FETCH statements, can occur only within the same compilation unit as the cursor declaration.
  • The use of a cursor allows the execution of the positioned forms of the UPDATE and DELETE statements.
  • If the DECLARE statement corresponds to a static SQL statement with parameter references:
    • The DECLARE statement must be executed before each execution of an OPEN statement for the same cursor.
    • The DECLARE statement and the OPEN statement that follows must occur within the same transaction within the same task.
    • If the statement contains parameter references to automatic variables or function arguments, the DECLARE statement and the following OPEN statement for the same cursor must occur within the same C function.

Example

EXEC SQL WHENEVER SQLERROR GOTO selerr ;
EXEC SQL DECLARE stcur CURSOR FOR  
     SELECT InvTransNum, Qty, OrderNum FROM PUB.InventoryTrans ;
EXEC SQL OPEN stcur ;      
EXEC SQL WHENEVER NOT FOUND GOTO seldone ;
 
EXEC SQL WHENEVER SQLERROR GOTO selerr ;
EXEC SQL PREPARE stmtid from :sel_stmt_v ;
EXEC SQL DECLARE dyncur CURSOR FOR stmtid ;
EXEC SQL OPEN dyncur ;            
EXEC SQL WHENEVER NOT FOUND GOTO seldone ;