RSS

9.4 FOR UPDATE and WHERE CURRENT Cursors

19 May

The cursor FOR UPDATE clause is used only with a cursor when you want to update tables in the database. Generally, when you execute a SELECT statement, you are not locking any rows. The purpose of using the FOR UPDATE clause is to lock the rows of the tables you want to update so that another user cannot perform an update until you perform your update and release the lock. The next COMMIT or ROLLBACK statement releases the lock. The FOR UPDATE clause changes the manner in which the cursor operates in only a few respects. When you open a cursor, all rows that meet the restriction criteria are identified as part of the active set. Using the FOR UPDATE clause locks these rows that have been identified in the active set. If the FOR UPDATE clause is used, rows may not be fetched from the cursor until a COMMIT has been issued. It is important to think about where to place the COMMIT.

 

The syntax is simply to add FOR UPDATE to the end of the cursor definition. If several items are being selected, but you want to lock only one of them, end the cursor definition with the following syntax:

FOR UPDATE OF <item_name>

Advertisements
 
Leave a comment

Posted by on 05/19/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: