FOR UPDATE (OF)串讲

<h1 align="center">FOR UPDATE (OF)串讲</h1>
 
摘录Oracle 11g官方文档《OracleDatabase SQL Language Reference》对for_update_clause的说明：
 
<img alt="" src="https://beijingoptbbs.oss-cn-beijing.aliyuncs.com/cs/5606289-23acf0eaa472b9091dd127b9ef4176fe.png">
for_update_clause
The FOR UPDATE clause lets you lock the selected rows so thatother users cannot lock
or update the rows until you end your transaction. You can specifythis clause only in
a top-level SELECT statement, not in subqueries.
<div>
 
</div>
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
<div>
SecureFilesand Large Objects Developer's Guide.
</div>
 
Nested table rows are not locked as a result of locking the parenttable rows. If you
want the nested table rows to be locked, then you must lock themexplicitly.
Restrictions on the FOR UPDATE Clause This clause is subject to the following
restrictions:
■ You cannot specify this clause with thefollowing other constructs: the DISTINCT
operator, CURSOR expression, set operators, group_by_clause, or aggregate
functions.
■ The tables locked by this clause must all belocated on the same database and on
the same database as any LONG columns and sequences referenced inthe same
statement.
Usingthe FOR UPDATE Clause on Views In general, thisclause is not supported on
views. However, insome 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 blockinternally by the query optimizer, and SELECT ... FOR UPDATE succeeds
on the internallytransformed query. The examples in this section illustrate when using
the FOR UPDATE clause on a view can succeed or fail.
■ Usingthe FORUPDATE 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
– TheFORUPDATE clause applies to a column list
The following statement succeeds because the underlying column ofthe view is
not an expression:
e.g1:
SELECT employee_id FROM (SELECT *FROM employees)
FOR UPDATE OFemployee_id;
The following statement succeeds because, while the underlyingcolumn of the
view is an expression,the FORUPDATE clause does not apply to a column list:
e.g2:
SELECT employee_id FROM (SELECT employee_id+1 ASemployee_id FROM employees)
FOR UPDATE;
The following statement fails because the underlying column of theview is an
expression and the FOR UPDATE clause applies to a column list:
e.g3:
SELECT employee_id FROM (SELECTemployee_id+1 AS employee_id FROM employees)
FOR UPDATE OFemployee_id;
*
Error at line 2:
ORA-01733: virtual column notallowed here
注：当然，改成以下这样是允许的，原因同eg1，因为j_id并不是view中的表达式。
SELECTemployee_id,j_id FROM (SELECT employee_id+1 ASemployee_id,job_id j_id FROM employees)
FORUPDATE OF j_id;
 
■ Using the FOR UPDATE clause on non-merged views
Since the FOR UPDATE clause is not supported on views, anything that prevents
view merging, such as the NO_MERGEhint, parameters that disallow view
merging, or something in the query structure that prevents viewmerging, will
result in an ORA-02014error.
In the following example, the GROUPBY statement prevents view merging, which
causes an error:
SELECT avgsal
FROM (SELECT AVG(salary) AS avgsalFROM employees GROUP BY job_id)
FOR UPDATE;
FROM (SELECT AVG(salary) AS avgsalFROM employees GROUP BY job_id)
*
ERROR