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, you cannot specify both a cursor allocation with a WITH HOLD specification and a cursor allocation without that specification.

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 FROM '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.

    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