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