1、将数组转换成逗号分隔字符串,在存储过程中解析
2、自己创建个类型,用于接收数组
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 )); 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[i] + "\n" ); } catch (Exception e) { System.out.println(e); } } public static void main(String args[]) { passArray(); } }