java存儲過程數組

❶ oracle測試存儲過程傳入數組怎麼傳

這個是我自己寫的一個例子，你看看：
在命令窗口執行以下語句，創建自定義類型NESTEDARRAY。
create or replace TYPE NESTEDARRAY IS TABLE OF NUMBER;

在存儲過程中使用自定義類型NESTEDARRAY。
PROCEDURE GET_ARR_RESULT(INPUTARRAY IN NESTEDARRAY, AR OUT NESTEDARRAY) IS
BEGIN
AR := NESTEDARRAY();

FOR I IN 1 .. INPUTARRAY.COUNT LOOP
AR.EXTEND;
AR(I) := I|| INPUTARRAY(I);
END LOOP;
END GET_ARR_RESULT;

java代碼：
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;

/**
* Java獲取Oracle存儲過程返回自定義類型
* @author lucky star
*
*/
public class OracleTypeResultTest {

/**
* @param args
*/
public static void main(String[] args) {
Connection con = null;
OracleCallableStatement ocs = null;
String sql = "{call test.GET_ARR_RESULT(?,?)}";

try {
con = DBUtil.dbUtil.getConnection();
ocs = (OracleCallableStatement) con.prepareCall(sql);
String[] params = {「10001」,」10003」};
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("NESTEDARRAY", con);
ARRAY inputArray = new ARRAY(arrayDesc, con, params);
ocs.setARRAY(1, inputArray);
ocs.registerOutParameter(2,OracleTypes.ARRAY,"NESTEDARRAY");
ocs.execute();

ARRAY array = ocs.getARRAY(2);
Datum[] datum = array.getOracleArray();
for (int i=0;i<datum.length;i++) {
System.out.println(datum[i].intValue());
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(ocs!=null)ocs.close();
if(con!=null)con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

}

用VARCHAR2測試，發現存儲過程中無法獲取到傳入的數組的值。
不過，將自定義類型由varchar2改成NVarchar2就成功了。

❷ JAVA調用存儲過程，Oracle自定義類型作參數怎麼寫法

1. 存儲過程以及類型定義如下：

--The array in oracle
CREATE OR REPLACE TYPE idArray AS TABLE OF VARCHAR2(20);

--package header
CREATE OR REPLACE PACKAGE Lib_Package AS
PROCEDURE Book_Check_Procere(ids IN idArray, exist OUT NUMBER);
END Lib_Package;

--package body
CREATE OR REPLACE PACKAGE BODY Lib_Package AS

PROCEDURE Book_Check_Procere( ids IN idArray, exist OUT NUMBER) AS v_Index BINARY_INTEGER; BEGIN v_Index:= ids.FIRST; LOOP SELECT COUNT(*) INTO exist FROM Lib_Duplicate WHERE status='Lent' AND book_id=ids(v_Index); EXIT WHEN v_Index=ids.LAST OR exist>0; v_Index:= ids.NEXT(v_Index); END LOOP;END Book_Check_Procere;
END Lib_Package;

2.在Java中調用上面的存儲過程
(1) 在Oracle中定義數組類型idArray (2) 在java構造數組並轉換成Oracle中定義的數組類型，調用存儲過程

/** * 當要刪除圖書時，檢查是否仍然有圖書復本處於借出狀態 */ public boolean checkBookStatus(String[] bookIds) throws DataAccessException {
boolean flag = false; Connection conn = null; OracleCallableStatement cstmt = null; ArrayDescriptor desc = null; ARRAY bookIdArray = null; int count = 0; String sql = "{call LIB_PACKAGE.Book_Check_Procere(?,?)}";
DbDriverManager dbManager = DbDriverManager.getInstance(); conn = dbManager.getConnection(Constants.DATABASE);
try { cstmt = (OracleCallableStatement) conn.prepareCall(sql);
//定義oracle中的數組類型 desc = ArrayDescriptor.createDescriptor("IDARRAY", conn); bookIdArray = new ARRAY(desc, conn, bookIds);
cstmt.setObject(1, bookIdArray, oracle.jdbc.OracleTypes.ARRAY); cstmt.registerOutParameter(2, Types.INTEGER); cstmt.execute(); count = cstmt.getInt(2);
log.info(this.getClass() + ".checkBookStatus: count = " + count);
DbOperHelp.closeStatement(this.getClass(), cstmt); DbOperHelp.closeConnection(this.getClass(), conn); } catch (SQLException e) {
log.error(this.getClass() + ".checkBookStatus-->SQLException: " + e.getMessage()); DbOperHelp.closeStatement(this.getClass(), cstmt); DbOperHelp.closeConnection(this.getClass(), conn); throw new DataAccessException( "When check the books, there is a SQLException: " + e.getMessage(), e.getCause()); }
if (count > 0) {
flag = true; }
return flag; }

❸ 在java中怎樣刪除所有存儲過程

直接用jdbc執行sql語句塊declare @procName varchar(500) declare cur cursor -- 定義個游標 for select [name] from sysobjects where type = 'p' open cur -- 打開游標 fetch next from cur into @procName --迭代游標並給@procName賦值 while @@fetch_status = 0 --判斷迭代狀態 begin if @procName <> 'DeleteAllProceres' exec('drop procere ' + @procName) -- 動態執行sql語句 fetch next from cur into @procName end close cur deallocate cur