oracle存储过程自定义数组,java向oracle 存储过程 传输数组

1 根据传参创建一个自定义类型

create or replace type user_type force as object(

age number,

username nvarchar2(20),

phone nvarchar2(20),

syntime DATE

)

2 创建数组

create or replace type user_type_arr as table of user_type

3 创建存储过程

create or replace procedure pro_testobj(userList user_type_arr)

as

begin

forall i in userList.first .. userList.last

insert into STU_INFO(SNO,SNAME,SYN_TIME) values(userList(i).age,userList(i).username,userList(i).syntime);

end;

4 JAVA程序，向表中两写条记录

public void tesArray(OracleConnection  oracleconn) throws SQLException{

Struct[] userlist = new Struct[2];//定义数组

Object[] result = new Object[4];

result[0]=1;result[1]="a";result[2]=null;result[3]=TimeUtil.getCurrentDatetime();

Struct struct1 =oracleconn.createStruct("USER_TYPE", result);

userlist[0]=struct1;

Object[] result2 = new Object[4];

result2[0]=2;result2[1]="完成";

Struct struct2 =oracleconn.createStruct("USER_TYPE", result2);

userlist[1]=struct2;

CallableStatement proc = oracleconn.prepareCall("{call pro_testobj(?)}"); //调用存储过程

Array userArray=oracleconn.createOracleArray("USER_TYPE_ARR", userlist);

proc.setObject(1, userArray);

proc.execute();

}