Skip to content

Latest commit

 

History

History
536 lines (371 loc) · 19.6 KB

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

File metadata and controls

536 lines (371 loc) · 19.6 KB

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

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

标签: 数据库

原文链接

Javier Sagrera, Babita Sonavane

发布: 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 驱动程序提供了可用于执行存储过程的 Statement 、 PreparedStatement 和 CallableStatement 方法。使用哪种方法取决于存储过程的特性。例如，如果存储过程返回一个值，那么应该使用 JDBC Statement 对象。下表提供了哪个存储过程类型使用哪个方法的一些指南。

显示基于各个存储过程类型要使用哪个 JDBC 方法的表

存储过程类型JDBC 方法存储过程不需要 IN 或 OUT 参数使用 Statement 对象存储过程具有 IN 参数使用 PreparedStatement 对象存储过程具有 IN 和 OUT 参数使用 CallableStatement 对象

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

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

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

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

使用没有参数的 Statement 对象

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

执行没有 IN 或 OUT 参数的存储过程所使用的语法
{call procedure-name}

Show moreShow more icon

对于这种类型的存储过程，不需要使用 CallableStatement 对象；可以使用一个简单的 JDBC 语句。清单 2 中的代码显示了使用 Informix SQL 的存储过程的定义。以下示例 GetCustName 返回了一个结果集，其中包含一列数据，这些数据是客户表中前 5 个联系人的名字和姓氏。

使用没有参数的 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;

Show moreShow more icon

请注意： 该存储过程示例（以及本文中的所有其他示例）都使用了演示数据库 stores_demo 中创建的表，这是在 Informix IDS 安装期间可以选择的一个选项。

用于执行存储过程的 JDBC Java 代码如清单 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();
            }
}

Show moreShow more icon

由于存储过程返回了一个结果集，因此我们必须使用 Statement.executeQuery() 方法，并通过使用 ResultSet.next() 从结果中获取它。清单 4 显示了以下程序的输出。

清单 3 的程序输出
$java sample_Stored procedure_1

Ludwig          Pauli
Carole          Sadler
Philip          Currie
Anthony         Higgins
Raymond         Vector
George          Watson

$

Show moreShow more icon

使用具有输入参数的 PreparedStatement

这是最常用的存储过程。存储过程需要使用参数将数据传递到存储过程中，以便进行内部处理。您应该使用 PreparedStatement 来处理这种类型的存储过程。语法如清单 5 中所示。

执行使用具有输入参数的准备语句的存储过程所使用的语法
{call procedure-name(?,?,...)}

Show moreShow more icon

尽管您可以使用 Informix SQL 语法来执行存储过程（例如，执行过程 procedure_name(?,?) ），但建议坚持使用 SQL 转义序列语法。

问号字符 (?) 对应于存储过程所需的每个输入参数。它充当传递给存储过程的值的占位符。

若要指定参数的值，您可以使用 IfxPreparedStatement 类的 setter 方法之一。（例如， pstmt.setInt() ）。您可以使用的 setter 方法取决于 IN 参数的数据类型。除了参数的值之外，setter 方法还将获取 SQL 语句中参数的位置。

显示具有签名的存储过程的示例
UPDMANU(INT MN_CODE, CHAR(10) MN_NAME, DATE MN_UPD);

Show moreShow more icon

应该在清单 6 中显示的示例中使用的 Java 代码片段
pstmt = con.prepareStatement("{call updmanu(?,?,?)}");
pstmt.setInt(1, manu_id);
pstmt.setString(2, manu_code);
pstmt.setDate(3, manu_date);

Show moreShow more icon

清单 8 显示了以下存储过程，可以用它来演示如何使用 IN 参数。

演示如何使用 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;

Show moreShow more icon

该存储过程接受一个名为 CUS_ID 的输入参数，该参数是一个整数值，它会返回该 customer_id 的订单列表。用于调用该存储过程的 Java 代码如清单 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();
    }
}

Show moreShow more icon

通过执行 executeSprocInParams() 方法，我们可以看到清单 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
$

Show moreShow more icon

使用具有输出参数的 CallableStatement

如果存储过程需要使用 IN 或 OUT 参数，那么您需要使用 JDBC CallableStatement 来处理这些参数。只有 IfxCallableStatement 类（该类是从 Java CallableStatement 扩展而来） 能处理 IN 和 OUT 参数。

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

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

Show moreShow more icon

您必须遵循参数（ IN 和 OUT ）的正确顺序。 OUT 参数的值必须使用 CallableStatement 类的 registerOutParameter() 方法注册。必须按照正确的顺序指定每个 OUT 参数。与 IN 参数一样，该方法的第一个参数是参数的序数（或位置），例如： cstmt.registerOutParameter(2, Types.INTEGER); 。

您为 registerOutParameter 方法中的 OUT 参数指定的值必须是 java.sql.Types 中包含的 Informix JDBC 数据类型之一，该值在内部转换为本机 IDS 数据类型之一。

对于该示例，我们将使用以下存储过程，该过程使用 stores_demo 数据库中的 “items” 表，如清单 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;

Show moreShow more icon

该存储过程返回一个 OUT 参数 (TotalPrice)，该参数是一个整数，基于指定的 IN 参数 (Order_ID)，后者也是一个整数。 OUT 参数中返回的值为 items 表中包含的特定订单号的所有项的总和。

执行使用具有输出参数的可调用语句的存储过程的 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();
    }
}

Show moreShow more icon

CallableStatement 中已命名参数

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

以下示例演示了如何在 Java 应用程序中使用已命名的参数。请注意，参数名称与存储过程的定义中的参数名称是相对应的。

如何在 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();
    }
}

Show moreShow more icon

必须按索引或名称来指示参数；不得将两种方法混合使用。这两个 Java 示例生成以下输出，该输出打印了指定订单的总价：

清单 13 和清单 14 中两个 java 示例的输出
$java sample_Stored procedure_3
Total price for order 1002 is $1200
$

Show moreShow more icon

请注意： 这些示例使用 CallableStatement 类的 execute() 方法来运行存储过程。之所以使用该方法是因为存储过程不返回结果集。如果存储过程返回结果集，则应该使用 executeQuery() 方法，如以下示例所示。

用来演示何时使用 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;

Show moreShow more icon

清单 17 中的方法使用 executeQuery() 来调用 GetTotalByManu 存储过程。

用于演示 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();
    }
}

Show moreShow more icon

清单 17 中所示程序的输出如清单 18 中所示。

清单 17 中 Java 示例的程序输出
$java sample_Stored procedure_4
Total for manufacturer 'Hero (HRO)' is $2882
$

Show moreShow more icon

请注意： 如果您不知道存储过程是如何定义的，可以使用 JDBC 元数据例程来获取有关存储过程的信息，如它所接受的参数名称和类型。

以下示例使用 getProcedureColumns() 方法来获取 gettotalbymanu 过程的名称和类型。

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();
    }
}

Show moreShow more icon

或者，您可以使用 CallableStatement.hasOutParameter() 方法检查存储过程是否具有 OUT 参数。如果它是使用 OUT 参数定义的，那么它会返回 TRUE，如清单 20 所示。

示例 Java 代码
CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
if (((IfxCallableStatement) cstmt).hasOutParameter())
System.out.println("Stored procedure has OUT parameters ");
// perform the logic

Show moreShow more icon

调用具有多个签名的存储过程

Informix 数据库服务器支持过载的存储过程。您可以拥有名称相同，但根据它接受的参数使用不同参数（或签名）来执行不同操作的存储过程。基本示例可为 第一个存储过程定义 和 第二个存储过程定义 中所示的两个过程。

第一个存储过程定义
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;

Show moreShow more icon

第二个存储过程定义
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;

Show moreShow more icon

这两个定义具有相同的名称 (GETORDERS)，但第一个使用 INT 参数来获取特定客户的订单，第二个让 DATE 参数返回特定日期的订单，如清单 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;

Show moreShow more icon

若要从 JDBC 应用程序中执行这些存储过程，您必须在 SQL 语法中提供参数类型，以便 Informix 引擎知道您要运行哪个存储过程。在占位符中使用 ::datatype 前缀，如清单 24 所示。

占位符的示例
{call getorders(?::INT)}
{call getorders(?::DATE)}

Show moreShow more icon

清单 25 显示如何执行 GETORDERS(DATE) 过程。

演示如何使用具有多个签名的存储过程的 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();
    }
}

Show moreShow more icon

结束语

本文介绍了从 JDBC 应用程序访问简单的和复杂的存储过程的各种方法。 参考资料 部分提供了指向服务器和 JDBC 文档的链接，这些链接有助于您探索代码示例，帮助您构建高效的存储过程和等效的 JDBC 应用程序。从本文中获得的了解应该可以帮助您将复杂的业务逻辑转换为存储过程，并从您的 JDBC 应用程序中将其导出。

本文翻译自： Stored procedures used in callable and prepared statements（2012-10-29）