SELECT FOR UPDATE in PL/SQL Cursors

April 30, 2009

When we  issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. Only those records which have been changed but not yet committed are locked
Others will be able to read those records as they appeared before the change.

When you  want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking.

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement.
No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT.
The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

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

For example, you could use the Select For Update statement as follows:

CURSOR Brands
IS
SELECT Brand_Code, Brand_Name
from Brand_Master
FOR UPDATE of Brand_Code;

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.

Share and Enjoy:
  • Twitter
  • Google Buzz
  • DZone
  • Digg
  • Reddit
  • Diigo
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • LinkedIn
  • Mixx
  • email
  • PDF
  • Add to favorites

tags: ,
posted in Oracle, PL/SQL by chandu

Follow comments via the RSS Feed | Leave a comment | Trackback URL

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org