chap10_4.html

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

<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"><B>10.4 How do I匔reate a stored function?</B></font></b>
<p>

<B>Problem</B>

<p>

In my PL/SQL program, there is repetitive code that could be better designed using functions. I want to create a stored function that returns a result after performing some operations. I also want to create some local procedures and functions within a PL/SQL block. How do I create a stored function?

<p>

<B>Technique</B>

<p>

Stored functions are similar to stored procedures, except that a stored function returns a value and
must be called as part of an expression. A function is a named PL/SQL block, which contains a declarative, an execution, and an exception section. The declarative and exception sections are optional. For more information about the sections of a PL/SQL block, see How-To 10.1. The structure of a stored function is shown here:

<p>

<code><b>CREATE [OR REPLACE] FUNCTION function_name (parameter list) IS

<p>
[Optional declarative section contains all local types, variables, constants,

<p>
cursors and subprogram declarations. Note: There is no DECLARE statement.]
<p>
BEGIN
<p>
Executable section which is run when the function is executed.
<p>
A RETURN statement must be specified to return a value.
<p>
[EXCEPTION
<p>
Optional exception section to handle errors that may occur.]
<p>
END;</b></code>
<p>
A stored function can accept parameters like a stored procedure (more information on passing parameters is covered in How-To 10.2). It must contain a RETURN statement to return a value back to the calling expression. When created, the stored function can be used in expressions in other PL/SQL blocks executed by the function owner or any user account granted EXECUTE privileges to the stored function.
<p>
Ever since the release of PL/SQL version 2.1, stored functions can be called from SQL statements with certain limitations. Stored functions can be used like system functions presented in Chapter 2, but they must meet certain conditions, depending on how they are used. Parameters used in these functions must use positional notation. They cannot be OUT or IN OUT parameters and are limited to only those datatypes supported by SQL. No stored function used in a SQL statement can modify data, and no procedures or functions called by the function can modify data. These rules are automatically enforced by checking the function body of a standalone stored function, but for functions defined in a package, the RESTRICT_REFERENCES pragma should be used in the package specification to enforce these rules, as explained in How-To 10.5.
<p>
Local subprograms (procedures and functions) can be specified in the declarative section of a PL/SQL block and remain local to the block; they can be called only from the executable section of the same block. Except that the CREATE OR REPLACE keywords cannot be used, a local procedure or function specified in the declarative section of a PL/SQL block has the same structure as stored procedures and functions. They all contain a header, a declarative section, an executable section, and an exception section. Local subprograms can reference other local subprograms as long as the referenced subprogram is declared before the referencing subprogram, or at least a forward declaration is provided for the referenced subprogram. All type, variable, and cursor declarations for the PL/SQL block must precede local subprogram declaration.
<p>
<B>Steps</B>
<p>
<B>1.</B> Run SQL*Plus, connect as the WAITE user account, and execute the CHP10_13.SQL file. It has a CREATE OR REPLACE FUNCTION statement to create a stored function, or you can replace it if it already exists in the database. It receives a single string parameter and returns a string. The results of the operation are displayed in <A HREF="image/10oht13.gif" TARGET="_blank">Figure 10.13</A>.
<p>
Lines 1 and 2 contain the function specification. Line 2 specifies the name of the function, its parameter, and the datatype of the value the function returns. All functions must include a returned datatype, which must be contained in the function specification. Lines 3 through 5 specify the body of the function. Line 4 uses the RETURN statement to return the value of the input parameter concatenated with a text string. A RETURN statement must be executed when the function is run, and a RETURN statement in a function must contain an expression. It is possible for a function to contain more than one RETURN statement, but only one can be executed each time the function is called.
<p>
<B>2.</B>	Run the CHP10_14.SQL file in SQL*Plus. The PL/SQL block contained in the file executes the stored function and displays the value returned. The results of the operation are displayed in <A HREF="image/10oht14.gif" TARGET="_blank">Figure 10.14</A>.
<p>
Lines 1 and 2 contain the declarative section of the anonymous block. Line 2 declares a variable assigned the value returned by the function created in the previous steps. Line 4 calls the stored function, passing a string as a parameter, and it assigns the return value to the local variable created in line 2. Line 5 displays the string returned by the function.
<p>
When the PL/SQL block is executed, the stored function modifies the parameter passed and returns a string, which is displayed in SQL*Plus using the DBMS_OUPUT.PUT_LINE procedure.
<p>
<B>3.</B>	Run the CHP10_15.SQL file in SQL*Plus. A function is declared within the declarative section of the anonymous PL/SQL block. <A HREF="image/10oht15.gif" TARGET="_blank">Figure 10.15</A> shows the output of the block in SQL*Plus.
<p>
Lines 1 through 5 contain the declarative section of the block. Lines 2 through 5 declare a function local to the block. Line 2 contains the function specification for the local function. Lines 3 through 5 contain the executable section of the local function. Line 4 returns the string 慦ORLD