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.
Get Oracle PL/SQL Best Practices now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.