使用存储过程

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

请注意，大多数 DBMS 都支持存储过程，但是它们的语法和功能存在很大的差异。因此，本教程包含两个类StoredProcedureJavaDBSample和StoredProcedureMySQLSample，以演示如何分别在 Java DB 和 MySQL 中创建存储过程。

此页面包含以下主题：

存储过程概述示例

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

  • SHOW_SUPPLIERS：打印结果集，其中包含咖啡供应商的名称以及他们向 The Coffee Break 提供的咖啡。此存储过程不需要任何参数。当示例调用此存储过程时，该示例将产生类似于以下内容的输出：
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

Parameter Modes

参数属性IN(默认值)，OUT和INOUT是参数 Pattern。它们定义形式参数的作用。下表总结了有关参数 Pattern 的信息。

参数 Pattern 的 FeatureINOUTINOUT
必须在存储过程定义中指定它吗？没有;如果省略，则形式参数的参数 Pattern 为IN。必须指定。必须指定。
参数是将值传递给存储过程还是返回值？将值传递给存储过程。返回值给调用者。都;将初始值传递给存储过程；将更新的值返回给调用方。
形式参数在存储过程中是充当常量还是变量？形式参数的作用类似于常量。形式参数的作用类似于未初始化的变量。形式参数的作用类似于初始化变量。
可以在存储过程中为形式参数分配一个值吗？无法为形式参数分配值。形式参数不能在表达式中使用；必须分配一个值。形式参数必须分配一个值。
哪些类型的实际参数(参数)可以传递给存储过程？实际参数可以是常量，初始化变量，Literals 或表达式。实际参数必须是变量。实际参数必须是变量。

在 Java DB 中创建存储过程

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

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

创建公共静态 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对象；如果这样做，则在调用存储过程时发出CALL语句时，ResultSet对象将不存在。

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

以下方法是StoredProcedureJavaDBSample.showSuppliers：

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的参数 Pattern 为IN。像 Java 方法中的任何其他参数一样，使用此形式参数。由于形式参数supplierName具有参数 PatternOUT，因此它必须使用一维数组数据类型。因为此方法不会产生结果集，所以方法定义不包含ResultSet[]类型的参数。为了从OUT形式参数检索值，必须将要检索的值分配给OUT形式参数的数组组件。在此示例中，检索到的咖啡供应商名称被分配给数组组件supplierName[0]。

以下是StoredProcedureJavaDBSample.raisePrice method:的方法签名

public static void raisePrice(
   String coffeeName, double maximumPercentage,
   BigDecimal[] newPrice) throws SQLException

因为形式参数newPrice具有参数 PatternINOUT，所以它必须使用一维数组数据类型。 Java DB 分别将FLOAT和NUMERIC SQL 数据类型 Map 到double和java.math.BigDecimal Java 数据类型。

使用 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("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 必须能够在 Classpath 或直接添加到数据库的 JAR 文件中找到此处指定的方法。请参阅以下步骤在 JAR 文件中打包 Java 类。

以下语句(在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称为参数 Pattern。它们定义形式参数的作用。有关更多信息，请参见Parameter Modes。此存储过程不检索结果集，因此过程元素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 中创建存储过程。请参见build.xml Ant 构建脚本中的脚本javadb/create-procedures.sql和 Ant 目标javadb-create-procedure。

将 Java 类打包在 JAR 文件中

Ant 构建脚本build.xml包含用于将教程编译和打包为 JAR 文件的目标。在命令提示符下，将当前目录更改为<JDBC tutorial directory>。在此目录中，运行以下命令以将教程编译并打包到一个 JAR 文件中：

ant jar

JAR 文件的名称为<JDBC tutorial directory>/lib/JDBCTutorial.jar。

Ant 构建脚本将文件JDBCTutorial.jar添加到 Classpath。您还可以在CLASSPATH环境变量中指定 JAR 文件的位置。这使 Java DB 可以找到存储过程调用的 Java 方法。

直接将 JAR 文件添加到数据库

Java DB 首先在 Classpath 中查找任何必需的类，然后在数据库中查找。本节说明如何将 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，以便将属性jar_file的值设置为JDBCTutorial.jar的完整路径名。

SQLPattern 中的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 首先在 JavaClasspath 中查找类，然后在derby.database.classpath中查找。

在 MySQL 中创建存储过程

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

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

MySQL 对其存储过程使用基于 SQL 的语法。以下 SQL 脚本mysql/create-procedures.sql的摘录创建了一个名为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(为清楚起见已插入换行符)：

<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 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的以下摘录包含创建名为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称为参数 Pattern。它们定义形式参数的作用。有关更多信息，请参见Parameter Modes。形式参数是在 SQL 查询中定义的，而不是在createProcedureGetSupplierOfCoffee方法中定义的。要将值分配给OUT参数supplierName，此存储过程使用SELECT语句。

方法StoredProcedureMySQLSample.createProcedureRaisePrice的以下摘录包含创建名为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。

在 Java DB 和 MySQL 中调用存储过程

下面的方法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。

在 MySQL 中使用 JDBC API 创建存储过程部分中演示了如何调用存储过程SHOW_SUPPLIERS。

以下方法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);

interfaceCallableStatement扩展了PreparedStatement。它用于调用存储过程。指定IN参数的值(例如本例中的coffeeName)，就像通过调用适当的 setter 方法使用PreparedStatement对象一样。但是，如果存储过程包含OUT参数，则必须使用registerOutParameter方法注册它。

以下来自方法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中的第三个参数)具有参数 PatternINOUT，所以您必须都通过调用适当的 setter 方法来指定其值，并使用registerOutParameter方法进行注册。

Name Java8 中文教程
Version
Language
Badge
Last Updated 2021-07-05T12:23:09