JDBC 存儲(chǔ)過(guò)程OUT參數(shù)

JDBC教程 - JDBC存儲(chǔ)過(guò)程OUT參數(shù)

以下部分顯示如何使用IN和OUT調(diào)用存儲(chǔ)過(guò)程參數(shù)。

以PL/SQL語(yǔ)言編寫的Oracle數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程如下所示。

過(guò)程中有四個(gè)參數(shù)，最后三個(gè)是OUT參數(shù)，這意味著數(shù)據(jù)將從這些參數(shù)中傳出。

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

例子

調(diào)用存儲(chǔ)過(guò)程的Java代碼如下所示，OUT參數(shù)在 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();
  }
}