Using SELECT FOR UPDATE Statement in Oracle

This Oracle tutorial explains how to use the Oracle/PLSQL SELECT FOR UPDATE assertion with syntax and examples.

Description

The SELECT FOR UPDATE assertion permits you to lock the data in the cursor end result set. You are no longer required to make modifications to the archives in order to use this statement. The record locks are released when the next commit or rollback assertion is issued.

Syntax

The syntax for the SELECT FOR UPDATE assertion in Oracle/PLSQL is:

CURSOR cursor_name
IS
   select_statement
   FOR UPDATE [OF column_list] [NOWAIT];

Parameters or Arguments

cursor_name The identify of the cursor. select_statement A SELECT assertion that will populate your cursor result set. column_list The columns in the cursor result set that you want to update. NOWAIT Optional. The cursor does no longer wait for resources.

Example

For example, you may want to use the SELECT FOR UPDATE assertion as follows:

CURSOR c1
IS
  SELECT course_number, instructor
  FROM courses_tbl
  FOR UPDATE OF instructor;

If you design on updating or deleting data that have been referenced by means of a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.