Oracle 存储过程传到二维数组

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 isids id3 := new id3();BEGINFOR i IN 1 .. 10 LOOPids.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());}}}

?
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html

查看更多 下一篇