DECLARE (SELECT … FOR UPDATE)

Syntax

DECLARE cid [SCROLL] CURSOR [WITH HOLD]
    FOR { select-statement | sid }

1. cid is the identifier of the database cursor.
2. select-statement is a SELECT statement defined in static SQL, with the FOR UPDATE keywords.
3. sid is the identifier of a prepared SELECT statement including the FOR UPDATE keywords.

Usage

DECLARE ... FOR UPDATE will define a cursor that can be used to do positioned updates and deletes with the WHERE CURRENT OF clause, and/or to set an exclusive lock on fetched rows in a transaction.

DECLARE must precede any other statement that refers to the cursor during program execution.

To perform positioned updates, the select-statement must include the FOR UPDATE keywords.

The scope of reference of the cid cursor identifier is local to the module where it is declared. Therefore, you must execute the DECLARE, UPDATE or DELETE instructions in the same module.

The static select-statement used in the DECLARE can contain ? (question mark) parameter placeholders, that can be bound to program variables with the USING clause of the OPEN instruction.