使用存储过程
原文: https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html
存储过程是一组形成逻辑单元并执行特定任务的 SQL 语句,它们用于封装一组要在数据库服务器上执行的操作或查询。例如,员工数据库上的操作(雇用,激活,升级,查找)可以编码为由应用程序代码执行的存储过程。存储过程可以使用不同的参数和结果进行编译和执行,并且它们可以具有输入,输出和输入/输出参数的任意组合。
请注意,大多数 DBMS 都支持存储过程,但语法和功能存在相当大的变化。因此,本教程包含两个类, [StoredProcedureJavaDBSample]($docs-gettingstarted.html)
和[StoredProcedureMySQLSample]($docs-gettingstarted.html)
,分别演示如何在 Java DB 和 MySQL 中创建存储过程。
此页面包含以下主题:
示例[StoredProcedureJavaDBSample.java]($docs-gettingstarted.html)
和[StoredProcedureMySQLSample.java]($docs-gettingstarted.html)
创建并调用以下存储过程:
SHOW_SUPPLIERS
:打印一个结果集,其中包含咖啡供应商的名称和他们提供给咖啡休息时间的咖啡。此存储过程不需要任何参数。当示例调用此存储过程时,该示例生成类似于以下内容的输出:Acme, Inc.: Colombian_Decaf
Acme, Inc.: Colombian
Superior Coffee: French_Roast_Decaf
Superior Coffee: French_Roast
The High Ground: Espresso
GET_SUPPLIER_OF_COFFEE
:打印咖啡coffeeName
的供应商名称supplierName
。它需要以下参数:IN coffeeName varchar(32)
:咖啡的名称OUT supplierName varchar(40)
:咖啡供应商的名称
当示例使用
Colombian
作为coffeeName
的值调用此存储过程时,该示例将生成类似于以下内容的输出:Supplier of the coffee Colombian: Acme, Inc.
RAISE_PRICE
:将咖啡coffeeName
的价格提高到newPrice
的价格。如果价格上涨大于maximumPercentage
的百分比,那么价格会提高该百分比。如果价格newPrice
低于咖啡的原始价格,此程序将不会改变价格。它需要以下参数:IN coffeeName varchar(32)
:咖啡的名称IN maximumPercentage float
:提高咖啡价格的最大百分比INOUT newPrice numeric(10,2)
:咖啡的新价格。调用RAISE_PRICE
存储过程后,此参数将包含咖啡的当前价格coffeeName
。
当示例将
Colombian
作为coffeeName
的值,0.10
作为maximumPercentage
的值,19.99
作为newPrice
的值调用此存储过程时,该示例生成类似于以下的输出:Contents of COFFEES table before calling RAISE_PRICE:
Colombian, 101, 7.99, 0, 0
Colombian_Decaf, 101, 8.99, 0, 0
Espresso, 150, 9.99, 0, 0
French_Roast, 49, 8.99, 0, 0
French_Roast_Decaf, 49, 9.99, 0, 0
Calling the procedure RAISE_PRICE
Value of newPrice after calling RAISE_PRICE: 8.79
Contents of COFFEES table after calling RAISE_PRICE:
Colombian, 101, 8.79, 0, 0
Colombian_Decaf, 101, 8.99, 0, 0
Espresso, 150, 9.99, 0, 0
French_Roast, 49, 8.99, 0, 0
French_Roast_Decaf, 49, 9.99, 0, 0
参数属性IN
(默认值),OUT
和INOUT
是参数模式。它们定义了形式参数的作用。下表总结了有关参数模式的信息。
参数模式的特点 | 在 | OUT | 进出 |
---|---|---|---|
必须在存储过程定义中指定吗? | 没有;如果省略,则形式参数的参数模式为IN 。 |
必须指定。 | 必须指定。 |
参数是否将值传递给存储过程或返回值? | 将值传递给存储过程。 | 返回调用者的值。 | 都;将初始值传递给存储过程;将更新的值返回给调用者。 |
形式参数在存储过程中是作为常量还是变量? | 形式参数就像一个常量。 | 形式参数的行为类似于未初始化的变量。 | 形式参数的作用类似于初始化变量。 |
可以在存储过程中为形式参数赋值吗? | 正式参数不能赋值。 | 形式参数不能用于表达式;必须分配一个值。 | 必须为正式参数分配值。 |
可以将哪些实际参数(参数)传递给存储过程? | 实际参数可以是常量,初始化变量,文字或表达式。 | 实际参数必须是变量。 | 实际参数必须是变量。 |
注:有关在 Java DB 中创建存储过程的更多信息,请参见 Java DB 参考手册 中的“CREATE PROCEDURE 语句”部分。
在 Java DB 中创建和使用存储过程涉及以下步骤:
- 在 Java 类中创建公共静态 Java 方法:此方法执行存储过程所需的任务。
- 创建存储过程:该存储过程调用您创建的 Java 方法。
- 将 Java 类(包含您之前创建的公共静态 Java 方法)打包到 JAR 文件中。
- 使用
CALL
SQL 语句调用存储过程。请参阅在 Java DB 和 MySQL 中调用存储过程一节。
以下方法[StoredProcedureJavaDBSample.showSuppliers]($docs-gettingstarted.html)
包含存储过程SHOW_SUPPLIERS
调用的 SQL 语句:
public static void showSuppliers(ResultSet[] rs)
throws SQLException {
Connection con = DriverManager.getConnection("jdbc:default:connection");
Statement stmt = null;
String query =
"select SUPPLIERS.SUP_NAME, " +
"COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"order by SUP_NAME";
stmt = con.createStatement();
rs[0] = stmt.executeQuery(query);
}
SHOW_SUPPLIERS
存储过程不带参数。您可以通过在公共静态 Java 方法的方法签名中定义参数来指定存储过程中的参数。注意,方法showSuppliers
包含ResultSet[]
类型的参数。如果存储过程返回任意数量的ResultSet
对象,请在 Java 方法中指定ResultSet[]
类型的一个参数。此外,请确保此 Java 方法是公共的和静态的。
从 URL jdbc:default:connection
中检索Connection
对象。这是 Java DB 中的一种约定,用于指示存储过程将使用当前存在的Connection
对象。
请注意,此方法中未关闭Statement
对象。不要关闭存储过程的 Java 方法中的任何Statement
对象;如果这样做,当您调用存储过程时发出CALL
语句时,ResultSet
对象将不存在。
为了使存储过程返回生成的结果集,必须将结果集分配给ResultSet[]
参数的数组组件。在此示例中,生成的结果集被分配给数组组件rs[0]
。
以下方法是[StoredProcedureJavaDBSample.showSuppliers]($docs-gettingstarted.html)
:
public static void getSupplierOfCoffee(String coffeeName, String[] supplierName)
throws SQLException {
Connection con = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement pstmt = null;
ResultSet rs = null;
String query =
"select SUPPLIERS.SUP_NAME " +
"from SUPPLIERS, COFFEES " +
"where " +
"SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
"and ? = COFFEES.COF_NAME";
pstmt = con.prepareStatement(query);
pstmt.setString(1, coffeeName);
rs = pstmt.executeQuery();
if (rs.next()) {
supplierName[0] = rs.getString(1);
} else {
supplierName[0] = null;
}
}
形式参数coffeeName
具有参数模式IN
。此形式参数与 Java 方法中的任何其他参数一样使用。因为形式参数supplierName
具有参数模式OUT
,所以它必须使用一维数组数据类型。由于此方法不生成结果集,因此方法定义不包含ResultSet[]
类型的参数。为了从OUT
形式参数中检索值,必须将要检索的值分配给OUT
形式参数的数组组件。在此示例中,将检索到的咖啡供应商名称分配给阵列组件supplierName[0]
。
以下是[StoredProcedureJavaDBSample.raisePrice]($docs-gettingstarted.html) 方法的方法签名:
public static void raisePrice(
String coffeeName, double maximumPercentage,
BigDecimal[] newPrice) throws SQLException
因为形式参数newPrice
具有参数模式INOUT
,所以它必须使用一维数组数据类型。 Java DB 将FLOAT
和NUMERIC
SQL 数据类型分别映射到double
和java.math.BigDecimal
Java 数据类型。
Java DB 将 Java 编程语言用于其存储过程。因此,在定义存储过程时,可以指定要调用的 Java 类以及 Java DB 可以在其中找到的 Java 类。
以下摘自[StoredProcedureJavaDBSample.createProcedures]($docs-gettingstarted.html)
创建了一个名为SHOW_SUPPLIERS
的存储过程:
public void createProcedures(Connection con)
throws SQLException {
Statement stmtCreateShowSuppliers = null;
// ...
String queryShowSuppliers =
"CREATE PROCEDURE SHOW_SUPPLIERS() " +
"PARAMETER STYLE JAVA " +
"LANGUAGE JAVA " +
"DYNAMIC RESULT SETS 1 " +
"EXTERNAL NAME " +
"'com.oracle.tutorial.jdbc." +
"StoredProcedureJavaDBSample." +
"showSuppliers'";
// ...
try {
System.out.println("Calling CREATE PROCEDURE");
stmtCreateShowSuppliers = con.createStatement();
// ...
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtCreateShowSuppliers != null) {
stmtCreateShowSuppliers.close();
}
// ...
}
}
以下列表描述了您可以在CREATE PROCEDURE
语句中指定的过程元素:
PARAMETER STYLE
:标识用于将参数传递给存储过程的约定。以下选项有效:JAVA
:指定存储过程使用符合 Java 语言和 SQL 例程规范的参数传递约定。DERBY
:指定存储过程支持 vararg 作为参数列表中的最后一个参数。
LANGUAGE JAVA
:指定存储过程的编程语言(当前,JAVA
是唯一选项)。DYNAMIC RESULT SETS 1
:指定检索的最大结果集数;在这种情况下,它是1
。EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers'
指定此存储过程调用的完全限定 Java 方法。 注意:Java DB 必须能够在类路径或直接添加到数据库的 JAR 文件中找到此处指定的方法。请参阅以下步骤,在 JAR 文件中打包 Java 类。
以下语句(在[StoredProcedureJavaDBSample.createProcedures]($docs-gettingstarted.html)
中)中创建了一个名为GET_SUPPLIERS_OF_COFFEE
的存储过程(为清楚起见,添加了换行符):
CREATE PROCEDURE GET_SUPPLIER_OF_COFFEE(
IN coffeeName varchar(32),
OUT supplierName
varchar(40))
PARAMETER STYLE JAVA
LANGUAGE JAVA
DYNAMIC RESULT SETS 0
EXTERNAL NAME 'com.oracle.tutorial.jdbc.
StoredProcedureJavaDBSample.
getSupplierOfCoffee'
该存储过程有两个形式参数,coffeeName
和supplierName
。参数说明符IN
和OUT
称为参数模式。它们定义了形式参数的作用。有关详细信息,请参阅参数模式。此存储过程不检索结果集,因此过程元素DYNAMIC RESULT SETS
为0
。
以下语句创建名为RAISE_PRICE
的存储过程(为清晰起见,已添加换行符):
CREATE PROCEDURE RAISE_PRICE(
IN coffeeName varchar(32),
IN maximumPercentage float,
INOUT newPrice float)
PARAMETER STYLE JAVA
LANGUAGE JAVA
DYNAMIC RESULT SETS 0
EXTERNAL NAME 'com.oracle.tutorial.jdbc.
StoredProcedureJavaDBSample.raisePrice'
您可以使用 SQL 脚本在 Java DB 中创建存储过程。请参阅[]($docs-gettingstarted.html)[javadb / create-procedures.sql]($docs-gettingstarted.html)
和[build.xml]($docs-gettingstarted.html)
Ant 构建脚本中的 Ant 目标javadb-create-procedure
。
Ant 构建脚本[build.xml]($docs-gettingstarted.html)
包含用于在 JAR 文件中编译和打包教程的目标。在命令提示符下,将当前目录更改为_< JDBC 教程目录>_
。从此目录中,运行以下命令以在 JAR 文件中编译和打包教程:
ant jar
JAR 文件的名称是_< JDBC 教程目录>_ /lib/JDBCTutorial.jar
。
Ant 构建脚本将文件JDBCTutorial.jar
添加到类路径。您还可以在CLASSPATH
环境变量中指定 JAR 文件的位置。这使 Java DB 能够找到存储过程调用的 Java 方法。
直接将 JAR 文件添加到数据库
Java DB 首先在类路径中查找任何所需的类,然后在数据库中查找。本节介绍如何将 JAR 文件直接添加到数据库。
使用以下系统过程将JDBCTutorial.jar
JAR 文件添加到数据库(为清晰起见,已添加换行符):
CALL sqlj.install_jar(
'<JDBC tutorial directory>/
lib/JDBCTutorial.jar',
'APP.JDBCTutorial', 0)
CALL sqlj.replace_jar(
'<JDBC tutorial directory>/
lib/JDBCTutorial.jar',
'APP.JDBCTutorial')";
CALL syscs_util.syscs_set_database_property(
'derby.database.classpath',
'APP.JDBCTutorial')";
注:方法 StoredProcedureJavaDBSample.registerJarFile
演示如何调用这些系统程序。如果调用此方法,请确保已修改[javadb-sample-properties.xml]($docs-gettingstarted.html)
,以便将属性jar_file
的值设置为[COD2 的完整路径名称] ]。
SQL
模式中的install_jar
过程将 JAR 文件添加到数据库。此过程的第一个参数是运行此过程的计算机上的 JAR 文件的完整路径名。第二个参数是 Java DB 用于引用 JAR 文件的标识符。 (标识符APP
是 Java DB 默认模式。)replace_jar
过程替换数据库中已有的 JAR 文件。
系统过程SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY
设置或删除当前连接上数据库的属性值。此方法将属性derby.database.classpath
设置为install_jar
文件中指定的标识符。 Java DB 首先查找类的 Java 类路径,然后查看derby.database.classpath
。
在 Java DB 中创建和使用存储过程涉及以下步骤:
- 使用 SQL 脚本或 JDBC API 创建存储过程
- 使用
CALL
SQL 语句调用存储过程。请参阅部分调用 Java DB 和 MySQL 中的存储过程
MySQL 对其存储过程使用基于 SQL 的语法。以下来自 SQL 脚本[mysql / create-procedures.sql]($docs-gettingstarted.html)
的摘录创建了一个名为SHOW_SUPPLIERS
的存储过程:
SELECT 'Dropping procedure SHOW_SUPPLIERS' AS ' '|
drop procedure if exists SHOW_SUPPLIERS|
# ...
SELECT 'Creating procedure SHOW_SUPPLIERS' AS ' '|
create procedure SHOW_SUPPLIERS()
begin
select SUPPLIERS.SUP_NAME,
COFFEES.COF_NAME
from SUPPLIERS, COFFEES
where SUPPLIERS.SUP_ID = COFFEES.SUP_ID
order by SUP_NAME;
end|
DROP PROCEDURE
语句删除该程序SHOW_SUPPLIERS
(如果存在)。在 MySQL 中,存储过程中的语句由分号分隔。但是,需要使用不同的分隔符来结束create procedure
语句。此示例使用管道(|
)字符;你可以使用另一个字符(或多个字符)。分隔语句的这个字符在调用此脚本的 Ant 目标的delimiter
属性中定义。这段摘录来自 Ant 构建文件[build.xml]($docs-gettingstarted.html)
(为了清楚起见,已插入换行符):
<target name="mysql-create-procedure">
<sql driver="${DB.DRIVER}"
url="${DB.URL}" userid="${DB.USER}"
password="${DB.PASSWORD}"
classpathref="CLASSPATH"
print="true"
delimiter="|"
autocommit="false"
onerror="abort">
<transaction
src="./sql/${DB.VENDOR}/
create-procedures.sql">
</transaction>
</sql>
</target>
或者,您可以使用DELIMITER
SQL 语句指定不同的分隔符。
CREATE PROCEDURE
语句由过程的名称,括号中的逗号分隔的参数列表以及BEGIN
和END
关键字中的 SQL 语句组成。
您可以使用 JDBC API 创建存储过程。以下方法[StoredProcedureMySQLSample.createProcedureShowSuppliers]($docs-gettingstarted.html)
执行与上一个脚本相同的任务:
public void
createProcedureShowSuppliers()
throws SQLException {
String createProcedure = null;
String queryDrop =
"DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";
createProcedure =
"create procedure SHOW_SUPPLIERS() " +
"begin " +
"select SUPPLIERS.SUP_NAME, " +
"COFFEES.COF_NAME " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"order by SUP_NAME; " +
"end";
Statement stmt = null;
Statement stmtDrop = null;
try {
System.out.println("Calling DROP PROCEDURE");
stmtDrop = con.createStatement();
stmtDrop.execute(queryDrop);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmtDrop != null)
{
stmtDrop.close();
}
}
try {
stmt = con.createStatement();
stmt.executeUpdate(createProcedure);
} catch (SQLException e) {
JDBCTutorialUtilities.printSQLException(e);
} finally {
if (stmt != null) { stmt.close(); }
}
}
请注意,此方法中未更改分隔符。
即使方法createProcedureShowSuppliers
的返回类型为void
并且该方法不包含任何参数,存储过程SHOW_SUPPLIERS
也会生成结果集。使用方法CallableStatement.executeQuery
调用存储过程SHOW_SUPPLIERS
时,将返回结果集:
CallableStatement cs = null;
cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();
以下摘自方法[StoredProcedureMySQLSample.createProcedureGetSupplierOfCoffee]($docs-gettingstarted.html)
包含创建名为GET_SUPPLIER_OF_COFFEE
的存储过程的 SQL 查询:
public void createProcedureGetSupplierOfCoffee()
throws SQLException {
String createProcedure = null;
// ...
createProcedure =
"create procedure GET_SUPPLIER_OF_COFFEE(" +
"IN coffeeName varchar(32), " +
"OUT supplierName varchar(40)) " +
"begin " +
"select SUPPLIERS.SUP_NAME into " +
"supplierName " +
"from SUPPLIERS, COFFEES " +
"where SUPPLIERS.SUP_ID = " +
"COFFEES.SUP_ID " +
"and coffeeName = COFFEES.COF_NAME; " +
"select supplierName; " +
"end";
// ...
}
该存储过程有两个形式参数,coffeeName
和supplierName
。参数说明符IN
和OUT
称为参数模式。它们定义了形式参数的作用。有关详细信息,请参阅参数模式。形式参数在 SQL 查询中定义,而不是在方法createProcedureGetSupplierOfCoffee
中定义。要为OUT
参数supplierName
赋值,此存储过程使用SELECT
语句。
以下摘自方法[StoredProcedureMySQLSample.createProcedureRaisePrice]($docs-gettingstarted.html)
包含创建名为RAISE_PRICE
的存储过程的 SQL 查询:
public void createProcedureRaisePrice()
throws SQLException {
String createProcedure = null;
// ...
createProcedure =
"create procedure RAISE_PRICE(" +
"IN coffeeName varchar(32), " +
"IN maximumPercentage float, " +
"INOUT newPrice numeric(10,2)) " +
"begin " +
"main: BEGIN " +
"declare maximumNewPrice " +
"numeric(10,2); " +
"declare oldPrice numeric(10,2); " +
"select COFFEES.PRICE into oldPrice " +
"from COFFEES " +
"where COFFEES.COF_NAME " +
"= coffeeName; " +
"set maximumNewPrice = " +
"oldPrice * (1 + " +
"maximumPercentage); " +
"if (newPrice > maximumNewPrice) " +
"then set newPrice = " +
"maximumNewPrice; " +
"end if; " +
"if (newPrice <= oldPrice) " +
"then set newPrice = oldPrice; " +
"leave main; " +
"end if; " +
"update COFFEES " +
"set COFFEES.PRICE = newPrice " +
"where COFFEES.COF_NAME " +
"= coffeeName; " +
"select newPrice; " +
"END main; " +
"end";
// ...
}
存储过程使用SET
和SELECT
语句为INOUT
参数newPrice
赋值。要退出存储过程,存储过程首先将语句括在标有main
的BEGIN ... END
块中。要退出该过程,该方法使用语句leave main
。
以下摘自方法[runStoredProcedures]($docs-gettingstarted.html)
,调用存储过程SHOW_SUPPLIERS
并打印生成的结果集:
cs = this.con.prepareCall("{call SHOW_SUPPLIERS()}");
ResultSet rs = cs.executeQuery();
while (rs.next()) {
String supplier = rs.getString("SUP_NAME");
String coffee = rs.getString("COF_NAME");
System.out.println(supplier + ": " + coffee);
}
注:与Statement
对象一样,要调用存储过程,可以调用execute
,executeQuery
或executeUpdate
,具体取决于过程返回的ResultSet
对象数。但是,如果您不确定该过程返回了多少ResultSet
对象,请调用execute
。
调用存储过程SHOW_SUPPLIERS
在在 MySQL 中使用 JDBC API 创建存储过程一节中进行了演示。
以下摘自方法[runStoredProcedures]($docs-gettingstarted.html)
,调用存储过程GET_SUPPLIER_OF_COFFEE
:
cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
cs.setString(1, coffeeNameArg);
cs.registerOutParameter(2, Types.VARCHAR);
cs.executeQuery();
String supplierName = cs.getString(2);
接口CallableStatement
扩展PreparedStatement
。它用于调用存储过程。通过调用相应的 setter 方法,就像使用PreparedStatement
对象一样,指定IN
参数的值(例如本例中的coffeeName
)。但是,如果存储过程包含OUT
参数,则必须使用registerOutParameter
方法注册它。
以下摘自方法[runStoredProcedures]($docs-gettingstarted.html)
,调用存储过程RAISE_PRICE
:
cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
cs.setString(1, coffeeNameArg);
cs.setFloat(2, maximumPercentageArg);
cs.registerOutParameter(3, Types.NUMERIC);
cs.setFloat(3, newPriceArg);
cs.execute();
由于参数newPrice
(过程RAISE_PRICE
中的第三个参数)具有参数模式INOUT
,因此必须通过调用相应的 setter 方法指定其值并使用registerOutParameter
方法进行注册。