Java处理Oracle中的用户自定义类型
Java对于PL/SQL中package下定义的类型并不支持（找不到，抛出invalid name pattern异常信息），需要定义schema级的类型。代码如下：
Schema Level Type:
create or replace type Guy as object
(
name varchar2(50),
age number
)
create or replace type GUYS as Table of GUY
PL/SQL:
create or replace package ArrayNesta is
PROCEDURE insert_guys(
guys_in IN guys
);
end ArrayNesta;
create or replace package body ArrayNesta is
PROCEDURE insert_guys(
guys_in IN guys
)
IS
BEGIN
for i in guys_in.first .. guys_in.last
LOOP
insert into ASSOCIATEARRAYNESTA(name, age) values(guys_in(i).name, guys_in(i).age);
END LOOP;
END insert_guys;
end ArrayNesta;
Database Table:
create table ASSOCIATEARRAYNESTA(
name varchar2(50),
age number
)
Java:
/**
*
*/
package array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.HashMap;
import java.util.Map;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class ArrayTest {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:username/password@host:port:database");
Connection conn = ods.getConnection();
System.out.println(conn);
StructDescriptor sd = StructDescriptor.createDescriptor("GUY", conn);
Object[] nesta = new Object[] {"nesta", 29};
STRUCT snesta = new STRUCT(sd, conn, nesta);
Object[] kisey = new Object[] {"kisey", 30};
STRUCT skisey = new STRUCT(sd, conn, kisey);
ArrayDescriptor ad = ArrayDescriptor.createDescriptor("GUYS", conn);
ARRAY guysin = new ARRAY(ad, conn, new STRUCT[] {snesta, skisey});
CallableStatement cs = conn.prepareCall("{ call ArrayNesta.insert_guys(?) }");
cs.setArray(1, guysin);
cs.execute();
conn.close();
}
}
页:
[1]