Cursor For Update:

Very frequently, the processing done in a fetch loop changes the rows which have been retrieved through the cursor.PL/SQL give a convenient syntax for doing this. This method consists of two parts:

• In the cursor declaration the FOR UPDATE clause
• In a DELETE or UPDATE statement the WHERE CURRENT OF clause.

FOR UPDATE

The clause FOR UPDATE identifies the rows which will be updated or deleted and then locks the rows in the result set.

Syntax

SELECT...FROM...FOR UPDATE [OF column_reference] [NOWAIT]

The column_reference is a column in the table against that the query is performed and a list of columns can also be used.

Generally SELECT operation will not take any locks on the rows being accessed. This permits other sessions connected to the database to modify the data being selected. The result set is even consistent. When the cursor is opened and the active set is determined.  Any changes that have been committed previous to this point are reflected in the active set. Any modifications made after this point still if they are committed are not reflected unless the cursor is reopened, that will evaluate the active set again. This is known as read-consistency. Thus, in the case of FOR UPDATE clause an exclusive row locks are taken on the rows in the active set before the OPEN returns. These locks avoid other sessions from changing the rows in the active set until the transaction is committed.

If another session already has locks on the rows in the active set and then the SELECT FOR UPDATE operation will wait for this waiting period- the SELECT FOR UPDATE will hang until the other session releases the lock. To handle this condition, the NOWAIT clause can be used. If the rows are locked through another session, then OPEN will return instantly with the Oracle error:

ORA-54: resource busy and obtain with NOWAIT specified