Edi Yanto (何 萬 新)

Use a FOR UPDATE Cursor

Posted on: December 18, 2007


The cursor FOR UPDATE clause is only used 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 that 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 will change 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 will lock these rows that have been identified in the active set. If the FOR UPDATE clause is used, then rows may not be fetched from the cursor until a COMMIT has been issued. It is important for you to consider where to place the COMMIT.

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

FOR UPDATE OF <item_name>   Example:

DECLARE

  CURSOR c_course IS

     SELECT course_no, cost

       FROM course FOR UPDATE;

BEGIN

   FOR r_course IN c_course

   LOOP

      IF r_course.cost < 2500

      THEN

         UPDATE course

            SET cost = r_course.cost + 10

          WHERE course_no = r_course.course_no;

      END IF;

   END LOOP;

END;

This example shows how to update the cost of all courses with a cost under $2500. It will increment them by 10.

Q:

In the example just given, where should the COMMIT be placed? What are the issues involved in deciding where to place a COMMIT in this example?

Answer: Placing a COMMIT after each update can be costly. But if there are a lot of updates and the COMMIT comes after the block loop, then there is a risk of a rollback segment not being large enough. Normally, the COMMIT would go after the loop, except when the transaction count is high, and then you might want to code something that does a COMMIT for each 10,000 records. If this were part of a large procedure, you may want to put a SAVEPOINT after the loop. Then, if you need to rollback this update at a later point, it would be an easy task.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

December 2007
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: