chap11_7.html

Oracle资料大集合

<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1">11.7	How do I匧ock rows when I query them?</font></b>
<p>
<B>Problem</B>
<p>
In my programs, I have procedures that perform queries and later update the results. I need to ensure that other users cannot update the records while the procedures are processing data. How do I lock rows when I query them?
<p>
<B>Technique</B>
<p>
The FOR UPDATE clause of a SELECT statement locks the rows returned by the query until a COMMIT statement is executed. When the FOR UPDATE clause is used in a query, another user cannot update, delete, or lock the rows until the lock is released. If the rows you are attempting to query for update are locked, the query will wait until the lock is released, unless the NO WAIT clause is specified.
<p>
<B>Step</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP11_17.SQL, shown in <A HREF="image/11oht15.gif" TARGET="_blank">Figure 11.15</A>, creates the sample tables used in this How-To.
<p>
The sample table is a simple table containing the departments in an organization. Data used to demonstrate the locking capabilities of the FOR UPDATE clause is created. Run the file to create the sample table and data.
<p>
<code><b>SQL>  START CHP11_17.sql
<p>
Table dropped.
<p>
Table created.
<p>
Table dropped.
<p>
Table created.
<p>
1 row inserted.
<p>
1 row inserted.
<p>
1 row inserted.
<p>
Commit complete.</b></code>
<p>
<B>2.</B>	Load CHP11_18.SQL into the SQL buffer. The file contains a query that locks the rows as they are queried.
<p>
<code><b>SQL> GET CHP11_18.sql
<p>
1  SELECT DEPT_NO
<p>
2  FROM
<p>
3     DEPT11
<p>
4  WHERE
<p>
5     DEPT_NO = 25
<p>
6* FOR UPDATE</b></code>
<p>
Lines 4 and 5 specify the WHERE clause identifying which rows the query will retrieve. Line 6 tells Oracle to lock the rows retrieved by the query.
<p>
<B>3.</B>	Run the query to return the results and lock the rows.
<p>
<code><b>SQL>  /
<p>
DEPT_NO
<p>
-------
<p>
25</b></code>
<p>
<B>4.</B>	Load CHP11_19.SQL into the SQL buffer. The file contains a query of multiple tables but only locks one of them.
<p>
<code><b>SQL>  GET CHP11_19.sql
<p>
1   SELECT DEPT11.DEPT_NO, EMP_NO
<p>
2   FROM DEPT11, EMP11
<p>
3   WHERE
<p>
4   DEPT11.DEPT_NO = EMP11.DEPT_NO
<p>
5*  FOR UPDATE OF DEPT11.DEPT_NO</b></code>
<p>
The FROM clause in line 2 specifies the two tables used in the query. Line 4 contains the join condition between the two tables. Line 5 specifies that only DEPT11 will have rows returned by the query locked. Rows from EMP11 will not be locked and can be updated by another process.
<p>
<B>5.</B>	Run the query to lock the rows.
<p><code><b>
SQL>  /
</b></code>
<td><code><b>DEPT_NO</b></code></TD>
<td><code><b>EMP_NO</b></code></TD>

<td><code><b>---------</b></code></TD>
<td><code><b>---------</b></code></TD>

<TD><code><b>25 </b></code></TD>
<TD><code><b>1</b></code></TD>

<TD><code><b>26</b></code></TD>
<TD><code><b>2</b></code></TD>

<p>
The query locks the DEPT11 records returning the two rows.
<p>
<B>6.</B>	Execute a COMMIT statement to unlock the rows. When rows are locked using the SELECT statement with a FOR UPDATE clause, they remain locked until a COMMIT or ROLLBACK statement is executed or until the session is terminated.
<p>
<code><b>COMMIT;</b></code>
<p>
<B>How It Works</B>
<p>
Step 1 creates two sample tables and records used throughout this How-To. Steps 2 and 3 use the FOR UPDATE clause in a SELECT statement to lock all rows returned by the query. The query only has a single source table, so the FOR UPDATE clause knows which table to lock. Steps 4 and 5 demonstrate a query from multiple tables, only one of which is locked. The table name is specified in the FOR UPDATE clause, telling Oracle to lock only that table. If the table name is not specified in the clause, records from both tables are locked. Step 6 unlocks any locked records by executing a COMMIT statement.
<p>
<B>Comments</B>
<p>
If you are planning to update a record that will be first returned by a query, it is a good idea to lock the record to prevent other users from updating it. Remember to unlock the record if you decide not to commit the changes you make.
<p>

<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>