Using Stored Procedures使用存储过程

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.存储过程是一组SQL语句，它们构成一个逻辑单元并执行特定任务，用于封装要在数据库服务器上执行的一组操作或查询。For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code.例如，员工数据库上的操作（雇用、解雇、晋升、查找）可以编码为由应用程序代码执行的存储过程。Stored procedures can be compiled and executed with different parameters and results, and they can have any combination of input, output, and input/output parameters.存储过程可以用不同的参数和结果编译和执行，它们可以有输入、输出和输入/输出参数的任意组合。

Note that stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities.请注意，大多数DBMS都支持存储过程，但它们的语法和功能有相当大的差异。Consequently, the tutorial contains two classes, StoredProcedureJavaDBSample and StoredProcedureMySQLSample to demonstrate how to create stored procedures in Java DB and MySQL, respectively.因此，本教程包含两个类：StoredProcedureJavaDBSample和StoredProcedureMySQLSample，分别演示如何在Java DB和MySQL中创建存储过程。

This page covers the following topics:本页涵盖以下主题：

• Overview of Stored Procedures Examples存储过程示例概述
• Parameter Modes参数模式
• Creating Stored Procedures in Java DB在JavaDB中创建存储过程
  • Creating Stored Procedures in Java DB with SQL Scripts or JDBC API使用SQL脚本或JDBCAPI在JavaDB中创建存储过程
  • Creating Stored Procedures in Java DB在JavaDB中创建存储过程
  • Package Java Class in JAR File在JAR文件中打包Java类
• Creating Stored Procedure in MySQL在MySQL中创建存储过程
  • Creating Stored Procedure in MySQL with SQL Scripts or JDBC API使用SQL脚本或JDBCAPI在MySQL中创建存储过程
• Calling Stored Procedures in Java DB and MySQL在JavaDB和MySQL中调用存储过程

Overview of Stored Procedures Examples存储过程示例概述

The examples StoredProcedureJavaDBSample.java and StoredProcedureMySQLSample.java create and call the following stored procedures:示例StoredProcedureJavaDBSample.java和StoredProcedureMySQLSample.java创建并调用以下存储过程：

• SHOW_SUPPLIERS: Prints a result set that contains the names of coffee suppliers and the coffees they supply to The Coffee Break.：打印一个结果集，其中包含咖啡供应商的名称以及他们向咖啡休息时间供应的咖啡。This stored procedure does not require any parameters.此存储过程不需要任何参数。When the example calls this stored procedure, the example produces output similar to the following:当该示例调用此存储过程时，该示例将生成类似以下内容的输出：

  Acme, Inc.: Colombian_Decaf
  Acme, Inc.: Colombian
  Superior Coffee: French_Roast_Decaf
  Superior Coffee: French_Roast
  The High Ground: Espresso

• GET_SUPPLIER_OF_COFFEE: Prints the name of the supplier supplierName for the coffee coffeeName.：打印咖啡coffeeName的供应商supplierName。It requires the following parameters:它需要以下参数：

  • IN coffeeName varchar(32): The name of the coffee：咖啡的名字
  • OUT supplierName varchar(40): The name of the coffee supplier：咖啡供应商的名称

  When the example calls this stored procedure with Colombian as the value for coffeeName, the example produces output similar to the following:当该示例使用Colombian作为coffeeName的值调用此存储过程时，该示例将生成类似以下内容的输出：

  Supplier of the coffee Colombian: Acme, Inc.

• RAISE_PRICE: Raises the price of the coffee coffeeName to the price newPrice.：将咖啡coffeeName的价格提高到价格newPrice。If the price increase is greater than the percentage maximumPercentage, then the price is raised by that percentage.如果价格上涨幅度大于百分比maximumPercentage，则价格上涨幅度为该百分比。This procedure will not change the price if the price newPrice is lower than the original price of the coffee.如果价格newPrice低于咖啡的原价，则此过程不会改变价格。It requires the following parameters:它需要以下参数：

  • IN coffeeName varchar(32): The name of the coffee：咖啡的名字
  • IN maximumPercentage float: The maximum percentage to raise the coffee's price：提高咖啡价格的最大百分比
  • INOUT newPrice numeric(10,2): The new price of the coffee.咖啡的新价格。After the RAISE_PRICE stored procedure has been called, this parameter will contain the current price of the coffee coffeeName.调用RAISE_PRICE存储过程后，此参数将包含咖啡coffeeName的当前价格。

  When the example calls this stored procedure with Colombian as the value for coffeeName, 0.10 as the value for maximumPercentage, and 19.99 as the value for newPrice, the example produces output similar to the following:当示例调用此存储过程时，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参数模式

The parameter attributes IN (the default), OUT, and INOUT are parameter modes.IN（默认）、OUT和INOUT的参数属性是参数模式。They define the action of formal parameters.它们定义了形式参数的作用。The following table summarizes the information about parameter modes.下表总结了有关参数模式的信息。

Creating Stored Procedures in Java DB在JavaDB中创建存储过程

Note注意: See the section "CREATE PROCEDURE statement" in Java DB Reference Manual for more information about creating stored procedures in Java DB.：有关在Java DB中创建存储过程的更多信息，请参阅《Java DB参考手册》中的“创建过程语句”一节。

Creating and using a stored procedure in Java DB involves the following steps:在Java DB中创建和使用存储过程涉及以下步骤：

1. Create a public static Java method in a Java class在Java类中创建公共静态Java方法: This method performs the required task of the stored procedure.：此方法执行存储过程所需的任务。
2. Create the stored procedure创建存储过程: This stored procedure calls the Java method you created.：此存储过程调用您创建的Java方法。
3. Package the Java class (that contains the public static Java method you created earlier) in a JAR file.将Java类（包含前面创建的公共静态Java方法）打包到JAR文件中。
4. Call the stored procedure with the CALL SQL statement.使用Call SQL语句调用存储过程。See the section Calling Stored Procedures in Java DB and MySQL.请参阅在JavaDB和MySQL中调用存储过程一节。

Creating Public Static Java Method创建公共静态Java方法

The following method, StoredProcedureJavaDBSample.showSuppliers, contains the SQL statements that the stored procedure SHOW_SUPPLIERS calls:以下方法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);
}

The SHOW_SUPPLIERS stored procedure takes no arguments. SHOW_SUPPLIERS存储过程不接受任何参数。You can specify arguments in a stored procedure by defining them in the method signature of your public static Java method. 通过在公共静态Java方法的方法签名中定义参数，可以在存储过程中指定参数。Note that the method showSuppliers contains a parameter of type ResultSet[]. 请注意，showSuppliers方法包含ResultSet[]类型的参数。If your stored procedure returns any number of ResultSet objects, specify one parameter of type ResultSet[] in your Java method. 如果存储过程返回任意数量的ResultSet对象，请在Java方法中指定一个ResultSet[]类型的参数。In addition, ensure that this Java method is public and static.此外，请确保此Java方法是公共的和静态的。

Retrieve the Connection object from the URL jdbc:default:connection. 从URL jdbc:default:connection检索Connection对象。This is a convention in Java DB to indicate that the stored procedure will use the currently existing Connection object.这是JavaDB中的一种约定，表示存储过程将使用当前存在的Connection对象。

Note that the Statement object is not closed in this method. 请注意，此方法中的Statement对象不是闭合的。Do not close any Statement objects in the Java method of your stored procedure; if you do so, the ResultSet object will not exist when you issue the CALL statement when you call your stored procedure.不要关闭存储过程的Java方法中的任何Statement对象；如果这样做，则在调用存储过程时发出CALL语句时，ResultSet对象将不存在。

In order for the stored procedure to return a generated result set, you must assign the result set to an array component of the ResultSet[] parameter. 为了使存储过程返回生成的结果集，必须将结果集分配给ResultSet[]参数的数组组件。In this example, the generated result set is assigned to the array component rs[0].在此示例中，生成的结果集被分配给阵列组件rs[0]。

The following method is StoredProcedureJavaDBSample.showSuppliers:以下方法适用于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;
    }
}

The formal parameter coffeeName has the parameter mode IN. 形式参数coffeeName具有参数模式IN。This formal parameter is used like any other parameter in a Java method. 此形式参数与Java方法中的任何其他参数一样使用。Because the formal parameter supplierName has the parameter mode OUT, it must use a one dimensional array data type. 由于形式参数supplierName具有参数模式OUT，因此它必须使用一维数组数据类型。Because this method does not produce a result set, the method definition does not contain a parameter of type ResultSet[]. 由于此方法不生成结果集，因此方法定义不包含ResultSet[]类型的参数。In order to retrieve a value from an OUT formal parameter, you must assign the value to be retrieved to an array component of the OUT formal parameter. 为了从OUT-formal参数中检索值，必须将要检索的值分配给OUT-formal参数的数组组件。In this example, the retrieved name of the coffee supplier is assigned to the array component supplierName[0].在本例中，检索到的咖啡供应商名称被分配给数组组件supplierName[0]。

The following is the method signature of the StoredProcedureJavaDBSample.raisePrice method:以下是StoredProcedureJavaDBSample.raisePrice方法的方法签名：

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

Because the formal parameter newPrice has the parameter mode INOUT, it must use a one dimensional array data type. 由于形式参数newPrice具有参数模式INOUT，因此它必须使用一维数组数据类型。Java DB maps the FLOAT and NUMERIC SQL data types to the double and java.math.BigDecimal Java data types, respectively.Java DB将FLOAT和NUMERIC SQL数据类型分别映射为double和Java数据类型java.math.BigDecimal。

Creating Stored Procedures in Java DB with SQL Scripts or JDBC API使用SQL脚本或JDBCAPI在JavaDB中创建存储过程

Java DB uses the Java programming language for its stored procedures. JavaDB将Java编程语言用于其存储过程。Consequently, when you define a stored procedure, you specify which Java class to call and where Java DB can find it.因此，在定义存储过程时，需要指定调用哪个Java类以及Java DB可以在哪里找到它。

The following excerpt from StoredProcedureJavaDBSample.createProcedures creates a stored procedure named SHOW_SUPPLIERS:以下摘录自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();
        }
        // ...
    }
}

The following list describes the procedure elements you can specify in the CREATE PROCEDURE statement:下表介绍了可以在CREATE PROCEDURE语句中指定的过程元素：

• PARAMETER STYLE: Identifies the convention used to pass parameters to the stored procedure. ：标识用于向存储过程传递参数的约定。The following options are valid:以下选项有效：
  • JAVA: Specifies that the stored procedure uses a parameter-passing convention that conforms to the Java language and the SQL routines specification.：指定存储过程使用符合Java语言和SQL例程规范的参数传递约定。
  • DERBY: Specifies that the stored procedure supports a vararg as the final argument in the parameter list.：指定存储过程支持vararg作为参数列表中的最终参数。
• LANGUAGE JAVA: Specifies the programming language of the stored procedure (currently, JAVA is the only option).：指定存储过程的编程语言（目前，JAVA是唯一选项）。
• DYNAMIC RESULT SETS 1: Specifies the maximum number of result sets retrieved; in this case, it is 1.：指定检索到的结果集的最大数目；在本例中，它是1。
• EXTERNAL NAME 'com.oracle.tutorial.jdbc.StoredProcedureJavaDBSample.showSuppliers' specifies the fully qualified Java method that this stored procedure calls. 指定此存储过程调用的完全限定Java方法。Note: Java DB must be able to find the method specified here in your class path or in a JAR file directly added to the database. 注意：JavaDB必须能够在类路径或直接添加到数据库的JAR文件中找到此处指定的方法。See the following step, Package Java Class in JAR File.请参阅以下步骤，将Java类打包到JAR文件中。

The following statement (which is found in StoredProcedureJavaDBSample.createProcedures) creates a stored procedure named GET_SUPPLIERS_OF_COFFEE (line breaks have been added for clarity):以下语句（可在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'

This stored procedure has two formal parameters, coffeeName and supplierName. 此存储过程有两个形式参数coffeeName和supplierName。The parameter specifiers IN and OUT are called parameter modes. 参数说明符IN和OUT称为参数模式。They define the action of formal parameters. 它们定义了形式参数的作用。See Parameter Modes for more information. 有关详细信息，请参阅参数模式。This stored procedure does not retrieve a result set, so the procedure element DYNAMIC RESULT SETS is 0.此存储过程不检索结果集，因此过程元素DYNAMIC RESULT SETS为0。

The following statement creates a stored procedure named RAISE_PRICE (line breaks have been added for clarity):下面的语句创建了一个名为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'

You can use SQL scripts to create stored procedures in Java DB. 您可以使用SQL脚本在JavaDB中创建存储过程。See the script javadb/create-procedures.sql and the Ant target javadb-create-procedure in the build.xml Ant build script.请参阅build.xml Ant build脚本中的脚本javadb/create-procedures.sql和Ant目标javadb-create-procedure。

Package Java Class in JAR File在JAR文件中打包Java类

The Ant build script build.xml contains targets to compile and package the tutorial in a JAR file. Ant build脚本build.xml包含编译教程并将其打包到JAR文件中的目标。At a command prompt, change the current directory to <JDBC tutorial directory>. 在命令提示下，将当前目录更改为<JDBC tutorial directory>。From this directory, run the following command to compile and package the tutorial in a JAR file:在此目录中，运行以下命令，将教程编译并打包到JAR文件中：

ant jar

The name of the JAR file is <JDBC tutorial directory>/lib/JDBCTutorial.jar.JAR文件的名称为<JDBC tutorial directory>/lib/JDBCTutorial.jar。

The Ant build script adds the file JDBCTutorial.jar to the class path. Ant构建脚本将文件JDBCTutorial.jar添加到类路径中。You can also specify the location of the JAR file in your CLASSPATH environment variable. 还可以在CLASSPATH环境变量中指定JAR文件的位置。This enables Java DB to find the Java method that the stored procedure calls.这使JavaDB能够找到存储过程调用的Java方法。

Adding JAR File Directly to Database将JAR文件直接添加到数据库

Java DB looks first in your class path for any required classes, and then in the database. JavaDB首先在类路径中查找任何必需的类，然后在数据库中查找。This section shows you how to add JAR files directly to the database.本节介绍如何将JAR文件直接添加到数据库中。

Use the following system procedures to add the JDBCTutorial.jar JAR file to the database (line breaks have been added for clarity):使用以下系统过程将JDBCTutorial.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')";

Note: The method StoredProcedureJavaDBSample.registerJarFile demonstrates how to call these system procedures. If you call this method, ensure that you have modified javadb-sample-properties.xml so that the value of the property jar_file is set to the full path name of JDBCTutorial.jar.

The install_jar procedure in the SQL schema adds a JAR file to the database. The first argument of this procedure is the full path name of the JAR file on the computer from which this procedure is run. The second argument is an identifier that Java DB uses to refer to the JAR file. (The identifier APP is the Java DB default schema.) The replace_jar procedure replaces a JAR file already in the database.

The system procedure SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY sets or deletes the value of a property of the database on the current connection. This method sets the property derby.database.classpath to the identifier specified in the install_jar file. Java DB first looks in your Java class path for a class, then it looks in derby.database.classpath.Java DB首先在Java类路径中查找类，然后在derby.database.classpath中查找。

Creating Stored Procedure in MySQL在MySQL中创建存储过程

Creating and using a stored procedure in Java DB involves the following steps:在Java DB中创建和使用存储过程涉及以下步骤：

1. Create the stored procedure with an SQL script or JDBC API使用SQL脚本或JDBCAPI创建存储过程
2. Call the stored procedure with the CALL SQL statement. 使用Call SQL语句调用存储过程。See the section Calling Stored Procedures in Java DB and MySQL请参阅在JavaDB和MySQL中调用存储过程一节。

Creating Stored Procedure in MySQL with SQL Scripts or JDBC API使用SQL脚本或JDBCAPI在MySQL中创建存储过程

MySQL uses a SQL-based syntax for its stored procedures. MySQL的存储过程使用基于SQL的语法。The following excerpt from the SQL script mysql/create-procedures.sql creates a stored procedure named SHOW_SUPPLIERS:以下摘自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|

The DROP PROCEDURE statement deletes that procedure SHOW_SUPPLIERS if it exists. In MySQL, statements in a stored procedure are separated by semicolons. However, a different delimiter is required to end the create procedure statement. This example uses the pipe (|) character; you can use another character (or more than one character). This character that separates statements is defined in the delimiter attribute in the Ant target that calls this script. This excerpt is from the Ant build file build.xml (line breaks have been inserted for clarity):

<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>

Alternatively, you can use the DELIMITER SQL statement to specify a different delimiter character.或者，您可以使用DELIMITER SQL语句指定不同的分隔符字符。

The CREATE PROCEDURE statement consists of the name of the procedure, a comma-separated list of parameters in parentheses, and SQL statements within the BEGIN and END keywords.CREATE PROCEDURE语句由过程名称、括号中以逗号分隔的参数列表以及BEGIN和END关键字中的SQL语句组成。

You can use the JDBC API to create a stored procedure. 可以使用JDBCAPI创建存储过程。The following method, StoredProcedureMySQLSample.createProcedureShowSuppliers, performs the same tasks as the previous script:以下方法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("Calling 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);
    }
  }

Note that the delimiter has not been changed in this method.请注意，此方法中的分隔符未更改。

The stored procedure SHOW_SUPPLIERS generates a result set, even though the return type of the method createProcedureShowSuppliers is void and the method does not contain any parameters. A result set is returned when the stored procedure SHOW_SUPPLIERS is called with the method CallableStatement.executeQuery:

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

The following excerpt from the method StoredProcedureMySQLSample.createProcedureGetSupplierOfCoffee contains the SQL query that creates a stored procedure named GET_SUPPLIER_OF_COFFEE:以下摘录自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("Calling 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);
    }
  }

This stored procedure has two formal parameters, coffeeName and supplierName. The parameter specifiers IN and OUT are called parameter modes. They define the action of formal parameters. See Parameter Modes for more information. The formal parameters are defined in the SQL query, not in the method createProcedureGetSupplierOfCoffee. To assign a value to the OUT parameter supplierName, this stored procedure uses a SELECT statement.

The following excerpt from the method StoredProcedureMySQLSample.createProcedureRaisePrice contains the SQL query that creates a stored procedure named RAISE_PRICE:以下摘录自方法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("Calling 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);
    }
  }

The stored procedure assigns a value to the INOUT parameter newPrice with the SET and SELECT statements. To exit the stored procedure, the stored procedure first encloses the statements in a BEGIN ... END block labeled main. To exit the procedure, the method uses the statement leave main.

Calling Stored Procedures in Java DB and MySQL在JavaDB和MySQL中调用存储过程

The following excerpt from the method runStoredProcedures, calls the stored procedure SHOW_SUPPLIERS and prints the generated result set:

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

Note: As with Statement objects, to call the stored procedure, you can call execute, executeQuery, or executeUpdate depending on how many ResultSet objects the procedure returns. However, if you are not sure how many ResultSet objects the procedure returns, call execute.

Calling the stored procedure SHOW_SUPPLIERS is demonstrated in the section Creating Stored Procedure with JDBC API in MySQL.调用存储过程SHOW_SUPPLIERS将在MySQL中使用JDBCAPI创建存储过程一节中演示。

The following excerpt from method runStoredProcedures, calls the stored procedure GET_SUPPLIER_OF_COFFEE:以下摘录自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);

The interface CallableStatement extends PreparedStatement. It is used to call stored procedures. Specify values for IN parameters (such as coffeeName in this example) just like you would with a PreparedStatement object by calling the appropriate setter method. However, if a stored procedure contains an OUT parameter, you must register it with the registerOutParameter method.

The following excerpt from the method runStoredProcedures, calls the stored procedure 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();

Because the parameter newPrice (the third parameter in the procedure RAISE_PRICE) has the parameter mode INOUT, you must both specify its value by calling the appropriate setter method and register it with the registerOutParameter method.由于参数newPrice（过程RAISE_PRICE中的第三个参数）具有参数模式INOUT，因此必须通过调用适当的setter方法指定其值，并使用registerOutParameter方法注册它。