📄 chap7_3.html

📁 Oracle资料大集合
💻 HTML
字号:
<HTML><HEAD><TITLE>Oracle8 How-To</TITLE><SCRIPT><!--function displayWindow(url, width, height) {        var Win = window.open(url,"displayWindow",'width=' + width +',height=' + height + ',resizable=1,scrollbars=yes');}//--></SCRIPT></HEAD><BODY BACKGROUND="../../../images/curve_itlibrary_white.gif" BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#DD0000" VLINK="#999999" ALINK="#FF0000"><!--Begin Header Table --><TABLE width="640" cellpadding="0" cellspacing="0" border="0"><TR><TD VALIGN="TOP" WIDTH="640" COLSPAN="3"><A HREF="http://www.earthweb.com" target="resource window"><IMG SRC="../../../images/eweb_banner.gif" VSPACE="6" WIDTH="640" HEIGHT="25" ALT="Brought to you by EarthWeb" border="0"><BR></a></TD></TR><TR><TD VALIGN="TOP" WIDTH="150"><A HREF="http://www.itlibrary.com/"><IMG SRC="../../../images/small_logo.gif" WIDTH=103 HEIGHT=82 ALT="IT Library Logo" border="0"></a><P><!--Begin Subscribe Box Table --><TABLE border="0" cellpadding="0" cellspacing="0" WIDTH="100"><TR><TD></TD></TR></TABLE><!--End Subscribe Box Table --></td><TD><img src="../../../images/dotclear.gif" WIDTH="15" HEIGHT="1"></TD><TD><!--  Begin Ads itl //--><a href="http://www.itlibrary.com/adclick.html/CID=00000349829b438e00000000/site=itlibrary/area=itl/aamsz=468x60" TARGET="new win"><img src="../../../../adimages.earthweb.com/images/ads/spidercatcher.gif@9688" ALT="Click Here!" border="0"><BR><CENTER>Click Here!</CENTER></a><!-- itl End Ads //--></td></TR></TABLE><!--End Header Table --><!--Begin Main Table --><TABLE width="640" cellpadding="0" cellspacing="0" border="0"><TR><!--Begin Left Navigation column --><TD VALIGN="TOP" WIDTH="140"><FORM action="/subscribe/list.cgi" method="post"><INPUT name='email' value='your e-mail' size="11"><INPUT type='hidden'  name='state' value='Subscribe'><br><INPUT type='image' value='subscribe' img src='/images/subscribe.gif' border='0' vspace='3' ></center></form><form name="Search" method="GET" action="http://search.earthweb.com/search97/search_redir.cgi"><INPUT TYPE="hidden" NAME="Action" VALUE="Search"><INPUT TYPE="hidden" NAME="SearchPage" VALUE="http://search.earthweb.com/search97/samples/forms/srchdemo.htm"><INPUT TYPE="hidden" NAME="Collection" VALUE="Site Index"><INPUT TYPE="hidden" NAME="ResultTemplate" VALUE="itl.hts"><INPUT TYPE="hidden" NAME="ViewTemplate" VALUE="view.hts"></font><table width=130 border=0 cellpadding=0 cellspacing=0><tr><td><font face="arial, helvetica" size=2 color="white"><b>Search the site:</b></font><br><INPUT NAME="queryText" size=8 VALUE="">&nbsp;<input type="submit" name="submitbutton" value="Go!"></td></tr><INPUT type=hidden NAME="section_on" VALUE="on"><tr><td><SELECT NAME="section" size=1><option value="../../../../www.itlibrary.com/default.htm" SELECTED>ITLibrary<option value="../../../../www.itknowledge.com/default.htm">ITKnowledge</SELECT><a href="http://www.itlibrary.com/search/"><font face="arial, helvetica" color=FFFFFF size=1>EXPERT SEARCH</a></font><IMG src="../../../images/rule.gif" width="100" vspace=3 height="2" ALT="-----"></form></td></tr></table><table bgcolor="#cc9900" cellpadding="0" cellspacing="0" border="0" width=110><tr><td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td><td><b><a href="http://www.itlibrary.com/reference/dir.programminglanguages.html"><font face="verdana,helvetica" size="1" color="000000">Programming Languages</font></a></b></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.databases.html"><font face="verdana,helvetica" size="1" color="000000"><b>Databases</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.security1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Security</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.webservices.html"><font face="verdana,helvetica" size="1" color="000000"><b>Web Services</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.networkservices1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Network Services</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.middleware.html"><font face="verdana,helvetica" size="1" color="000000"><b>Middleware</b></font></td></tr><tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.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.3	How do I匘etermine if a view can be updated?</font></b><p><B>Problem</B><p>We use views to make querying data easier. I know many views can be updated, but I need a method to determine which ones they are. How do I determine if a view can be updated?<p><B>Technique</B><p>A view cannot be updated in some cases if it joins tables, uses a DISTINCT operator, or contains a GROUP BY clause or group functions. How-To 7.4 fully describes how to update a join view. You can use the TEXT column in the ALL_VIEWS data dictionary view to determine if any of these limitations exist in the query. An easier way to determine if the view can be updated is to perform a data management operation and trap the error if it occurs.<p><B>Steps</B><p><B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP7_6.SQL, shown in <A HREF="image/07oht04.gif" TARGET="_blank">Figure 7.4</A>, contains the statements used to build two tables and two views that will test the code segment developed in this How-To.<p>The two tables, TAB_A07 and TAB_B07, are the source tables for the two views. The first view, VIEW0_A07, selects all of the columns from the TAB_A07 table and can be updated. The second view, VIEW_B07, joins the two sample tables and cannot be updated due to the join condition.<p><B>2.</B>	Run the statement to create the tables and views.<p><code><b>SQL>  START CHP7_6.sql<p>Table created.<p>Table created.<p>View created.<p>View created.</b></code><p><B>3.</B>	Load CHP7_7.SQL into the SQL buffer. The file contains an INSERT statement that tests if a data management statement can be performed on a view. Because the INSERT statement uses a query of the view, it is not necessary to know the structure of the view. The technique shown in this step will test all views.<p><code><b>SQL> GET CHP7_7<p>1 INSERT INTO VIEW_B07<p>2  SELECT * FROM VIEW_B07<p>3*  WHERE 1 = 0<></b></code><p>Line 1 specifies that records are inserted into VIEW_B07. Line 2 specifies that the records inserted are also queried from VIEW_B07. The WHERE clause in line 3 doesn抰 insert any rows, because 1 is never equal to 0. If an INSERT statement cannot be executed on the view, an error will occur when the statement is run.<p><B>4.</B>	Run the statement to attempt to insert a record into the view.<p><code><b>SQL>  /<p>insert into VIEW_B07 select * from VIEW_B07<p>* ERROR at line 1:<p>ORA-01779: cannot modify a column which maps to a non key-preserved table</b></code><p>The Oracle error ORA-01779 is returned when a data manipulation operation is attempted on a view where it is not allowed.<p><B>5.</B>	Load CHP7_8.SQL into the SQL buffer. The statement in the file creates a stored function to determine if a data manipulation operation can be performed on a view. For more information about the creation of stored functions, see How-To 10.4.<p><code><b>SQL>  GET CHP7_8.sql<p> 1 CREATE OR REPLACE FUNCTION CHECK_VIEW<p> 2   (VIEW_NAME IN VARCHAR2) RETURN BOOLEAN IS<p> 3 TMP VARCHAR2(60);<p> 4  CURSOR_HANDLE INTEGER;<p> 5  CNT INTEGER;<p> 6 BEGIN<p> 7  TMP := 慖NSERT INTO
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -