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:
- Remote database access
- 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
- A foreign table, or a view table based on a foreign table
For holdable cursors, see the HiRDB Version 8 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, cursors that reference other RD nodes cannot be opened.
- If more than one cursor is allocated to an extended statement name that identifies a given SELECT statement, a cursor allocation with a WITH HOLD specification and a cursor allocation without that specification cannot be specified on a mixed basis.
- Updates and deletions using a cursor cannot be performed by means of an SQL statement that specifies a foreign table or a view table based on a foreign table.
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 FOR '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.
PREPARE GLOBAL :sel FOR 'SELECT * FROM STOCK FOR UPDATE'
Assign CR1 to the embedded variable cr
ALLOCATE GLOBAL :cr CURSOR FOR GLOBAL :sel
PREPARE PRE1 FOR
'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