8. SQL Statements : DECLARE CURSOR : Cursor Modes
 
Cursor Modes
There are two update modes for cursors: DEFERRED and DIRECT.
DEFERRED
Specifies that cursor updates take effect when the cursor is closed. Only thereafter are the updates visible to the program that opened the cursor. The actual committal of the changes does not override or interfere with commit or rollback statements that can be executed subsequently in the program. Transaction semantics, such as the release of locks and external visibility to other programs, are not changed by using the deferred mode of update.
Only one update or delete against a row fetched by a cursor opened in the deferred mode can be executed. If an attempt to update such a row is made more than once, or if the row is updated and deleted, the DBMS Server returns an error indicating that an ambiguous update operation was attempted.
Only one cursor can be open at a time in the deferred mode.
DIRECT
Specifies that updates associated with the cursor take effect on the underlying table when the statement is executed, and can be seen by the program before the cursor is closed. The actual committal of the changes does not override or interfere with COMMIT or ROLLBACK statements subsequently executed in the program. Because changes take effect immediately, avoid updating keys that cause the current row to move forward with respect to the current position of the cursor, because this can result in fetching the same row again at a later point.
Multiple update statements can be issued against a row that was fetched from a cursor opened in the direct mode. This enables a row to be updated and deleted.
Note:  The default cursor mode is specified at the time the DBMS Server is started. For compliance with the ANSI/ISO SQL-92 standard, the default cursor mode must be DIRECT.