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

None.

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

Common rules

  1. Any allocated cursor is closed.
  2. 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.
  3. 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.
  4. 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

  1. An error may occur if a specified extended cursor name is already allocated in the prevailing scope.

Examples

  1. 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

  2. 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