Using Cursors to Update Data
To use a cursor to update rows, specify the FOR UPDATE clause when declaring the cursor:
EXEC SQL DECLARE cursor_name CURSOR FOR
select_statement
FOR [DEFERRED | DIRECT] UPDATE FROM column {, column};
The FOR UPDATE clause must list any columns in the selected database table that are intended to be updated. Columns cannot be updated unless they have been declared for update. To delete rows, the cursor does not need to be declared for update.
The cursor UPDATE statement has the following syntax:
EXEC SQL UPDATE tablename
SET column = expression {, column = expression}
WHERE CURRENT OF cursor_name;
Cursor Modes
There are two cursor modes: direct and deferred. The default cursor mode is specified when the DBMS Server is started. The default for ANSI/ISO Entry SQL-92 compliance is direct mode.
Direct Mode for Update
Direct mode allows changes to be seen by the program before the cursor is closed. In direct mode, if a row is updated with a value that causes the row to move forward with respect to the current position of the cursor (for example, a key column is updated), the program sees this row again and takes appropriate steps to avoid reprocessing that row.
Deferred Mode for Update
In a Deferred mode, changes made to the current row of a cursor are not visible to the program that opened the cursor until the cursor is closed. Transaction behavior, such as the release of locks and external visibility to other programs, is not affected if deferred update is used. There can be only one cursor open for update in deferred mode at any given time.
Last modified date: 11/28/2023