Question

oracle jdbc Array使用，真的玄！

上一篇: 如何在Oracle中修改Collection类型的变量。 | 下一篇: Javascript获取字符串字节数的方法
如何在JAVA程序中使用Struct一次传入多条数据给Oracle的存储过程。
为了减少连接Oracle数据库的数量，需要将多条数据作为变量一次传入Oracle的存储过程中。方法如下：
步骤一：定义对象类型。
CREATE TYPE department_type AS OBJECT (
DNO NUMBER (10),
NAME VARCHAR2 (50),
LOCATION VARCHAR2 (50)
);
步骤二：定义一个对象类型的数组对象。
CREATE TYPE dept_array AS TABLE OF department_type;
步骤三：定义存储过程来插入数据。
CREATE OR REPLACE PACKAGE objecttype AS
PROCEDURE insert_object (d dept_array);
END objecttype;
CREATE OR REPLACE PACKAGE BODY objecttype
AS
PROCEDURE insert_object (d dept_array)
AS
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
INSERT INTO department_teststruct
VALUES (d(i).dno,d(i).name,d(i).location);
END LOOP;
END insert_object;
END objecttype;

步骤四（可选步骤，即可以不做）：定义一个Java class来映射对象中类型。
步骤五：定义Java方法来调用存储过程。

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class TestStruct ...{
public static void main(String[] args)
...{
sendStruct();
}
public static void sendStruct()
...{
Connection dbConn = null;
try...{
Object[] so1 = ...{"10","Accounts","LHR"};
Object[] so2 = ...{"20","HR","ISB"};
OracleCallableStatement callStatement = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
dbConn = DriverManager.getConnection("jdbc:oracle:thin:@ServerName:Port:ORa", "UserName", "Password");
StructDescriptor st = new StructDescriptor("DEPARTMENT_TYPE",dbConn);
STRUCT s1 = new STRUCT(st,dbConn,so1);
STRUCT s2 = new STRUCT(st,dbConn,so2);
STRUCT[] deptArray = ...{s1,s2};
ArrayDescriptor arrayDept = ArrayDescriptor.createDescriptor("DEPT_ARRAY", dbConn);
ARRAY deptArrayObject = new ARRAY(arrayDept, dbConn, deptArray);
callStatement = (OracleCallableStatement)dbConn.prepareCall("{call insert_object(?)}");
((OracleCallableStatement)callStatement).setArray(1, deptArrayObject);
callStatement.executeUpdate();
dbConn.commit();
callStatement.close();
}
catch(Exception e)...{
System.out.println(e.toString());
}
}
}

Answer 1

刚好遇到同样的问题，解决了，感激！！！

Answer 2

不错！

Answer 3

不错！

Answer 4

你太有才了