To use a cursor to perform row updating, deletion, or insertion on a table being retrieved, you must define the cursor with DECLARE CURSOR or ALLOCATE CURSOR. When you define the cursor, specify the FOR UPDATE (including FOR UPDATE OF) and FOR READ ONLY clauses according to the processing contents of the UAP.

A good way to update or delete a row that uses a cursor without updating nearly all of the retrieved rows is to specify WITH SHARE LOCK as the lock option. If a lock option is not specified, WITH EXCLUSIVE LOCK is assumed.

Care must be taken, because specifying the FOR UPDATE (or FOR UPDATE OF) clause or the FOR READ ONLY clause may result in a significant drop in processing efficiency in some cases.

Table 3-24 lists the issues to be considered when specifying the FOR UPDATE (or FOR UPDATE OF) clause and the FOR READ ONLY clause.

Table 3-24 Specifying FOR UPDATE and FOR READ ONLY clauses

Application		Consideration
FOR UPDATE clause	Specified for a table being retrieved using a cursor when the rows for which the cursor is used will be updated or deleted, followed by updating, deletion, or addition of rows for which the cursor is not used.	To guarantee correct operation even when the target index is updated during retrieval of a row for which the cursor is used, a work table is created internally during the first FETCH; creation of this work table involves overhead during retrieval.
FOR UPDATE OF clause	Specified for a table being retrieved using a cursor when only some of the columns will be updated.	When the index assigned to a column specified by its column name is used for retrieval, a work table is created internally during the first FETCH; creation of this work table involves overhead during retrieval.
FOR READ ONLY clause	Specified when another cursor will be used for updating (or deletion or insertion) during retrieval using a cursor or for updating (or deletion) by directly specifying a search condition.	When another cursor will be used for updating during retrieval using a cursor, a work table is created internally during the first FETCH so that there will be no impact on the processing result; creation of this work table involves overhead during retrieval.

Even if the FOR UPDATE or FOR READ ONLY clause is not specified, a work table may be created internally during the first FETCH, so overhead creation must still be taken into account.

No internal work table is created when only retrieval is to be performed; in this case, do not consider overhead.