DECLARE (SELECT … FOR UPDATE)
Associate a database cursor with a SELECT
statement
to perform positioned updates and deletes
Syntax
DECLARE cid [SCROLL] CURSOR [WITH HOLD]
FOR { select-statement | sid }
- cid is the identifier of the database cursor.
- select-statement is a
SELECT
statement defined in static SQL, with theFOR UPDATE
keywords. - sid is the identifier of a prepared
SELECT
statement including theFOR 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.
WITH HOLD
option carefully, because this feature is
specific to IBM® Informix® servers. Other database servers do not behave as Informix does with such cursors. For example, if the
SELECT
is not declared FOR UPDATE
, most database servers keep
cursors open after the end of a transaction, but IBM DB2® automatically closes all cursors when the transaction is
rolled back.