文档

Java™ 教程
隐藏目录
JDBC基础
入门
使用JDBC处理SQL语句
建立连接
使用DataSource对象连接
处理SQLExceptions
设置表
从结果集中检索和修改值
使用预处理语句
使用事务
使用RowSet对象
使用JdbcRowSet对象
使用CachedRowSet对象
使用JoinRowSet对象
使用FilteredRowSet对象
使用WebRowSet对象
使用高级数据类型
使用大对象
使用SQLXML对象
使用数组对象
使用DISTINCT数据类型
使用结构化对象
使用自定义类型映射
使用Datalink对象
使用RowId对象
使用存储过程
使用JDBC和GUI API
路径： JDBC数据库访问
课程： JDBC基础知识
« 上一页 • 路径 • 下一页 »

使用存储过程

存储过程是一组SQL语句，它们形成一个逻辑单元并执行特定任务，用于封装要在数据库服务器上执行的一组操作或查询。例如，对员工数据库的操作（招聘、解雇、晋升、查找）可以编写为由应用程序代码执行的存储过程。存储过程可以编译并以不同的参数和结果执行，并且它们可以具有任何组合的输入、输出和输入/输出参数。

请注意，大多数数据库管理系统都支持存储过程，但在语法和功能上有很多变化。因此，本教程包含两个示例，StoredProcedureJavaDBSample.java 和 StoredProcedureMySQLSample.java，分别演示了如何在Java DB和MySQL中创建存储过程。

本页面包括以下主题：

存储过程示例概述

示例 StoredProcedureJavaDBSample.java 和 StoredProcedureMySQLSample.java 创建并调用以下存储过程：

参数模式

参数属性IN（默认值），OUT和INOUT是参数模式。它们定义了形式参数的操作。下表总结了有关参数模式的信息。

参数模式的特点 IN OUT INOUT

是否必须在存储过程定义中指定？

否；如果省略，则形式参数的参数模式为IN。

必须指定。

必须指定。

参数是向存储过程传递值还是返回值？

向存储过程传递值。

向调用者返回值。

既传递初始值给存储过程，又将更新后的值返回给调用者。

形式参数在存储过程中是作为常量还是变量？

形式参数作为常量。

形式参数作为未初始化的变量。

形式参数作为已初始化的变量。

形式参数在存储过程中可以被赋值吗？

形式参数不能被赋值。

形式参数不能在表达式中使用；必须被赋值。

形式参数必须被赋值。

可以传递哪些实际参数（参数）给存储过程？

实际参数可以是常量、已初始化的变量、文字或表达式。

实际参数必须是变量。

实际参数必须是变量。

在Java DB中创建存储过程

注意：有关在Java DB中创建存储过程的更多信息，请参阅《Java DB参考手册》中的"CREATE PROCEDURE语句"部分。

在Java DB中创建和使用存储过程涉及以下步骤：

  1. 在Java类中创建一个公共的静态Java方法：该方法执行存储过程所需的任务。
  2. 创建存储过程：该存储过程调用您创建的Java方法。
  3. 调用存储过程
  4. 将包含之前创建的公共静态Java方法的Java类打包到JAR文件中。

创建公共静态Java方法

下面的方法StoredProcedureJavaDBSample.showSuppliers包含了存储过程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对象；如果这样做，当您调用存储过程时，ResultSet对象将不存在。

为了使存储过程返回一个生成的结果集，您必须将结果集分配给ResultSet[]参数的一个数组组件。在这个例子中，生成的结果集被分配给了数组组件rs[0]。

使用SQL脚本或JDBC API在Java DB中创建存储过程

Java DB使用Java编程语言来编写存储过程。因此，当您定义一个存储过程时，您需要指定要调用的Java类以及Java DB可以找到它的位置。

以下摘自StoredProcedureJavaDBSample.createProcedures的代码创建了一个名为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("调用 CREATE PROCEDURE");
        stmtCreateShowSuppliers = con.createStatement();

        // ...

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmtCreateShowSuppliers != null) {
            stmtCreateShowSuppliers.close();
        }
        // ...
    }
}

下面的列表描述了在 CREATE PROCEDURE 语句中可以指定的过程元素：

下面的语句（在 StoredProcedureJavaDBSample.createProcedures 中找到）创建了一个名为 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中的存储过程。参见脚本javadb/create-procedures.sql和Ant目标javadb-create-procedure在build.xml Ant构建脚本中。

在Java DB中调用存储过程

以下来自方法StoredProcedureJavaDBSample.runStoredProcedures的代码片段调用存储过程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。

以下来自方法StoredProcedureJavaDBSample.runStoredProcedures的代码片段调用存储过程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方法注册它。

以下是方法StoredProcedureJavaDBSample.runStoredProcedures的一部分代码，它调用了存储过程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方法进行注册。

将Java类打包成JAR文件

Ant构建脚本build.xml包含了编译和将教程打包成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教程目录>/
  lib/JDBCTutorial.jar',
  'APP.JDBCTutorial', 0)
CALL sqlj.replace_jar(
  '<JDBC教程目录>/
  lib/JDBCTutorial.jar',
  'APP.JDBCTutorial')";
CALL syscs_util.syscs_set_database_property(
  'derby.database.classpath',
  'APP.JDBCTutorial')";

注意：方法StoredProcedureJavaDBSample.registerJarFile演示了如何调用这些系统过程。如果你调用了该方法，请确保已修改javadb-sample-properties.xml，将属性jar_file的值设置为JDBCTutorial.jar的完整路径名。

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中查找。

在MySQL中创建存储过程

在Java DB中创建和使用存储过程涉及以下步骤：

  1. 使用SQL脚本或JDBC API创建存储过程。
  2. 使用CALL SQL语句调用存储过程。参见在MySQL中调用存储过程部分。

使用SQL脚本或JDBC API在MySQL中创建存储过程

MySQL使用基于SQL的语法来定义存储过程。以下摘录来自SQL脚本mysql/create-procedures.sql，创建了一个名为SHOW_SUPPLIERS的存储过程：

SELECT '删除存储过程SHOW_SUPPLIERS' AS ' '|
drop procedure if exists SHOW_SUPPLIERS|

# ...

SELECT '创建存储过程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（为了清晰起见，已插入换行符）：

<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执行与前面的脚本相同的任务：

  public void createProcedureShowSuppliers() throws SQLException {
    
    String queryDrop = "DROP PROCEDURE IF EXISTS SHOW_SUPPLIERS";

    String 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";

    try (Statement stmtDrop = con.createStatement()) {
      System.out.println("调用DROP PROCEDURE");
      stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    } 

    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

请注意，此方法中未更改分隔符。

存储过程SHOW_SUPPLIERS生成一个结果集，即使方法createProcedureShowSuppliers的返回类型是void，且该方法不包含任何参数。当使用方法CallableStatement.executeQuery调用存储过程SHOW_SUPPLIERS时，将返回一个结果集：

CallableStatement cs = null;
cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
ResultSet rs = cs.executeQuery();

下面是方法StoredProcedureMySQLSample.createProcedureGetSupplierOfCoffee中的一段代码，该代码包含创建名为GET_SUPPLIER_OF_COFFEE的存储过程的SQL查询：

  public void createProcedureGetSupplierOfCoffee() throws SQLException {

    String queryDrop = "DROP PROCEDURE IF EXISTS GET_SUPPLIER_OF_COFFEE";

    String 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";

    try (Statement stmtDrop = con.createStatement()) {
      System.out.println("调用DROP PROCEDURE");
      stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }

    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

这个存储过程有两个形式参数，coffeeName和supplierName。参数指定符IN和OUT被称为参数模式，它们定义了形式参数的行为。更多信息请参见参数模式。形式参数在SQL查询中定义，而不是在方法createProcedureGetSupplierOfCoffee中定义。为了给OUT参数supplierName赋值，这个存储过程使用了一个SELECT语句。

下面是方法StoredProcedureMySQLSample.createProcedureRaisePrice中的一段代码，其中包含了创建名为RAISE_PRICE的存储过程的SQL查询：

  public void createProcedureRaisePrice() throws SQLException {
    
    String queryDrop = "DROP PROCEDURE IF EXISTS RAISE_PRICE";

    String 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";

    try (Statement stmtDrop = con.createStatement()) {
      System.out.println("调用DROP PROCEDURE");
      stmtDrop.execute(queryDrop);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }

    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate(createProcedure);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

存储过程使用SET和SELECT语句为newPrice参数赋值。为了退出存储过程，首先将语句包含在标记为main的BEGIN ... END块中。使用leave main语句退出过程。

在MySQL中调用存储过程

在MySQL中调用存储过程与在Java DB中调用存储过程相同。

下面是从方法StoredProcedureMySQLSample.runStoredProcedures中的代码片段，调用存储过程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对象一样，调用存储过程时，可以根据过程返回的ResultSet对象的数量调用execute，executeQuery或executeUpdate方法。但是，如果不确定存储过程返回的ResultSet对象的数量，调用execute方法。

下面是从方法StoredProcedureMySQLSample.runStoredProcedures中的代码片段，调用存储过程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方法为IN参数（例如此示例中的coffeeName）指定值，就像使用PreparedStatement对象一样。但是，如果存储过程包含OUT参数，必须使用registerOutParameter方法进行注册。

下面是从方法StoredProcedureMySQLSample.runStoredProcedures中的代码片段，调用存储过程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方法进行注册。

« 上一页 • 目录 • 下一页 »
上一页： 使用 RowId 对象
下一页： 使用带有 GUI API 的 JDBC