chap7_4.html

Oracle资料大集合

<td><a href="http://www.itlibrary.com/reference/dir.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</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.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</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.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</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.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</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.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</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.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</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.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">7.4	How do I匔reate an updatable join view?</font></b>
<p>
<B>Problem</B>
<p>
I want to create views based upon my tables and objects, and would like to allow the underlying tables to be modified. My application requires that the tables and objects can be inserted, updated, and deleted, yet I want to retain the flexibility and security of using views. How do I create an updatable join view?
<p>
<B>Technique</B>
<p>
When a view is based upon a single table or object, all Data Manipulation Language statements (DML) may be performed on the view such as insert, update, and delete, if the user account has proper permissions. However, when two or more tables are joined within a view, there are limitations on if and how the underlying tables may be inserted, updated, and deleted.
<p>
Only one table may be modified, and with certain restrictions. The most important condition is that the table being modified is key-preserved. A key-preserved table has all of its identifying unique key columns contained within the SELECT clause of the view text. For the view to be updatable, the resulting join must also uniquely define each record in the view. This How-To will provide examples that explain this important condition.
<p>
In addition to having to be key-preserved, the view must not contain certain clauses. These are the ROWNUM pseudocolumn, table hierarchies (START WITH; CONNECT BY), set operations (UNION, MINUS, and so on), grouping functions (DISTINCT; GROUP BY; HAVING), and math functions (COUNT, MAX, SUM, and so on).
<p>
To delete records, only one table in the resulting join may be key-preserved. If two or more tables are key-preserved, Oracle would not know which table to delete from, due to the ambiguity of the statement.
To insert records, all columns of the view must come from key-preserved tables. If a view has several columns from several tables, and at least one column is from an underlying non杒ey-preserved table, then Oracle would not be able to determine how to uniquely identify the record being inserted.
<p>
As with inserting records, to update records, all columns of the view must come from key-preserved tables. If a view has several columns from several tables, and at least one column is from an underlying non杒ey-preserved table, then Oracle would not be able to determine how to uniquely identify the record being updated.
<p>
<code><b>
SQL> START CHP7_11.SQL
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
SQL></b></code>

<p>

<B>Steps</B>

<p>

<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP7_10.SQL creates the PART07 and FACTORY07 tables. Run the script, as shown in <A HREF="image/07oht05.gif" TARGET="_blank">Figure 7.5</A>.
<p>
Notice that the FACTORY_NO column of the FACTORY07 table is defined as the primary key. If the FACTORY_NO column appears in the view, it will be an updatable key-defined view. Also, the PARTNO column of the PART07 table will be the key-defined column whose inclusion in the view is necessary for updating data.
<p>
<B>2.</B>	Run the CHP7_11.SQL script to insert data into each table. The data will be used to describe how views may be updated. CHP7_11.SQL is shown in <A HREF="image/07oht06.gif" TARGET="_blank">Figure 7.6</A>.

<code><b><p>
SQL> START CHP7_11.SQL
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
1 row created.
<p>
SQL></b></code>

<p>

<B>3.</B>	Now that the two tables have data, run the CHP7_12.SQL script to create the PART_VIEW07 view, which is a join of the PART07 and FACTORY07 tables. This process is shown in <A HREF="image/07oht07.gif" TARGET="_blank">Figure 7.7</A>.

<p>

<B>4.</B>	The PART_VIEW07 view has been created. Note that because of the many-to-one relationship between PART07 and FACTORY07, only the PART07 table may be updated. In our example, there are three records that exist where the FACTORY_NO=20. If the entire operation were to move to Tokyo, we would want to update all records to point to Japan. Because the FACTORY07 is not a key-preserved table in this join view, the statement will fail. Run CHP7_13.SQL to see the results.

<code><b>
<p>
SQL> GET CHP7_13.SQL
<p>
1  UPDATE PART_VIEW07
<p>
2  SET LOCATION=扵OKYO, JAPAN