The
ALLOCATE CURSOR statement allocates a cursor for the result set identified
by the result set locator variable.
For more information about result set locator variables,
see the description of the ASSOCIATE LOCATORS statement.
Invocation
This statement can only be embedded
in an SQL procedure. It is not an executable statement and cannot
be dynamically prepared.
Authorization
None required.
Syntax
>>-ALLOCATE--cursor-name--CURSOR FOR RESULT SET--rs-locator-variable-><
Description
- cursor-name
- Names the cursor. 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
-
Names a result set locator variable that has been declared
in the source SQL procedure, according to the rules for declaring
result set locator variables. For more information about declaring
SQL variables, see "Compound SQL (Procedure) statement".
The
result set locator variable must contain a valid result set locator
value, as returned by the ASSOCIATE LOCATORS SQL statement (SQLSTATE
0F001).
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 cannot be used in a positioned UPDATE or
DELETE statement (SQLSTATE 42828).
- An allocated cursor can be closed with the CLOSE statement. Closing
an allocated cursor closes the associated cursor.
- 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.
- Destroying an allocated cursor closes the associated cursor in
the SQL procedure.
Example
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;