ALLOCATE CURSOR statement Format 1 (Allocate a statement cursor)
Function
Defines and allocates a cursor to the SELECT statement (dynamic SELECT statement) preprocessed by a PREPARE statement.
Privileges
Format 1: Allocating a cursor to the SELECT statement (dynamic SELECT statement) preprocessed by a PREPARE statement
ALLOCATE extended-cursor-name CURSOR [WITH HOLD] FOR extended-statement-name |
Operands
- extended-cursor-name
Specifies the extended cursor name for the cursor to be allocated.
For extended cursor names, see 2.27 Extended cursor name.
- [WITH HOLD]
Specify this operand when allocating a cursor as a holdable cursor. A holdable cursor, however, cannot be used in the following cases:
- When specifying a table containing an abstract data type for which a plug-in is used in a FROM clause
- A query for a named derived table that was derived by specifying a function call using a plug-in
- A retrieval through a list
For holdable cursors, see the HiRDB Version 9 UAP Development Guide.
- extended-statement-name
Within the scope of this command, specifies an extended statement name that identifies the SELECT statement preprocessed by a PREPARE statement.
For extended statement names, see 2.26 Extended statement name.
Common rules
- Any allocated cursor is closed.
- If more than one cursor is allocated to an extended statement name that identifies a given SELECT statement, you cannot specify both a cursor allocation with a WITH HOLD specification and a cursor allocation without that specification.
Notes
- An error may occur if a specified extended cursor name is already allocated in the prevailing scope.
Examples
- Allocate a cursor (extended-cursor-name: cr (scope: GLOBAL)) to fetch rows, row by row, from the inventory table (STOCK):
PREPARE GLOBAL :sel FROM 'SELECT * FROM STOCK'
ALLOCATE GLOBAL :cr CURSOR FOR GLOBAL :sel
- While retrieving all rows from the inventory table (STOCK) using a cursor (extended-cursor-name: cr (scope: GLOBAL, value: CR1)), dynamically update the unit price (PRICE) in the row at the cursor position to a 10% discount.
Set any name as the embedded variable :sel
PREPARE GLOBAL :sel FROM 'SELECT * FROM STOCK FOR UPDATE'
Assign CR1 to the embedded variable cr
ALLOCATE GLOBAL :cr CURSOR FOR GLOBAL :sel
PREPARE PRE1 FROM
'UPDATE SET PRICE = 10% discount value on the unit price WHERE CURRENT OF GLOBAL CR1'
OPEN GLOBAL :cr
FETCH GLOBAL :cr INTO Name of the variable into which columns are fetched
EXECUTE PRE1
CLOSE GLOBAL :cr
DEALLOCATE PREPARE GLOBAL :sel