FOR UPDATE (OF)串讲.docVIP

FOR UPDATE (OF)串讲 摘录Oracle 11g官方文档《Oracle Database SQL Language Reference》对for_update_clause的说明： for_update_clause The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries. Note: Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with an embedded SELECT ... FOR UPDATE statement. You can do this using one of the programmatic languages or DBMS_LOB package. For more information on lock rows before writing to a LOB, see Oracle Database SecureFiles and Large Objects Developers Guide. Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, then you must lock them explicitly. Restrictions on the FOR UPDATE Clause This clause is subject to the following restrictions: ■ You cannot specify this clause with the following other constructs: the DISTINCT operator, CURSOR expression, set operators, group_by_clause, or aggregate functions. ■ The tables locked by this clause must all be located on the same database and on the same database as any LONG columns and sequences referenced in the same statement. Using the FOR UPDATE Clause on Views In general, this clause is not supported on views. However, in some cases, a SELECT ... FOR UPDATE query on a view can succeed without any errors. This occurs when the view has been merged to its containing query block internally by the query optimizer, and SELECT ... FOR UPDATE succeeds on the internally transformed query. The examples in this section illustrate when using the FOR UPDATE clause on a view can succeed or fail. ■ Using the FOR UPDATE clause on merged views An error can occur when you use the FOR UPDATE clause on a merged view if both of the following conditions apply: – The underlying column of the view is an expression – The FOR UPDATE clause applie