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);
}
}
}