chap2_8.html

来自「Oracle资料大集合」· HTML 代码 · 共 411 行 · 第 1/2 页

<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">2.8	How do I匲se SQL*Plus to write repetitive scripts?</font></b>
<p>
<B>Problem</B>
<p>
I need to run a SQL statement for each table in the database. It is a great deal of work to type in a statement for each table. I know that I can save the results of a query to a file and that I can run a file. How do I use SQL*Plus to write repetitive scripts?
<p>
<B>Technique</B>
<p>
The data dictionary contains just about everything you ever wanted to know about the structure of your database. Oracle enables you to concatenate text using the concatenation operator. By concatenating the keywords required by the statement you want to add to the query output, you can build a series of SQL statements based on a query. Because any string can be concatenated with the results of a query, the output of the query can be forced into the format of a SQL statement.
<p>
<B>Steps</B>
<p>
1.	Run SQL*Plus and connect as the WAITE user account. Remove the column headings. A heading can be suppressed by setting the HEADING system variable to OFF.
<p>
<B><code> SQL> SET HEADING OFF</code></B>
<p>
SQL>
<p>
2.	Remove the row count feedback by setting the FEEDBACK system variable to OFF. The FEEDBACK variable controls the row count displayed at the end of a query. The extra line of information will cause an error in the SQL script. Execute a SET command to turn off row feedback.
<p>
<code><B>SQL>  SET FEEDBACK OFF</B></code>
<p>
<code><B>SQL></B></code>
<p>
3.	Widen the output line. SQL*Plus attempts to make it easier to read the output of a query by wrapping the lines. When creating a SQL statement with a query, the default line length might be too small. The LINESIZE system variable determines the length of a line. The maximum value of LINESIZE is operating-system杁ependent. In Microsoft Windows, the maximum value is 32,000.
<p>
<code><B>SQL>  SET LINESIZE 132</B></code>
<p>
<code><B>SQL></B></code>
<p>
4.	Construct the query that creates SQL statements. To do this, concatenate the keywords the statement requires to the table name as it is retrieved from the database. <A HREF="image/02oht18.gif" TARGET="_blank">Figure 2.18</A> shows CHP2_15.SQL, which contains a query. Run the query once to check for syntax errors before writing the output to a file. Running the statement verifies the output.
<p>
Because there are no column headings and no row count, the output is a sequence of SQL statements without the undesirable statements.
<p>
5.	Write the output to a file. The SPOOL command is used to create the output file.
<p>
<code><B>SQL>  SPOOL CMDS.SQL</B></code>

<p>

SQL>

<p>

SQL*Plus commands, query results, and SQL*Plus feedback are written to the CMDS.SQL file. When spooling begins, only statements that produce SQL statements are desired. Be sure to execute all necessary SQL*Plus commands prior to executing the SPOOL command. Unfortunately, there will be a couple undesired lines because the slash and the SPOOL OFF commands are written to the file. These lines will create runtime errors, which can be ignored, or you can use any text editor to remove the unwanted lines from the file.

<p>

6.	Execute the SQL statement from CHP2_15.SQL. This time when the query is executed, the results will be written to the CMDS.SQL file.

<p>

<code><B>SQL>  /</B></code>

<p>

7.	Stop writing to the file by executing the SPOOL OFF command. SPOOL OFF stops the spooling operation and closes the output file.

<p>

<code><B>SQL>  SPOOL OFF</B></code>

<p>

A file that runs like a SQL*Plus command file is created. <A HREF="image/02oht19.gif" TARGET="_blank">Figure 2.19</A> shows the results of the operation.

<p>

<B>How It Works</B>

<p>

Every SQL statement begins with keywords梥uch as SELECT COUNT(*) FROM, DROP TABLE, or INSERT INTO梐nd ends with a semicolon. The string concatenation function ( | | ) enables static text to be combined with the query output to create a SQL statement. The capability of SQL*Plus to write query output to a text file and execute the file enables you to dynamically build SQL scripts as the output from a query. Step 1 suppresses the headings usually displayed by the query. Step 2 suppresses the row count feedback displayed at the end of a query. Step 3 widens the length of the line to ensure that the output fits on a single line. Step 4 creates and runs a query to create the desired SQL statements. Step 5 begins writing the output of the query to a file. Step 6 runs the query, and Step 7 ends the spooling process.

<p>

<B>Comments</B>

<p>

Using SQL*Plus to create SQL commands is a very useful feature. If you look to this technique whenever you need to execute a large number of statements on the database, you will greatly increase your productivity. This technique is used in many places throughout the book.

<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>