java往oracle存储过程中传递数组方法小结
java往oracle存储过程中传递数组方法小结，下面是一个例子，比如存储过程中要接受一个数组，并且输出一个数组，则先注意数组在oracle中的定义方法如下：
CREATE TYPE array_table AS TABLE OF VARCHAR2 (50); -- Array of String
CREATE TYPE array_int AS TABLE OF NUMBER; -- Array of integers
接下来，一个存储过程，接受一个数组的输入，一个数组的输出，如下：
CREATE OR REPLACE PROCEDURE SchemaName.proc1 (p_array IN array_table, len OUT NUMBER, p_arr_int OUT array_int)AS v_count NUMBER;BEGIN p_arr_int := NEW array_int (); p_arr_int.EXTEND (10); len := p_array.COUNT; v_count := 0; FOR i IN 1 .. p_array.COUNT LOOP DBMS_OUTPUT.put_line (p_array (i)); p_arr_int (i) := v_count; v_count := v_count + 1; END LOOP;END;/
然后是JAVA代码，传入一个数组，返回一个数组的输出
import java.math.BigDecimal;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.internal.OracleTypes;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;public class TestDatabase { public static void passArray() { try{ Class.forName("oracle.jdbc.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:url ","UserName","Password");; String array[] = {"one", "two", "three","four"}; ArrayDescriptor des = ArrayDescriptor.createDescriptor("SchemaName.ARRAY_TABLE", con); ARRAY array_to_pass = new ARRAY(des,con,array); CallableStatement st = con.prepareCall("call SchemaName.proc1(?,?,?)"); st.setArray(1, array_to_pass); st.registerOutParameter(2, Types.INTEGER); st.registerOutParameter(3,OracleTypes.ARRAY,"SchemaName.ARRAY_INT"); st.execute(); System.out.println("size : "+st.getInt(2)); // Retrieving array from the resultset of the procedure after execution - ARRAY arr = ((OracleCallableStatement)st).getARRAY(3); BigDecimal[] recievedArray = (BigDecimal[])(arr.getArray()); for(int i=0;i<recievedArray.length;i++) System.out.println("element" + i + ":" + recievedArray + "\n"); } catch(Exception e) { System.out.println(e); } } public static void main(String args[]){ passArray(); }}
页:
[1]