JDBC 存储过程OUT参数

JDBC教程 - JDBC存储过程OUT参数

以下部分显示如何使用IN和OUT调用存储过程参数。

以PL/SQL语言编写的Oracle数据库存储过程如下所示。

过程中有四个参数，最后三个是OUT参数，这意味着数据将从这些参数中传出。

CREATE OR REPLACE PROCEDURE getPERSONByUserId(
     p_userid IN PERSON.USER_ID%TYPE,
     o_username OUT PERSON.USERNAME%TYPE,
     o_createdby OUT  PERSON.CREATED_BY%TYPE,
     o_date OUT PERSON.CREATED_DATE%TYPE)
IS
BEGIN

  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  PERSON WHERE USER_ID = p_userid;

END;
/

例子

调用存储过程的Java代码如下所示，OUT参数在 CallableStatement 的getXXX()方法中使用。

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;

public class Main {
  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:YourDatabase";
  private static final String DB_USER = "user";
  private static final String DB_PASSWORD = "password";

  public static void main(String[] argv) throws Exception {
    Class.forName(DB_DRIVER);
    Connection dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
        DB_PASSWORD);

    CallableStatement callableStatement = null;
    String getPERSONByUserIdSql = "{call getPERSONByUserId(?,?,?,?)}";
    callableStatement = dbConnection.prepareCall(getPERSONByUserIdSql);

    callableStatement.setInt(1, 10);
    callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
    callableStatement.registerOutParameter(4, java.sql.Types.DATE);

    callableStatement.executeUpdate();

    String userName = callableStatement.getString(2);
    String createdBy = callableStatement.getString(3);
    Date createdDate = callableStatement.getDate(4);

    System.out.println("UserName : " + userName);
    System.out.println("CreatedBy : " + createdBy);
    System.out.println("CreatedDate : " + createdDate);
    callableStatement.close();
    dbConnection.close();
  }
}