哈哈哈,找到方法了。 create or replace package pkg_dzcp_price is
type string_array is table of raw(16) index by binary_integer; procedure PRO_DZCP_PRICE(CreateKaNo in string_array); end pkg_dzcp_price; ---------- create or replace package body pkg_dzcp_price is PROCEDURE PRO_DZCP_PRICE(CreateKaNo in string_array) IS begin FOR i IN CreateKaNo.FIRST .. CreateKaNo.LAST LOOP INSERT into table1 (c1,c2) SELECT c1,c2 FROM table2 WhERE AUTO_ID = CreateKaNo(i); END LOOP; exception when others then rollback;
END PRO_DZCP_PRICE;end pkg_dzcp_price;
需要在数据库定义数组类型或表类型,在java构造这个对象,数据库接收时,参数类型是这个数组类型。下面是例子。 1、先定义数据库数组、表对象CREATE OR REPLACE TYPE UPLOAD_ENTITY AS OBJECT ( 属性1 varchar2(100), 属性2 varchar2(200), 属性3 varchar2(100) ); --定义一个实体表类型 CREATE OR REPLACE TYPE UPLOAD_ENTITY_TAB is table of UPLOAD_ENTITY;2、java中传入参数import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor;public class Test{ public static void mian(string[] args){ try{ List<ENTITY> entity = new ArrayList();--要传入的数据 StructDescriptor sd = new StructDescriptor("UPLOAD_ENTITY",conn); --引用数据库对象 ArrayDescriptor ad = new ArrayDescriptor("UPLOAD_ENTITY_TAB",conn); --引用数据库对象列表 STRUCT[] results = new STRUCT[entity.size()]; for (int i = 0; i < 10; i++) { Object[] obj = new Object[] { o.属性1()!=null?o.属性1():"", o.属性2()!=null?o.属性2():"", o.属性3()!=null?o.属性3():"" }; results[i] = new STRUCT(sd, conn, obj); }
ARRAY array = new ARRAY(ad, conn, results); stmt.setArray(1, array); stmt.registerOutParameter(2, OracleTypes.VARCHAR); stmt.registerOutParameter(3, OracleTypes.VARCHAR); stmt.execute(); errCode = stmt.getString(2); errMsgs.append(stmt.getString(3)); }catch(Exception e){ errCode = "F"; e.printStackTrace(); } } } 3、PLSQL中使用 procedure import_all_Entitys(v_in_list in UPLOAD_ENTITY,--传进实体表类型 p_ret_flag out varchar2, p_err_msg out varchar2) is entity UPLOAD_ENTITY; --定义实体 begin --如果使用 for i in 1 .. v_in_list.count loop entity := v_in_list(i); dbms_output.put_line(entity.属性1 ||':'||entity.属性2); end loop; end;
begin
insert into tab value(...);
insert into tab value(...);
insert into tab value(...);
insert into tab value(...);
end;
create or replace package pkg_dzcp_price is
type string_array is table of raw(16) index by binary_integer;
procedure PRO_DZCP_PRICE(CreateKaNo in string_array);
end pkg_dzcp_price;
----------
create or replace package body pkg_dzcp_price is
PROCEDURE PRO_DZCP_PRICE(CreateKaNo in string_array) IS
begin
FOR i IN CreateKaNo.FIRST .. CreateKaNo.LAST LOOP
INSERT into table1 (c1,c2)
SELECT c1,c2 FROM table2 WhERE AUTO_ID = CreateKaNo(i);
END LOOP;
exception
when others then
rollback;
END PRO_DZCP_PRICE;end pkg_dzcp_price;
1、先定义数据库数组、表对象CREATE OR REPLACE TYPE UPLOAD_ENTITY AS OBJECT
(
属性1 varchar2(100),
属性2 varchar2(200),
属性3 varchar2(100)
);
--定义一个实体表类型
CREATE OR REPLACE TYPE UPLOAD_ENTITY_TAB is table of UPLOAD_ENTITY;2、java中传入参数import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;public class Test{
public static void mian(string[] args){
try{
List<ENTITY> entity = new ArrayList();--要传入的数据
StructDescriptor sd = new StructDescriptor("UPLOAD_ENTITY",conn); --引用数据库对象
ArrayDescriptor ad = new ArrayDescriptor("UPLOAD_ENTITY_TAB",conn); --引用数据库对象列表
STRUCT[] results = new STRUCT[entity.size()];
for (int i = 0; i < 10; i++) {
Object[] obj = new Object[] {
o.属性1()!=null?o.属性1():"",
o.属性2()!=null?o.属性2():"",
o.属性3()!=null?o.属性3():""
};
results[i] = new STRUCT(sd, conn, obj);
}
ARRAY array = new ARRAY(ad, conn, results);
stmt.setArray(1, array);
stmt.registerOutParameter(2, OracleTypes.VARCHAR);
stmt.registerOutParameter(3, OracleTypes.VARCHAR);
stmt.execute();
errCode = stmt.getString(2);
errMsgs.append(stmt.getString(3));
}catch(Exception e){
errCode = "F";
e.printStackTrace();
}
}
}
3、PLSQL中使用 procedure import_all_Entitys(v_in_list in UPLOAD_ENTITY,--传进实体表类型
p_ret_flag out varchar2,
p_err_msg out varchar2) is
entity UPLOAD_ENTITY; --定义实体
begin
--如果使用
for i in 1 .. v_in_list.count loop
entity := v_in_list(i);
dbms_output.put_line(entity.属性1 ||':'||entity.属性2);
end loop;
end;