Oracle 存储过程传入二维数组

Oracle  存储过程传入二维数组

   

使用Oracle数组，打算传入一个二维数组到数据库中，然后利用存储过程将数据写入到数据

 

1、创建一个和目标表结构完全一样的TYPE，类型为object

CREATE OR REPLACE TYPE ID2 AS OBJECT(
ID NUMBER(10),
NUM NUMBER(10)
)

 
2、创建一个table类型的type

CREATE OR REPLACE TYPE id3 AS table OF id2

 
3、在存储过程中插入数据

create or replace procedure p_batch_insert3(ids in id3) is                                                        

begin
--对数组进行类型转变然后将数据插入到目标表中。
insert into a
(id, num)
select * from the (select cast(ids as id3) from dual);
end p_batch_insert3;

 

4、测试存储过程

CREATE OR REPLACE PROCEDURE sample is

ids id3 := new id3();
BEGIN
FOR i IN 1 .. 10 LOOP

ids.Extend();
ids(i) := id2(i,i);
dbms_output.put_line(''id--->'' || ids(i).id);
dbms_output.put_line(''num--->'' || ids(i).num);
END LOOP;
p_batch_insert3(ids);
END;

 

5、java程序如下：令我不解的是传入一维数组，能够将数据写入到数据库中，
在使用二维数组时，出现如下错误
java.sql.SQLException: 无法转换为内部表示: [I@9ed927

package com;
import java.sql.Connection;
import java.sql.PreparedStatement ;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.sql.rowset.*;
import oracle.sql.*;
import com.sun.rowset.CachedRowSetImpl;

public class Test2 {
public static void main(String arg[]) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.6.185:1521:billing";
Connection con = DriverManager.getConnection(url, "flux",
"1231flux");
//con.setAutoCommit(false);
PreparedStatement pstmt = null;
String sql = "{call p_batch_insert3(?)}";
//String sql = "{call p_batch_insert2(?)}";

pstmt = con.prepareCall(sql);
//int[] x = new int[34465]; 
//int x[][] = new int[10][]; 
int x[][] = {{1,2},{4,5}};

/*for (int i = 0; i <= 34464; i++) {

x[i]=i; 
}
*/


for(int i = 0; i < x.length; i++)
{
for(int j = 0; j < x[i].length; j++) 
System.out.println("a1[" + i + "][" + j +"] = " + x[i][j]); 
} 

System.out.println("start-->" + System.currentTimeMillis());
oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("ID3",con);
oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc,con,x);
// ArrayDescriptor descriptor =ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
pstmt.setArray(1, array);
pstmt.executeUpdate();
//pstmt.executeBatch();
//con.commit();
//con.setAutoCommit(true);
System.out.println("end-->" + System.currentTimeMillis());
} catch (Exception e) {
e.printStackTrace();
//System.out.println(e.toString());
}
}
}