Oracle PL/SQL Best Practices

Name

SQL-11: Specify columns to be updated in a SELECT FOR UPDATE statement.

Synopsis

Use the SELECT FOR UPDATE statement to request that locks be placed on all rows identified by the query. This is done when you know you will change some or all of those rows, and you don’t want another session to change them out from under you.

Specify the columns to be updated so that (a) anyone reading the code knows the intentions of your program, and (b) if your query contains a join of more than one table, Oracle will lock only the rows in those tables that contain any of the specified columns.

Example

The following code sets the favorite ice cream flavor of the Feuerstein family to ROCKY ROAD, but doesn’t lock any rows in the person table:

DECLARE
   CURSOR change_prefs_cur IS
      SELECT PER.name, PREF.name flavor
        FROM person PER, preference PREF
       WHERE PER.name = PREF.person_name
         AND PREF.type = 'ICE CREAM'
         FOR UPDATE OF PREF.name;
BEGIN
   FOR rec IN change_prefs_cur
   LOOP
      IF rec.name LIKE 'FEUERSTEIN%'
      THEN
         UPDATE preference SET name = 'ROCKY ROAD'
          WHERE CURRENT OF change_prefs_cur;
      END IF;
   END LOOP;
END;
/

Benefits

You keep to a minimum the number of locks placed on rows in tables.

You self-document the behavior of your code, which is important for those who come to your code later in its life to maintain it.

Resources

forupdate.sql : Contains the code for the example in this section.