SQL Reference

ALLOCATE CURSOR Statement

The ALLOCATE CURSOR statement allocates a cursor for the result set identified by the result set locator variable. See ASSOCIATE LOCATORS Statement for more information on result set locator variables.

Syntax

>>-ALLOCATE--cursor-name--CURSOR FOR RESULT SET--rs-locator-variable-->
 
>--------------------------------------------------------------><
 

Description

cursor-name

Specifies the cursor name. The name must not identify a cursor that has already been declared in the source SQL procedure (SQLSTATE 24502).

CURSOR FOR RESULT SET rs-locator-variable

Specifies a result set locator variable that has been declared in the source SQL procedure, according to the rules for host variables. For more information on declaring SQL variables, see SQL variable declaration.

The result set locator variable must contain a valid result set locator value, as returned by the ASSOCIATE LOCATORS SQL statement (SQLSTATE 24501).

Notes

• Dynamically prepared ALLOCATE CURSOR statements: The EXECUTE statement with the USING clause must be used to execute a dynamically prepared ALLOCATE CURSOR statement. In a dynamically prepared statement, references to host variables are represented by parameter markers (question marks).

  In the ALLOCATE CURSOR statement, rs-locator-variable is always a host variable. Thus, for a dynamically prepared ALLOCATE CURSOR statement, the USING clause of the EXECUTE statement must identify the host variable whose value is to be substituted for the parameter marker that represents rs-locator-variable.

• You cannot prepare an ALLOCATE CURSOR statement with a statement identifier that has already been used in a DECLARE CURSOR statement. For example, the following SQL statements are invalid because the PREPARE statement uses STMT1 as an identifier for the ALLOCATE CURSOR statement and STMT1 has already been used for a DECLARE CURSOR statement.

       DECLARE CURSOR C1 FOR STMT1; 
   
       PREPARE STMT1 FROM
          'ALLOCATE C2 CURSOR FOR RESULT SET ?'; 
  

Rules

• The following rules apply when using an allocated cursor:
  • An allocated cursor cannot be opened with the OPEN statement (SQLSTATE 24502).
  • An allocated cursor can be closed with the CLOSE statement. Closing an allocated cursor closes the associated cursor in the stored procedure.
  • Only one cursor can be allocated to each result set.
• Allocated cursors last until a rollback operation, an implicit close, or an explicit close.
• A commit operation destroys allocated cursors that are not defined WITH HOLD by the stored procedure.
• Destroying an allocated cursor closes the associated cursor in the SQL procedure.

Examples

This SQL procedure example defines and associates cursor C1 with the result set locator variable LOC1 and the related result set returned by the SQL procedure:

     ALLOCATE C1 CURSOR FOR RESULT SET LOC1;