Java調用Oracle存儲過程示例

2. 創建存儲過程
無返回值的存儲過程
CREATE OR REPLACE PROCEDURE proctest (
   ID     IN   NUMBER,
   NAME   IN   VARCHAR2,
   age    IN   NUMBER
)
AS
BEGIN
   INSERT INTO t_test_procedure
               (ID, NAME, age)
        VALUES (ID, NAME, age);
END proctest;
/

有非列表返回值的存儲過程
CREATE OR REPLACE PROCEDURE proc_returnnolist (
   p_id     IN       INTEGER,
   p_name   OUT      VARCHAR2,
   p_age    OUT      INTEGER
)
AS
BEGIN
   SELECT NAME, age
     INTO p_name, p_age
     FROM t_test_procedure
    WHERE >END proc_returnnolist;
/

返回結果集的存儲過程
要返回結果集，需要使用Oracle的package
CREATE OR REPLACE PACKAGE test_proc_package
AS
   TYPE testproccursor IS REF CURSOR;
END test_proc_package;
--存儲過程
create or replace procedure xxx(testproccursor   OUT   test_proc_package.testproccursor) is
begin
  OPEN testproccursor
    FOR
       SELECT *
         FROM t_test_procedure;
end xxx;
/

// Java Connected Oracle
public Connection getConnection(){
 try{
  Class.forName("oracle.jdbc.driver.OracleDriver");
  Connection conn = DriverManager.getConnection(
    "jdbc:Oracle:thin:@//133.96.40.82:1521/ora92",
    "icdmain41","icd");
 }catch(Java.lang.ClassNotFoundException e){
  System.out.println(e.getMessage());
 }catch(Java.sql.SQLException e){
  System.out.println(e.getMessage());
 }
 return conn != null ? conn : null;
}

用Java調用存儲過程
1. 調用無返回值的存儲過程
    CallableStatement callableStmt = con.prepareCall("{call xxx(?,?,?)}");
    callableStmt.setInt(1, 2);
    callableStmt.setString(2, "Stephen");
    callableStmt.setInt(3, 27);
    callableStmt.execute();
    callableStmt.close();
    System.out.println("Success");

2. 調用返回非列表的存儲過程
    CallableStatement callableStmt = con
      .prepareCall("call proc_returnnolist(?,?,?)");
    callableStmt.setInt(1, 3);
    callableStmt.registerOutParameter(2, Types.VARCHAR);
    callableStmt.registerOutParameter(3, Types.INTEGER);
    callableStmt.execute();
    int age = callableStmt.getInt(3);
    callableStmt.close();
    System.out.println("name:" + name + " age:" + age);

3. 調用返回結果集的存儲過程
    CallableStatement callableStmt = con.prepareCall("call proc_returnlist(?)");
    callableStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
    callableStmt.execute();
    ResultSet rs = (ResultSet) callableStmt.getObject(1);
    // ResultSet rs = ((OracleCallableStatement)callableStmt).getCursor(1);
    while (rs.next()) {
     System.out.println("name:" + rs.getString("name") + " age:"+ rs.getInt("age"));
    }
    rs.close();
    callableStmt.close();

==========================================================================

CREATE OR REPLACE PROCEDURE P_TGetSpecialno(
  callerType  IN  VARCHAR2,
  datetime    OUT DATE,
  c_Specialno OUT SYS_REFCURSOR
)
AS
BEGIN
  datetime := SYSDATE;
 OPEN c_Specialno FOR
   SELECT * FROM t_scejudgespecialcustomer t
     WHERE t.specialno LIKE callerType||'%';
END;

==========   Java Code ==========

try{
   CallableStatement callProcedure =
    this.getConnection().prepareCall("{call P_TGetSpecialno(?,?,?)}");// String,Date,Cursor
   callProcedure.setString(1,"1342636");
   callProcedure.registerOutParameter(2,oracle.jdbc.driver.OracleTypes.DATE);
   callProcedure.registerOutParameter(3,oracle.jdbc.driver.OracleTypes.CURSOR);
   callProcedure.execute();
           
   String date = callProcedure.getString(2);
   ResultSet res = (ResultSet) callProcedure.getObject(3);
   //ResultSet res = ((OracleCallableStatement)callProcedure).getCursor(3);
   while(res.next()){
       System.out.println(res.getString("Specialno"));
   }
   System.out.println("數據庫時間："+date);
   res.close();
   callProcedure.close();

}catch(SQLException e){
    System.out.println(e.getMessage());
}finally{
}