准备好的可调用语句中使用的存储过程

探索对 Informix JDBC 驱动程序使用可调用语句的存储过程的执行

为了提高关系数据库应用程序的性能,通常会使用存储过程,存储过程只不过是用户定义的功能,其中包含存储在数据库中的一系列 SQL 语句。在本文中,我们将演示如何通过对 Informix® JDBC 驱动程序使用可调用语句来访问存储过程。JDBC CallableStatement 扩展了 PreparedStatement,并向服务器发送请求,以执行用于复杂操作(比如返回值或使用输入和输出存储过程参数)的存储过程。

Babita Sonavane, 软件开发人员, IBM

Babita SonavaneBabita Sonavane 是位于班加罗尔的 IBM 印度实验室的一名软件开发人员。Babita 有 5 年多的 IT 从业经验。她积极维护和开发 Informix JDBC 和 DB2 JDBC 驱动程序中的各种功能。



Javier Sagrera, 软件工程师, IBM

作者 Javier Sagrera 的照片Javier Sagrera 是 Common Client Technologies (CCT) 小组的一名软件工程师。他于 2000 年加入 Informix 团队,在 Informix 数据库服务器和 Informix 客户端应用程序开发方面有 10 多年的丰富经验。他目前在伦敦的 IBM UK Bedfont Lab 工作,拥有所有 Microsoft® 技术的丰富知识,被公认为是所有 Informix 开发工具方面的全球主题专家。



2012 年 10 月 29 日

免费下载: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 语句,并对这些语句进行了优化。


在 JDBC 应用程序中调用存储过程

Informix JDBC 驱动程序提供了可用于执行存储过程的 StatementPreparedStatementCallableStatement 方法。使用哪种方法取决于存储过程的特性。例如,如果存储过程返回一个值,那么应该使用 JDBC Statement 对象。下表提供了哪个存储过程类型使用哪个方法的一些指南。

表 1. 显示基于各个存储过程类型要使用哪个 JDBC 方法的表
存储过程类型JDBC 方法
存储过程不需要 INOUT 参数使用 Statement 对象
存储过程具有 IN 参数使用 PreparedStatement 对象
存储过程具有 INOUT 参数使用 CallableStatement 对象

我们将提供一个在以下情况下使用 Informix JDBC 方法执行存储过程的示例:

  • 使用没有参数的 Statement
  • 使用具有输入参数的 PreparedStatement
  • 使用具有输出参数的 CallableStatement
  • CallableStatement 中的已命名参数
  • 过载的存储过程

上面提到的每个主题将包含以下细节:

  • 在 Informix JDBC 驱动程序过程中调用存储过程所使用的语法
  • 数据库上的存储过程的架构
  • 具有输出的 JDBC 驱动程序示例程序

使用没有参数的 Statement 对象

清单 1 中显示了执行没有 INOUT 参数的存储过程所使用的语法。

清单 1. 执行没有 INOUT 参数的存储过程所使用的语法
{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

这是最常用的存储过程。存储过程需要使用参数将数据传递到存储过程中,以便进行内部处理。您应该使用 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
$

使用具有输出参数的 CallableStatement

如果存储过程需要使用 INOUT 参数,那么您需要使用 JDBC CallableStatement 来处理这些参数。只有 IfxCallableStatement 类(该类是从 Java CallableStatement 扩展而来) 能处理 INOUT 参数。

接下来,我们将演示如何调用返回一个或多个 OUT 参数的存储过程。存储过程会使用这些参数将数据以单个值的形式(而不是我们之前看到的结果集)返回给调用应用程序。用于 IN/OUT 存储过程的 SQL 语法与我们之前在 清单 5 中所示的语法类似。

清单 11. 执行使用没有输出参数的可调用语句的存储过程所使用的语法
{call procedure-name(?,?,...)}

您必须遵循参数(INOUT)的正确顺序。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();
    }
}

CallableStatement 中已命名参数

在上一个示例中,我们使用位置来标识存储过程中的每个参数。您可以通过名称来标识参数,从而使得应用程序代码更清晰、更易读。

以下示例演示了如何在 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 应用程序中将其导出。

参考资料

学习

获得产品和技术

  • 利用 评估 IBM 产品试用版软件(可直接从 developerWorks 下载)构建您的下一个开发项目。
  • 现在您可以免费使用 DB2。下载 IBM 软件下载:IBM DB2 Express-C 10.1,它是针对社区的 DB2 Express Edition 的免费版本,它与 DB2 Express Edition 提供相同的核心数据功能,并为构建和部署应用程序提供了坚实的基础。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=843045
ArticleTitle=准备好的可调用语句中使用的存储过程
publish-date=10292012