ch14.htm

Teach yourself Oracle8 in 21 day

the calling program, or you want to return more than one value, use a procedure.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For the remainder of this lesson,
	the term <I>procedure</I> is used to refer to both procedures and functions because
	both are similar in nature and function. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>How Procedures and Functions Operate</B></FONT></H3>
<P>Procedures and functions use the same basic syntax in the program body with the
exception of the <TT>RETURN</TT> keyword, which can only be used by functions. The
body itself is made up of PL/SQL blocks that perform the desired function and return
the desired data to the calling program. The goal of the body of the procedure is
both to minimize the amount of data to be transmitted across the network (to and
from the calling program) and to perform the PL/SQL statements in the most efficient
manner possible.
<H4><FONT COLOR="#000077"><B>The PL/SQL Language</B></FONT></H4>
<P>PL/SQL is a block-structured language offered by Oracle to facilitate the use
of the Oracle RDBMS. It has the following properties and features that can be used
to aid in application development:

<UL>
	<LI>Block structure--The block structure allows blocks to contain nested subblocks.
	<P>
	<LI>Block declarations--Each block can have its own declarations, which means that
	you can logically separate functions.
	<P>
	<LI>Variable declaration--Variables can be declared and used within a PL/SQL block.
	<P>
	<LI>Constant declaration--Constants can be declared and referenced within a PL/SQL
	block.
	<P>
	<LI>Conditional statements--PL/SQL allows for conditional processing with <TT>IF</TT>...<TT>THEN</TT>...<TT>ELSE</TT>,
	<TT>WHILE</TT>...<TT>LOOP</TT>, <TT>FOR</TT>...<TT>LOOP</TT>, <TT>EXIT</TT>...<TT>WHEN</TT>,
	and <TT>GOTO</TT> functions.
</UL>

<P>These features make PL/SQL a powerful SQL processing language. Using PL/SQL has
several major advantages over using standard SQL statements (in addition to allowing
the use of stored procedures and functions). Among these are ease of use, portability,
and higher performance.</P>
<P>The primary performance difference between PL/SQL and SQL is the fact that PL/SQL
statements are transmitted to Oracle as a block of statements rather than as individual
state-ments. In a network application, the additional overhead needed to transmit
individual statements can be quite high. It takes very little additional CPU and
network resources to send a larger packet than it does to send a smaller one.</P>
<P><FONT COLOR="#000077"><B>The <TT>RETURN</TT> Statement</B></FONT></P>
<P>In the declaration portion of a function, a <TT>RETURN</TT> <I>parameter</I> is
used to declare the type of the return value. Later, in the body of the function,
the <TT>RETURN</TT> <I>statement</I> is used to exit the function and return the
specified value to the calling program. With a procedure, the <TT>RETURN</TT> statement
can also be used, but not to return a value. In a procedure, the <TT>RETURN</TT>
statement can be used only to exit the procedure. No values can be associated with
the <TT>RETURN</TT> statement in a procedure.</P>
<P><FONT COLOR="#000077"><B>The <TT>EXCEPTION</TT> Statement</B></FONT></P>
<P>In both procedures and functions, you can add optional exception handlers. These
exception handlers allow you to return additional information based on certain conditions
(such as no data found or some user-specified condition). By using exception handlers
and allowing the stored procedure to notify you of some special conditions, you can
minimize the amount of return-value checking that must be done in the application
code. Because the work to determine that no data has been selected has already been
done by the RDBMS engine, you can save on resources if you take advantage of this
information.</P>
<P><FONT COLOR="#000077"><B>The <TT>RDBMS_OUTPUT</TT> Package</B></FONT></P>
<P>To visually represent data selected within a stored procedure or function, you
can use the <TT>RDBMS_OUTPUT</TT> package supplied by Oracle. To see data returned
by <TT>RDBMS_OUTPUT</TT> in SQL*Plus or Server Manager, you must set the <TT>SERVEROUTPUT</TT>
option by issuing the command <TT>SET SERVEROUTPUT ON</TT>. Also be sure to terminate
the procedure with a slash (<TT>/</TT>) to invoke it. When using the <TT>RDBMS_OUTPUT</TT>
package, you can select several options for inputting or outputting data. The following
procedures are available in the <TT>RDBMS_OUTPUT</TT> package:

<UL>
	<LI><TT>RDBMS_OUTPUT.ENABLE</TT>--Enables output processing.
	<P>
	<LI><TT>RDBMS_OUTPUT.DISABLE</TT>--Disables output processing.
	<P>
	<LI><TT>RDBMS_OUTPUT.PUT_LINE</TT>--Places a newline-terminated string in the buffer.
	<P>
	<LI><TT>RDBMS_OUTPUT.PU</TT>--Places a string in the buffer (no newline).
	<P>
	<LI><TT>RDBMS_OUTPUT.GET_LINE</TT>--Gets one line from the buffer.
	<P>
	<LI><TT>RDBMS_OUTPUT.GET_LINE</TT>--Gets an array of lines from the buffer.
</UL>

<P>In this manner, you can use a stored procedure for ad-hoc functions that require
data to be displayed in SQL*Plus. The typical stored procedure is used to return
data that has been bound to variables in a program.
<H3><FONT COLOR="#000077"><B>How to Create Stored Procedures and Stored Functions</B></FONT></H3>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>There are advantages to using
procedures and functions; however, the greatest advantage of using functions and
procedures happens when the procedures and functions are stored in the database.
Such procedures and functions are referred to as <I>stored procedures</I> and <I>stored
functions</I>. A stored procedure or stored function has the advantage of being stored
in the library cache in an already parsed form, thus reducing parsing time. In this
section you will see how to parse the procedure and store it in the database.</P>
<P>To create a stored procedure or function, use the keywords <TT>CREATE PROCEDURE</TT>
or <TT>CREATE FUNCTION</TT> with the same syntax as the <TT>PROCEDURE</TT> and <TT>FUNCTION</TT>
commands shown earlier in this lesson. When creating a procedure or function, however,
the <TT>IS</TT> keyword is replaced with the <TT>AS</TT> keyword. Listing 14.3 shows
an example of how to create a stored procedure to retrieve some information from
the <TT>Dogs</TT> table.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The typical stored procedure or
	function is called by an application program. In the following example, however,
	to better illustrate how a stored procedure is coded, I chose to use SQL*Plus. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000000"><B>INPUT:</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 14.3. Creating the stored procedure </B>old_dogs<B>.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">SQL&gt; CREATE OR REPLACE PROCEDURE 
  2      old_dogs
  3  AS
  4      CURSOR dog_cursor IS
  5      SELECT 
  6          dogname, age, owner
  7      FROM dogs
  8      WHERE age &gt; 8;
  9  BEGIN
 10      RDBMS_OUTPUT.PUT_LINE(`Dogs older than 8 years old');
 11      RDBMS_OUTPUT.PUT_LINE(`Name  Age  Owner');
 12      FOR dog IN dog_cursor LOOP
 13      RDBMS_OUTPUT.PUT_LINE(dog.dogname||'  `||dog.age||'  `||dog.owner);
 14      END LOOP;
 15  END old_dogs;
 16  /
</FONT></PRE>
<P><FONT COLOR="#0066FF"><TT>Procedure created.</TT></FONT></P>
<P>To view the output of this stored procedure from SQL*Plus, you must to enable
the <TT>SERVEROUTPUT</TT> option, as follows:</P>
<P><B>INPUT:</B></P>
<PRE><FONT COLOR="#0066FF">SQL&gt; set serveroutput on
</FONT></PRE>
<P><B>INPUT:</B></P>
<PRE><FONT COLOR="#0066FF">SQL&gt; execute old_dogs;
</FONT></PRE>
<P>The resulting output of this procedure is</P>
<P><B>OUTPUT:</B></P>
<PRE><FONT COLOR="#0066FF">Dogs older than 8 years old
Name  Age  Owner
Shasta  9  Jones
Jessy  10  Wilson
Ruff  9  King
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">PL/SQL procedure successfully completed.
</FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>As you can see, to enable the stored procedure to return multiple rows selected
from the <TT>Dogs</TT> table, it is necessary to declare a cursor. By looping through
this cursor, you can output all the lines that were selected.
<H3><FONT COLOR="#000077"><B>How to Replace Procedures and Functions</B></FONT></H3>
<P>If the procedure or function is already stored in the library cache, you must
<I>replace</I>, rather than <I>create</I>, the procedure or function. You do this
by using the command <TT>CREATE OR REPLACE PROCEDURE</TT> or <TT>CREATE OR REPLACE
FUNCTION</TT>. With this command, an already-present procedure or function is replaced;
if it is not already present, it is created.
<H3><FONT COLOR="#000077"><B>Packages</B></FONT></H3>
<P><I>Packages</I> are sets of related procedures or functions that are compiled
and stored together in the data dictionary. They allow you to group together PL/SQL
types, objects, and subprograms into a logical unit. When you link these logically
related entities together, it can be easier to program and modify modules based on
their function and relation. Performance is enhanced because the entire package is
loaded into memory when it is first called, thus increasing the chance for a cache
hit on a related function or object that is likely to be called soon.</P>
<P>Packages are actually created in a statement with two different parts. The first
is the declaration part, where the package is defined. Then there is the package
body definition, where the body of the package is defined. The syntax of the statement
used to create the package definition is as follows:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">CREATE PACKAGE package_name AS package_specification
    public type and object declaration
    subprogram definition
END [package_name];
</FONT></PRE>
<P>This definition part of the package creation declares the parts of the package
available to the user. The rest of the package definition is used by the user, but
is not visible to the user. This second part has the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">CREATE PACKAGE BODY package_name AS package_body
    private type and object declaration
    subprogram bodies
[BEGIN
    initialization statements]
END [package_name];
</FONT></PRE>
<P>The user application must have knowledge of the package specification in order
to call the package correctly. The arrangement of the package-creation process has
several advantages: