![关闭 [x]](http://dw1.s81c.com/i/c.gif){kind=small}
| 免费下载：IBM® Informix® 11.7 试用版（包括 Ultimate Edition、Developer Edition 和 Innovator-C Edition） |
|---|
| 下载更多的 IBM 软件试用版，并加入 IBM 软件下载与技术交流群组，参与在线交流。 |
在关系数据库应用程序中，与单个 SQL 语句相比，使用存储过程的主要优势在于查询计划（或执行计划）是在创建存储过程时生成的，并且需要对存储过程的每次执行重用相同的查询计划，这节省了数据库服务器中的大量资源。创建存储过程之后，任何数据库客户端（如 JDBC 应用程序）都可以随意调用这些过程，无需制定新的执行计划。
使用存储过程的方式因数据库服务器而异。数据库管理系统 (DBMS)，如 Informix 和 DB2®，使用不同的 SQL 语法来执行存储过程。当应用程序开发人员需要编写以多个 DBMS 为目标的代码时，这使得事情变得更加困难。可调用语句提供了一个在所有 DBMS 系统中使用相同的 SQL 语句的执行存储过程的方法。
假设我们有一个 JDBC 应用程序，该应用程序需要高效地一次又一次重复某个任务序列。我们可能希望使用 Java™ 方法，但我们希望进行多少次客户端/服务器通信来发送和接收数据呢？数据库服务器将为应用程序发送的每个 SQL 语句准备并生成一个查询计划，这将占用一些 CPU 时间。尽管考虑到了性能，但对一个 SQL 语句使用简单的 Java 方法可能不太好。
使用存储过程（它只是一次性任务）会怎么样呢？创建一个 SQL调用，并且您可以使用 CallableStatement 对象从 JDBC 应用程序中调用它，CallableStatement 对象充当了服务器上存储过程的调用方。大多数业务逻辑将位于存储过程上。这有助于简化客户端代码并会加快执行速度，因为在创建存储过程时就已经准备好了存储过程中包含的 SQL 语句，并对这些语句进行了优化。
Informix JDBC 驱动程序提供了可用于执行存储过程的 Statement、PreparedStatement 和 CallableStatement 方法。使用哪种方法取决于存储过程的特性。例如，如果存储过程返回一个值，那么应该使用 JDBC Statement 对象。下表提供了哪个存储过程类型使用哪个方法的一些指南。
表 1. 显示基于各个存储过程类型要使用哪个 JDBC 方法的表
| 存储过程类型 | JDBC 方法 |
|---|---|
存储过程不需要 IN 或 OUT 参数 | 使用 Statement 对象 |
存储过程具有 IN 参数 | 使用 PreparedStatement 对象 |
存储过程具有 IN 和 OUT 参数 | 使用 CallableStatement 对象 |
我们将提供一个在以下情况下使用 Informix JDBC 方法执行存储过程的示例：
- 使用没有参数的
Statement - 使用具有输入参数的
PreparedStatement - 使用具有输出参数的
CallableStatement CallableStatement中的已命名参数- 过载的存储过程
上面提到的每个主题将包含以下细节：
- 在 Informix JDBC 驱动程序过程中调用存储过程所使用的语法
- 数据库上的存储过程的架构
- 具有输出的 JDBC 驱动程序示例程序
清单 1 中显示了执行没有 IN 或 OUT 参数的存储过程所使用的语法。
清单 1. 执行没有
IN 或 OUT 参数的存储过程所使用的语法
{call procedure-name} |
对于这种类型的存储过程，不需要使用 CallableStatement 对象；可以使用一个简单的 JDBC 语句。清单 2 中的代码显示了使用 Informix SQL 的存储过程的定义。以下示例 GetCustName 返回了一个结果集，其中包含一列数据，这些数据是客户表中前 5 个联系人的名字和姓氏。
清单 2. 使用没有参数的
Statement 对象的存储过程的定义
CREATE PROCEDURE GETCUSTNAME() RETURNING LVARCHAR AS NAME;
DEFINE W_NAME LVARCHAR;
FOREACH
SELECT FIRST 5 FNAME || ' ' || LNAME INTO W_NAME
FROM CUSTOMER
RETURN W_NAME WITH RESUME;
END FOREACH;
END PROCEDURE;
|
请注意：该存储过程示例（以及本文中的所有其他示例）都使用了演示数据库 stores_demo 中创建的表，这是在 Informix IDS 安装期间可以选择的一个选项。
用于执行存储过程的 JDBC Java 代码如清单 3 中所示。
清单 3. 使用没有参数的
Statement 对象的 JDBC 代码
public static void executeStoredprocNoParams(Connection con) {
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call GETCUSTNAME}");
while (rs.next()) {
System.out.println(rs.getString("Name"));
}
rs.close();
stmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
|
由于存储过程返回了一个结果集，因此我们必须使用 Statement.executeQuery() 方法，并通过使用 ResultSet.next() 从结果中获取它。清单 4 显示了以下程序的输出。
清单 4. 清单 3 的程序输出
$java sample_Stored procedure_1
Ludwig Pauli
Carole Sadler
Philip Currie
Anthony Higgins
Raymond Vector
George Watson
$ |
这是最常用的存储过程。存储过程需要使用参数将数据传递到存储过程中，以便进行内部处理。您应该使用 PreparedStatement 来处理这种类型的存储过程。语法如清单 5 中所示。
清单 5. 执行使用具有输入参数的准备语句的存储过程所使用的语法
{call procedure-name(?,?,...)} |
尽管您可以使用 Informix SQL 语法来执行存储过程（例如，执行过程 procedure_name(?,?)），但建议坚持使用 SQL 转义序列语法。
问号字符 (?) 对应于存储过程所需的每个输入参数。它充当传递给存储过程的值的占位符。
若要指定参数的值，您可以使用 IfxPreparedStatement 类的 setter 方法之一。（例如，pstmt.setInt()）。您可以使用的 setter 方法取决于 IN 参数的数据类型。除了参数的值之外，setter 方法还将获取 SQL 语句中参数的位置。
清单 6. 显示具有签名的存储过程的示例
UPDMANU(INT MN_CODE, CHAR(10) MN_NAME, DATE MN_UPD); |
清单 7. 应该在清单 6 中显示的示例中使用的 Java 代码片段
pstmt = con.prepareStatement("{call updmanu(?,?,?)}");
pstmt.setInt(1, manu_id);
pstmt.setString(2, manu_code);
pstmt.setDate(3, manu_date); |
清单 8 显示了以下存储过程，可以用它来演示如何使用 IN 参数。
清单 8. 演示如何使用
IN 参数的存储过程示例
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT AS ORDER_NUM,
DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
DEFINE W_ORDERN INT;
DEFINE W_ORDERD DATE;
DEFINE W_SHIP LVARCHAR;
FOREACH
SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT
INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID
RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
END FOREACH;
END PROCEDURE;
|
该存储过程接受一个名为 CUS_ID 的输入参数，该参数是一个整数值，它会返回该 customer_id 的订单列表。用于调用该存储过程的 Java 代码如清单 9 中所示。
清单 9. 使用具有输入参数的准备语句执行存储过程的 Java 代码
public static void executeSprocInParams(Connection con, int c_id) {
try {
PreparedStatement pstmt = con.prepareStatement("{call getorders(?)}");
pstmt.setInt(1, c_id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("Order Number\t: "+rs.getString("order_num"));
System.out.println("Order Date\t: "+rs.getString("order_date"));
System.out.println("Instructions\t: "+rs.getString("shinstruc"));
System.out.println();
}
rs.close();
pstmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
|
通过执行 executeSprocInParams() 方法，我们可以看到清单 10 中所示的输出。
清单 10. 清单 9 中所示的程序的输出
$java sample_Stored procedure_2
Order Number : 1001
Order Date : 2008-05-20
Instructions : express
Order Number : 1003
Order Date : 2008-05-22
Instructions : express
Order Number : 1011
Order Date : 2008-06-18
Instructions : express
Order Number : 1013
Order Date : 2008-06-22
Instructions : express
$
|
如果存储过程需要使用 IN 或 OUT 参数，那么您需要使用 JDBC CallableStatement 来处理这些参数。只有 IfxCallableStatement 类（该类是从 Java CallableStatement 扩展而来） 能处理 IN 和 OUT 参数。
接下来，我们将演示如何调用返回一个或多个 OUT 参数的存储过程。存储过程会使用这些参数将数据以单个值的形式（而不是我们之前看到的结果集）返回给调用应用程序。用于 IN/OUT 存储过程的 SQL 语法与我们之前在 清单 5 中所示的语法类似。
清单 11. 执行使用没有输出参数的可调用语句的存储过程所使用的语法
{call procedure-name(?,?,...)}
|
您必须遵循参数（IN 和 OUT）的正确顺序。OUT 参数的值必须使用 CallableStatement 类的 registerOutParameter() 方法注册。必须按照正确的顺序指定每个 OUT 参数。与 IN 参数一样，该方法的第一个参数是参数的序数（或位置），例如：cstmt.registerOutParameter(2, Types.INTEGER);。
您为 registerOutParameter 方法中的 OUT 参数指定的值必须是 java.sql.Types 中包含的 Informix JDBC 数据类型之一，该值在内部转换为本机 IDS 数据类型之一。
对于该示例，我们将使用以下存储过程，该过程使用 stores_demo 数据库中的 “items” 表，如清单 12 所示。
清单 12. 用于演示如何使用 OUT 参数的存储过程
CREATE PROCEDURE GETTOTAL(ORDER_ID INT, OUT TOTALPRICE MONEY); LET TOTALPRICE=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE ORDER_NUM=ORDER_ID); END PROCEDURE; |
该存储过程返回一个 OUT 参数 (TotalPrice)，该参数是一个整数，基于指定的 IN 参数 (Order_ID)，后者也是一个整数。OUT 参数中返回的值为 items 表中包含的特定订单号的所有项的总和。
清单 13. 执行使用具有输出参数的可调用语句的存储过程的 Java 代码
public static void executeStoredProcOUTParams(Connection con,int o_id) {
try {
CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}");
cstmt.setInt(1, o_id);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.execute();
System.out.println("Total price for order" + o_id +"is $"+cstmt.getInt(2));
}
catch (Exception e) {
e.printStackTrace();
}
}
|
在上一个示例中，我们使用位置来标识存储过程中的每个参数。您可以通过名称来标识参数，从而使得应用程序代码更清晰、更易读。
以下示例演示了如何在 Java 应用程序中使用已命名的参数。请注意，参数名称与存储过程的定义中的参数名称是相对应的。
清单 14. 如何在 Java 应用程序中使用已命名的参数
public static void executeStoredProcOUTParams(Connection con,int o_id) {
try {
CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}");
cstmt.setInt("Order_ID", o_id);
cstmt.registerOutParameter("TotalPrice", Types.INTEGER);
cstmt.execute();
System.out.println("Total price for order"+ o_id +"is $"+cstmt.getInt("TotalPrice"));
}
catch (Exception e) {
e.printStackTrace();
}
}
|
必须按索引或名称来指示参数；不得将两种方法混合使用。这两个 Java 示例生成以下输出，该输出打印了指定订单的总价：
清单 15. 清单 13 和清单 14 中两个 java 示例的输出
$java sample_Stored procedure_3 Total price for order 1002 is $1200 $ |
请注意：这些示例使用 CallableStatement 类的 execute() 方法来运行存储过程。之所以使用该方法是因为存储过程不返回结果集。如果存储过程返回结果集，则应该使用 executeQuery() 方法，如以下示例所示。
清单 16. 用来演示何时使用
executeQuery() 方法的存储过程
CREATE PROCEDURE GETTOTALBYMANU(CODE CHAR(3), OUT TOTAL MONEY)
RETURNING CHAR(3) AS MANU_CODE, CHAR(10) AS MANU_NAME;
DEFINE W_MANU_CODE CHAR(3);
DEFINE W_MANU_NAME CHAR(10);
LET TOTAL=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE MANU_CODE=CODE);
SELECT MANU_CODE,MANU_NAME
INTO W_MANU_CODE,W_MANU_NAME FROM MANUFACT WHERE MANU_CODE=CODE;
RETURN W_MANU_CODE,W_MANU_NAME;
END PROCEDURE;
|
清单 17 中的方法使用 executeQuery() 来调用 GetTotalByManu 存储过程。
清单 17. 用于演示
executeQuery() 方法用法的 Java 代码
public static void executeStoredProcOUTParamsResulset(Connection con,String manu_id) {
try {
CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
cstmt.setString(1, manu_id);
cstmt.registerOutParameter(2, Types.CHAR);
ResultSet rs = cstmt.executeQuery();
rs.next();
System.out.println("Total for manufacturer '"+rs.getString(2).trim()+
" ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));
}
catch (Exception e) {
e.printStackTrace();
}
}
|
清单 17 中所示程序的输出如清单 18 中所示。
清单 18. 清单 17 中 Java 示例的程序输出
$java sample_Stored procedure_4 Total for manufacturer 'Hero (HRO)' is $2882 $ |
请注意：如果您不知道存储过程是如何定义的，可以使用 JDBC 元数据例程来获取有关存储过程的信息，如它所接受的参数名称和类型。
以下示例使用 getProcedureColumns() 方法来获取 gettotalbymanu 过程的名称和类型。
清单 19. Java 代码
public static void executeStoredGetOutParams(Connection con,String procname) {
try {
DatabaseMetaData dbmd = con.getMetaData();
ResultSet rs = dbmd.getProcedureColumns("stores7","",procname.toUpperCase(),null);
while (rs.next())
if (rs.getShort("COLUMN_TYPE")==DatabaseMetaData.procedureColumnOut) {
System.out.println("OUT Parame: "+ rs.getString("COLUMN_NAME"));
System.out.println("Type: "+rs.getString("DATA_TYPE") );
}
}
catch (Exception e) {
e.printStackTrace();
}
}
|
或者，您可以使用 CallableStatement.hasOutParameter() 方法检查存储过程是否具有 OUT 参数。如果它是使用 OUT 参数定义的，那么它会返回 TRUE，如清单 20 所示。
清单 20. 示例 Java 代码
CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
if (((IfxCallableStatement) cstmt).hasOutParameter())
System.out.println("Stored procedure has OUT parameters ");
// perform the logic
|
Informix 数据库服务器支持过载的存储过程。您可以拥有名称相同，但根据它接受的参数使用不同参数（或签名）来执行不同操作的存储过程。基本示例可为 清单 21 和 清单 22 中所示的两个过程。
清单 21. 第一个存储过程定义
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
DEFINE W_ORDERN INT;
DEFINE W_ORDERD DATE;
DEFINE W_SHIP LVARCHAR;
FOREACH
SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT
INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID
RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
END FOREACH;
END PROCEDURE;
|
清单 22. 第二个存储过程定义
CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
DEFINE W_ORDERN INT;
DEFINE W_ORDERD DATE;
DEFINE W_SHIP LVARCHAR;
FOREACH
SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT
INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.ORDER_DATE=ORD_DATE
RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
END FOREACH;
END PROCEDURE;
|
这两个定义具有相同的名称 (GETORDERS)，但第一个使用 INT 参数来获取特定客户的订单，第二个让 DATE 参数返回特定日期的订单，如清单 23 所示。
清单 23. 多个签名的示例
CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
|
若要从 JDBC 应用程序中执行这些存储过程，您必须在 SQL 语法中提供参数类型，以便 Informix 引擎知道您要运行哪个存储过程。在占位符中使用 ::datatype 前缀，如清单 24 所示。
清单 24. 占位符的示例
{call getorders(?::INT)}
{call getorders(?::DATE)}
|
清单 25 显示如何执行 GETORDERS(DATE) 过程。
清单 25. 演示如何使用具有多个签名的存储过程的 Java 代码
public static void executeSprocInParams_date(Connection con, String o_date) {
try {
PreparedStatement pstmt = con.prepareStatement("{call getorders(?::DATE)}");
pstmt.setString(1, o_date);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("Order Number\t: "+rs.getString("order_num"));
System.out.println("Order Date\t: "+rs.getString("order_date"));
System.out.println("Instructions\t: "+rs.getString("shinstruc"));
System.out.println();
}
rs.close();
pstmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
|
本文介绍了从 JDBC 应用程序访问简单的和复杂的存储过程的各种方法。参考资料 部分提供了指向服务器和 JDBC 文档的链接，这些链接有助于您探索代码示例，帮助您构建高效的存储过程和等效的 JDBC 应用程序。从本文中获得的了解应该可以帮助您将复杂的业务逻辑转换为存储过程，并从您的 JDBC 应用程序中将其导出。
学习
- "Informix JDBC 驱动程序指南" 是有关学习 JDBC 驱动程序 API 的分步指南。
-
了解有关 Informix JDBC 和 IDS 数据类型的信息。
- 在 Informix 11.70.Information 中心，获取提高您的 Informix 技巧所需的参考资料。
- 阅读 IBM Redbooks 中的“IBM Informix 开发人员手册”，快速了解 IBM Informix 产品知识。
- 在 developerWorks 中国网站 Information Management 专区了解有关信息管理的详细信息。查找技术文档、操作方法文章、教育、下载、产品信息和其他更多内容。
- 随时关注 developerWorks 技术活动 和 网络广播。
- 关注 Twitter 上的 developerWorks。
获得产品和技术
- 利用 评估 IBM 产品试用版软件（可直接从 developerWorks 下载）构建您的下一个开发项目。
- 现在您可以免费使用 DB2。下载 IBM 软件下载：IBM DB2 Express-C 10.1，它是针对社区的 DB2 Express Edition 的免费版本，它与 DB2 Express Edition 提供相同的核心数据功能，并为构建和部署应用程序提供了坚实的基础。
讨论
- 查阅 developerWorks 博客 并加入 developerWorks 中文社区。
