Java調用預存程序返回數組

標籤：

Java調用預存程序：

結合SQL操作與預存程序

 

create procedure set_death_age(poet VARCHAR2, poet_age NUMBER)

poet_id NUMBER;

begin SELECT id INTO poet_id FROM poets WHERE name = poet;

INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age);

end set_death_age;  

下面是調用上面預存程序的Java代碼：

public static void setDeathAge(Poet dyingBard, int age) throws SQLException{

Connection con = null;

CallableStatement proc = null;

try {

con = connectionPool.getConnection();

proc = con.prepareCall("{ call set_death_age(?, ?) }");

proc.setString(1, dyingBard.getName());

proc.setInt(2, age);

proc.execute();

}

finally {

try { proc.close(); }

catch (SQLException e) {}

con.close();

}

}  

 

 

 

Functions

預存程序可以有傳回值，所以CallableStatement類有類似getResultSet這樣的方法來擷取傳回值。當預存程序返回一個值時，你必須使用registerOutParameter方法告訴JDBC磁碟機該值的SQL類型是什麼。你也必須調整預存程序調用來指示該過程返回一個值。

下面接著上面的例子。這次我們查詢Dylan Thomas逝世時的年齡。這次的預存程序使用：

create function snuffed_it_when (VARCHAR) returns integer ‘‘declare

poet_id NUMBER;

poet_age NUMBER;

begin

--first get the id associated with the poet.

SELECT id INTO poet_id FROM poets WHERE name = $1;

--get and return the age.

SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id; 

return age;

end;

下面是調用這個預存程序的Java代碼：

 

 

connection.setAutoCommit(false);

CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }"); proc.registerOutParameter(1, Types.INTEGER);

proc.setString(2, poetName); cs.execute();

int age = proc.getInt(2); 

 

 

 

在使用預存程序中，我們有時需要傳遞可變數組，存在兩種情況，預存程序有輸入或輸出參數為自訂可變數組的。在java代碼中，如何正確調用oracle預存程序的自訂可變數群組類型，在這裡做一下樣本說明.

 

java調用oracle預存程序的自訂類型:

 

plsql定義字串和數值型可變數組：

 

一．定義全域類型：

 

CREATE OR REPLACE TYPE USERSEQID_ARRAY IS VARRAY(50000) OF NUMBER(9)

 

CREATE OR REPLACE TYPE USERNAME_ARRAY  AS VARRAY(32) of varchar(32)

 

CREATE OR REPLACE TYPE USERPWD_ARRAY  AS VARRAY(50000) of varchar(60)

 

二．java調用輸出參數為自訂數組的預存程序：

 

2.1 輸出參數為自訂數組的預存程序make_logincard_pro：

 

procedure make_logincard_pro (

p_cardsuitcode in varchar,

p_userseqidArr out USERSEQID_ARRAY ,

p_usernameArr out USERNAME_ARRAY

)

IS

v_addedtime date:= sysdate;

 

BEGIN

 

    FOR ii IN 1 .. 10 LOOP

 

        IF p_userseqidArr IS NULL THEN

          p_userseqidArr := USERSEQID_ARRAY(ii);

        ELSE

           p_userseqidArr.EXTEND;   --超過數組定義大小(50000)將拋出異常 

           p_userseqidArr(ii) := ii;               

        END IF;

 

        IF p_usernameArr IS NULL THEN

          p_usernameArr := USERSEQID_ARRAY(ii || ‘TT‘);

        ELSE

           p_usernameArr.EXTEND;      --超過數組定義大小(32)將拋出異常

           p_usernameArr(ii) := ii || ‘TT‘;                

        END IF;

 

 

    END LOOP

 

END make_logincard_pro ;

 2.2JAVA調用預存程序make_logincard_pro：

 

 

//程式碼片段

Connection con = session.connection();

java.sql.CallableStatement cst = con

        prepareCall("call CNBT.test_pro(?,?,?)");

cst.setString(1, cardSuitCode);

cst.registerOutParameter(2, OracleTypes.ARRAY,"USERSEQID_ARRAY");

cst.registerOutParameter(3, OracleTypes.ARRAY,"USERNAME_ARRAY");

 

java.sql.Array userSeqIdArr = cst.getArray(2);

java.sql.Array userNameArr = cst.getArray(3);

 

if ( userSeqIdArr  != null ) ...{

    BigDecimal userSeqIdList[] = (BigDecimal[])userSeqIdArr.getArray();//資料庫的number映射為BigDecimal

    //。。。。。。

}

if ( userNameArr  != null ) ...{

    String userNameList[] = (String[])userNameArr.getArray();

    //。。。。。。

}

 

 

 

 

--------------------------------------------------------------------------------

 

三. java調用輸入參數為自訂數組的預存程序：

 

3.1 輸入參數為自訂數組的預存程序update_logincard_pwd：

 

  /**//**********************************************

   *          update_logincard_pwd               *

   *功能描述：更新密碼預存程序         *

   *輸入參數：                                         *

   *輸出參數：                                         *

   *hanjiong                                    *

   ***********************************************/  

procedure update_logincard_pwd (

     p_userSeqIdList in USERSEQID_ARRAY,

     p_userPwdList in USERPWD_ARRAY,

     p_resultcode out number

   );

3.2 java調用預存程序update_logincard_pwd：

 

//程式碼片段

..........................

Connection con = session.connection();//使用的weblogic資料來源

oracle.jdbc.OracleCallableStatement cst2 = (oracle.jdbc.OracleCallableStatement)con

                                    .prepareCall(

                                    "call CNBT.update_logincard_pwd(?,?,?)");

                            weblogic.jdbc.wrapper.Connection weblogicConn = (weblogic.jdbc.wrapper.Connection)con;

                            oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection)weblogicConn.getVendorConnection();//轉化connection

                            oracle.sql.ArrayDescriptor des_USERSEQID_ARRAY =

                                    oracle.sql.ArrayDescriptor.createDescriptor("USERSEQID_ARRAY",oracleConn);

                            oracle.sql.ArrayDescriptor des_USERPWD_ARRAY =

                                    oracle.sql.ArrayDescriptor.createDescriptor("USERPWD_ARRAY",oracleConn);

                            oracle.sql.ARRAY ora_array1 = new oracle.sql.ARRAY(des_USERSEQID_ARRAY, oracleConn, userAccSeqIdArr);

                            oracle.sql.ARRAY ora_array2 = new oracle.sql.ARRAY(des_USERPWD_ARRAY, oracleConn, userPwdList);

                            cst2.setArray(1, ora_array1);

                            cst2.setArray(2, ora_array2);

                            cst2.registerOutParameter(3, java.sql.Types.INTEGER);

                            cst2.execute();

                            updateCode = cst2.getInt(3);

 

.....................................

....................................

因為我使用的是weblogic配置的資料來源，在取得的connection對象時需要注意，通過資料來源取得的Connection對象為weblogic.jdbc.wrapper.Connection，所以不能直接轉化為oracle.jdbc.OracleConnection，否則會出現java.lang.ClassCastException異常，所以我們要通過weblogic.jdbc.wrapper.Connection.getVendorConnection()取得java.sql.Connection，在強制轉化為oracle.jdbc.OracleConnection。

 

 

--------------------------------------------------------------------------------

 

通過上述兩種情況，就可以在Oracle預存程序中使用zid

 

Java調用預存程序返回數組