oracle 存儲過程返回數組的方法：
1.建立包
create or replace package test is
TYPE filename_array IS TABLE OF varchar2(1);
?filename filename_array;
end test;
2. 建立存儲過程
create or replace procedure test_array(v_cfjg out test.filename_array ) is
begin DECLARE i number;
D_cfjg dic_cfjg%rowTYPE;
-- D_nr dic_cfjg%rowTYPE;
cursor c1 is SELECT * FROM dic_cfjg;
BEGIN
i:=0;
v_cfjg := test.filename_array(); --數組初始化

open c1;
LOOP fetch c1 into D_cfjg;
EXIT WHEN c1%NOTFOUND ;
i:=i+1;
v_cfjg.EXTEND;
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));
v_cfjg(v_cfjg.count):=D_cfjg.dm;
DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));
-- 測試
-- FETCH C1 INTO D_cfjg;
-- EXIT WHEN c1%NOTFOUND ;
END LOOP;
end;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
end test_array;

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

Java 調用PL/SQL Returning Object Array

上次寫了oracle 存儲過程返回數組的方法。這次實現在java調用返回值是數組的存儲過程方法。

1. 數組：CYJ_ARRAY

CREATE OR REPLACE TYPE CYJ_ARRAY is VARRAY(20) OF integer

2. 存儲過程：getTestArray()

CREATE OR REPLACE FUNCTION getTestArray RETURN CYJ_ARRAY
AS
? l_test CYJ_ARRAY :=CYJ_ARRAY();

?? begin
??? FOR i IN 1..10 LOOP
????? l_test.extend;
????? l_test(l_test.count) := i;
????? dbms_output.put_line(l_test(l_test.count));
??? END LOOP;
??? RETURN? l_test;
? END;
3. 調用存儲過程的java 類

public class TestOracleReturnArray? {
?

public static void main(String[] args) throws SQLException {
?Connection conn = null;
?CallableStatement cstmt = null;
?ResultSet rs = null;
?try {
??
??DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
??conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora","zzz","sss");
??
??conn.setAutoCommit(false);
??String procedure = "{? = call getTestArray() }";// 調用存儲過程
??cstmt = conn.prepareCall(procedure);
??cstmt.registerOutParameter(1, OracleTypes.ARRAY,"CYJ_ARRAY"); //注：type name：區分大小，不是包中的type, 使用包中定義的type如何調用還不知道
??
??cstmt.execute();
??ARRAY array = (ARRAY)cstmt.getObject(1);
??Object[] items = (Object[])array.getArray();
??Object obj;
??
??for (int i = 0; i < items.length; i++) {
??? obj = items[i];
???
???System.out.println(obj);
???
???} // for
?} catch (Exception e) {
??e.printStackTrace();
?} finally {
??//DbConnRestore.connRestore(rs, cstmt, null, conn);
?}
}

}

參考如下資料：

http://www.oracle.com/technology/global/cn/sample_code/tech/java/codesnippet/jdbc/varray/index.html