createprocedureandfunction.html
来自「java类库详细讲解」· HTML 代码 · 共 272 行
HTML
272 行
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<TITLE>Creating a Stored Procedure or Function in an Oracle Database
(Java Developers Almanac Example)
</TITLE>
<META CONTENT="Patrick Chan" NAME="AUTHOR">
<META CONTENT="Code Examples from The Java Developers Almanac 1.4" NAME="DESCRIPTION">
<META CONTENT="Addison-Wesley/Patrick Chan" NAME="OWNER">
<META CONTENT="3/20/02" NAME="revision">
<STYLE TYPE="text/css">
<!-- BODY CODE {font-family: Courier, Monospace; font-size: 11pt} TABLE, BODY {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10pt} PRE {font-family: Courier, Monospace; font-size: 10pt} H3 {font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 11pt} A.eglink {text-decoration: none} A:hover.eglink {text-decoration: underline} -->
</STYLE>
</HEAD>
<BODY>
<TABLE CELLSPACING="0" CELLPADDING="0" BORDER="0">
<TR>
<TD rowspan="3"><A HREF="/?l=ex"><IMG BORDER="0" ALIGN="BOTTOM" HSPACE="10" SRC="/egs/almanac14a.jpg"></A></TD><TD VALIGN="top"><font face="Times" size="6"><b>The Java Developers Almanac 1.4</b></font>
<br>
Order this book from <a href="/cgi-bin/scripts/redirect.pl?l=ex&url=http://www.amazon.com/exec/obidos/ASIN/0201752808/xeo">Amazon</a>.
</TD>
</TR>
<TR>
<TD align="right" valign="bottom">
<FORM method="get" action="/cgi-bin/search/find.pl">
<INPUT size="25" name="words" type="text"><INPUT value="Search" type="submit">
</FORM>
</TD>
</TR>
</TABLE>
<HR color="#6666cc">
<DIV ALIGN="LEFT">
<A HREF="/">Home</A>
>
<A HREF="../index.html">List of Packages</A>
>
<B><A HREF="../java.sql/pkg.html">java.sql</A></B><font color="#666666" SIZE="-2">
[73 examples]
</font>
>
<B><A HREF="../java.sql/pkg.html#Procedures%20and%20Functions">Procedures and Functions</A></B><font color="#666666" SIZE="-2">
[4 examples]
</font>
</DIV><P>
<h3>
e283.
Creating a Stored Procedure or Function in an Oracle Database</h3>
A stored procedure or function can be created with no parameters, IN
parameters, OUT parameters, or IN/OUT parameters. There can be many
parameters per stored procedure or function.
<P> An IN parameter is a parameter whose value is passed into a
stored procedure/function module. The value of an IN parameter is a
constant; it can't be changed or reassigned within the module.
<P> An OUT parameter is a parameter whose value is passed out of
the stored procedure/function module, back to the calling PL/SQL
block. An OUT parameter must be a variable, not a constant. It can
be found only on the left-hand side of an assignment in the module.
You cannot assign a default value to an OUT parameter outside of the
module's body. In other words, an OUT parameter behaves like
an uninitialized variable.
<P> An IN/OUT parameter is a parameter that functions as an IN or
an OUT parameter or both. The value of the IN/OUT parameter is passed
into the stored procedure/function and a new value can be assigned to
the parameter and passed out of the module. An IN/OUT parameter must
be a variable, not a constant. However, it can be found on both sides
of an assignment. In other words, an IN/OUT parameter behaves like
an initialized variable.
<P>This example creates stored procedures and
functions demonstrating each type of parameter.
<P> See also <a href="../java.sql/CallProcedure.html" class="eglink"><font size="-1"><b>e281</b> Calling a Stored Procedure in a Database</font></a> and
<a href="../java.sql/CallFunction.html" class="eglink"><font size="-1"><b>e282</b> Calling a Function in a Database</font></a>.
<pre>
try {
// To create a connection to an Oracle database,
// see <a href="../java.sql/ConnectOracle.html" class="eglink"><font size="-1"><b>e235</b> Connecting to an Oracle Database</font></a>
Statement stmt = <font color="#0066ff"><i>connection</i></font>.createStatement();
// Create procedure myproc with no parameters
String procedure =
"CREATE OR REPLACE PROCEDURE myproc IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 1'); "
+ "END;";
stmt.executeUpdate(procedure);
// Create procedure myprocin with an IN parameter named x.
// IN is the default mode for parameter, so both `x VARCHAR' and `x IN VARCHAR' are valid
procedure =
"CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); "
+ "END;";
stmt.executeUpdate(procedure);
// Create procedure myprocout with an OUT parameter named x
procedure =
"CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES('string 2'); "
+ "x := 'outvalue'; " // Assign a value to x
+ "END;";
stmt.executeUpdate(procedure);
// Create procedure myprocinout with an IN/OUT parameter named x;
// x functions as an IN parameter and also as an OUT parameter
procedure =
"CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS "
+ "BEGIN "
+ "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
+ "x := 'outvalue'; " // Use x as OUT parameter
+ "END;";
stmt.executeUpdate(procedure);
// Create a function named myfunc which returns a VARCHAR value;
// the function has no parameter
String function =
"CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);
// Create a function named myfuncin which returns a VARCHAR value;
// the function has an IN parameter named x
function =
"CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "RETURN 'a return string'||x; "
+ "END;";
stmt.executeUpdate(function);
// Create a function named myfuncout which returns a VARCHAR value;
// the function has an OUT parameter named x whose value is
// returned to the calling PL/SQL block when the execution of the function ends
function =
"CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= 'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);
// Create a function named myfuncinout that returns a VARCHAR value;
// the function has an IN/OUT parameter named x. As an IN parameter, the value of x is
// defined in the calling PL/SQL block before it is passed in eyfuncinout
// function. As an OUT parameter, the new value of x, `x value||outvalue', is also
// returned to the calling PL/SQL block when the execution of the function ends.
function =
"CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
+ "BEGIN "
+ "x:= x||'outvalue'; "
+ "RETURN 'a returned string'; "
+ "END;";
stmt.executeUpdate(function);
} catch (SQLException e) {
}
</pre>
<P><table width="600" CELLSPACING="0" CELLPADDING="2" BORDER="0">
<tr>
<td bgcolor="#6666cc" align="center"><font color="#ffffff">
Related Examples
</font></td>
</tr>
</table>
e280. <a class="eglink" href="GetProcedures.html?l=rel">
Getting the Stored Procedure Names in a Database
</a>
<br>
e281. <a class="eglink" href="CallProcedure.html?l=rel">
Calling a Stored Procedure in a Database
</a>
<br>
e282. <a class="eglink" href="CallFunction.html?l=rel">
Calling a Function in a Database
</a>
<br>
<table width="600" CELLSPACING="0" CELLPADDING="2" BORDER="0">
<tr>
<td align="left">
<br>
See also:
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Batching">
Batching
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Connections">
Connections
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Database%20Meta%20Data">
Database Meta Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Deleting%20Data">
Deleting Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Drivers">
Drivers
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Importing%20and%20Exporting">
Importing and Exporting
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Inserting%20and%20Updating%20Data">
Inserting and Updating Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Oracle%20OBJECTs">
Oracle OBJECTs
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Oracle%20VARRAYs">
Oracle VARRAYs
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Retrieving%20Data">
Retrieving Data
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Scrollable%20Result%20Sets">
Scrollable Result Sets
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Tables">
Tables
</a>
<a class="eglink" href="/egs/java.sql/pkg.html?l=rel#Updatable%20Result%20Sets">
Updatable Result Sets
</a>
</td>
</tr>
</table>
<br>
<br>
<FONT FACE="Verdana, Arial, Helvetica, sans-serif" SIZE="0">
© 2002 Addison-Wesley.
</FONT>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?