chap10_1.html

Oracle资料大集合

<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"><B>10.1	How do I匔reate anonymous PL/SQL blocks?</B></font></b>

<p>

<B>Problem</B>

<p>

I want to perform tasks not well-suited for SQL on the database, such as performing conditional logic and working with the procedural components available in PL/SQL. Because the PL/SQL block is the smallest logical unit of PL/SQL code, I want to start by creating a PL/SQL block to perform a simple action. How do I create anonymous PL/SQL blocks in Oracle?

<p>

<B>Technique</B>

<p>

PL/SQL is a block structured language. PL/SQL blocks are implemented using anonymous blocks, procedures, and functions. Unlike functions and procedures, no name is associated with an anonymous PL/SQL block and, hence, cannot be referenced from other PL/SQL blocks. Anonymous blocks are standalone; they neither take input parameters nor return values and can be nested within another PL/SQL block. The anonymous PL/SQL block is made up of three sections, of which two are optional (indicated by square brackets):

<p>

<code><b>DECLARE

<p>

Declarative Section]

<p>BEGIN

<p>Executable Section

<p>

[EXCEPTION

<p>

Exception handling Section]

<p>

END;</b></code>

<p>

The declarative section, started with the DECLARE statement, is where objects are declared. User-defined variables, datatypes, cursors, and PL/SQL subprograms local to the block are declared in this section. The objects within the declarative section are limited to the block and cannot be used outside it. The executable section starts with the BEGIN keyword and contains procedural and SQL statements that are executed when the block is run. Next, the exception section starts with the EXCEPTION keyword and contains exception handlers to handle errors that occur during processing. The error handling code in the exception section is executed only when an error occurs. A PL/SQL block is terminated with the END statement and a semicolon.

<p>

<B>Steps</B>

<p>

<B>1.</B>	Run SQL*Plus and connect to the database as the WAITE user account. Run the CHP10_1.SQL file at the prompt. It has the simplest form of the anonymous block, one that does not have the optional declaration and exception sections. The PL/SQL block contained in the file calls the DBMS_OUTPUT.PUT_LINE to display a message in SQL*Plus. <A HREF="image/10oht01.gif" TARGET="_blank">Figure 10.1</A> shows the code and the output of the anonymous PL/SQL block in SQL*Plus.

<p>

For the output of the DBMS_OUTPUT package to be displayed in SQL*Plus, the SERVEROUTPUT system variable must be set to ON. The anonymous block calls the PUT_LINE procedure in the DBMS_OUTPUT built-in package to display a text message in SQL*Plus. The PUT_LINE procedure is passed a string to be displayed within SQL*Plus and can accept DATE and NUMBER variables as well. To display data of other datatypes, a conversion function such as TO_CHAR must be used. Table 10.1 shows procedures in the DBMS_OUTPUT package.

<p>

Table 10.1 Procedures in the DBMS_OUTPUT package
<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR><td>Procedure</td>
<td>Description</td></TR>

<TR>
<td>ENABLE</td>
<td>Enables message output (or SERVEROUTPUT ON)</td>
</TR>

<TR>
<td>DISABLE</td>
<td>Disables message output (or SERVEROUTPUT OFF)</td>
</TR>

<TR><td>PUT_LINE</td>
<td>Puts the output on a new line</td></TR>

<TR><td>PUT</td>
<td>Puts the output on the same line</td></TR>

<TR><td>NEW_LINE</td>
<td>Signals the beginning of a new line</td></TR>

<TR><td>GET_LINE</td>
<td>Retrieves a line from the buffer</td></TR>

<TR><td>GET_LINES</td>
<td>Retrieves an array of lines from the buffer</td></TR>
</table>
<P>

Note that line 4 in the file has a / signaling SQL*Plus to run the anonymous block after it has loaded the block into the SQL*Plus buffer. The DBMS_OUTPUT.PUT_LINE procedure is important because it is often used to debug PL/SQL code.

<p>

To debug a PL/SQL block with compilation errors, issue a SHOW ERRORS command at the SQL*Plus prompt; you can also include the SHOW ERRORS command at the end of the block in the line following the /, as shown in the preceding example. This makes SQL*Plus display any errors generated by the last module compiled in the current session. A blank line is left as the line following the /, to avoid a benign SQL*Plus error (Input truncated to n characters).

<p>

<B>2.</B>	Run CHP10_2.SQL, which has a PL/SQL block that declares and uses a numeric variable. <A HREF="image/10oht02.gif" TARGET="_blank">Figure 10.2</A> shows the code and output.

<P>

Lines 1 and 2 contain the declarative section of the block, which is started with the DECLARE keyword and continues until the BEGIN keyword. When declaring variables in PL/SQL, the datatype is specified after the variable name. Lines 4 and 5 contain the code segment executed when the block is run. The statement on line 4 sets the value of the variable ORDER_NO to the number 3000. The colon-equals operator (:=) is used to set a variable to a value in PL/SQL.

<p>

Table 10.2 contains the operators used in PL/SQL, listed in the order of descending precedence; operators with higher precedence are evaluated first. Whenever the precedence of operators is in doubt or you need to override the default precedence, use the ( and ) expression delimiters around the expression that needs to be evaluated first.

<p>

Exceptions are handled in the exception section starting on line 6. The WHEN OTHERS exception handler in line 7 handles any exceptions not dealt with by other exception handlers and must be the last exception handler. The NULL statement in line 8 is a 揹o nothing