利用自定义类型和index by表,对两表进行批量插入:java代码调用详见我的blog里面。给出sql:create table parent( id number(10), name varchar2(100), title varchar2(10) );create table child( id number(10), parent_id number(10), child_name varchar2(100), child_title varchar2(10), child_content varchar2(200), child_time timestamp );create sequence seq_p_c_id minvalue 1 maxvalue 9999999999 start with 1 increment by 1 nocache;drop type t_child_lst_map; drop type t_child_lst; drop type t_parent_lst;create or replace type t_parent as object ( name varchar2(100), title varchar2(10) ); /create or replace type t_child as object ( child_name varchar2(100), child_title varchar2(10), child_content varchar2(200) ); /create or replace type t_parent_lst as table of t_parent; /create or replace type t_child_lst as table of t_child; /create or replace type t_child_lst_map as table of t_child_lst; /create or replace procedure proc_ins_parent_child( i_parent_lst in t_parent_lst, --parent列表 i_child_map_lst in t_child_lst_map, --child列表集合,一个map元素对应一个child_lst,其下标与 parent列表的下标相同。 o_ret out number ) as var_parent t_parent; var_child_lst t_child_lst; var_child t_child; var_parent_id number; var_child_id number; begin for i in 1..i_parent_lst.count loop --取得parent各列的值 var_parent := i_parent_lst(i); --取得parent_id; select seq_p_c_id.nextVal into var_parent_id from dual;
--插入parent表 insert into parent( id, name, title ) values( var_parent_id, var_parent.name, var_parent.title );
--取得该parent对应的child列表 var_child_lst := i_child_map_lst(i); for j in 1..var_child_lst.count loop var_child := var_child_lst(j);
--取得child_id; select seq_p_c_id.nextVal into var_child_id from dual; --插入child表 insert into child( id, parent_id, child_name, child_title, child_content, child_time ) values( var_child_id, var_parent_id, var_child.child_name, var_child.child_title, var_child.child_content, systimestamp ); end loop;
end loop; o_ret := 0; exception when others then begin o_ret := -1; raise; end; end proc_ins_parent_child; /
在一个存储过程里头,可以写多条insert语句插入不同的表,没有限制的。 若是要求一条SQL语句往多张表插数据:INSERT ALL INTO T1 (C1, C2, ...) VALUES (C1, C2, ...) INTO T2 (C1, C2, ...) VALUES (C1, C2, ...) ... SELECT C1, C2, ... FROM T9;
具体分析表结构。建议用开发工具加ORACLE一起来实现比较简单
这个看你自己需要插入哪些表 就在存储过程里面写对应的sql就是了 相当简单的
INSERT ALL | FIRST when condition then INTO T1 (C1, C2, ...) VALUES (C1, C2, ...) when condition then INTO T2 (C1, C2, ...) VALUES (C1, C2, ...) ... SELECT C1, C2, ... FROM T9;
存储过程是随便搞的 ,插入多少表都中吧,一般Create Or Replace Procedure My_Test(……)as Begin Insert into a values('a') ; insert into a values('b') ; insert into b valuse(1) ; insert into b values(100);
关于INSERT ALL和INSERT FIRST的用法
id number(10),
name varchar2(100),
title varchar2(10)
);create table child(
id number(10),
parent_id number(10),
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200),
child_time timestamp
);create sequence seq_p_c_id
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
nocache;drop type t_child_lst_map;
drop type t_child_lst;
drop type t_parent_lst;create or replace type t_parent as object (
name varchar2(100),
title varchar2(10)
);
/create or replace type t_child as object (
child_name varchar2(100),
child_title varchar2(10),
child_content varchar2(200)
);
/create or replace type t_parent_lst as table of t_parent;
/create or replace type t_child_lst as table of t_child;
/create or replace type t_child_lst_map as table of t_child_lst;
/create or replace procedure proc_ins_parent_child(
i_parent_lst in t_parent_lst, --parent列表
i_child_map_lst in t_child_lst_map, --child列表集合,一个map元素对应一个child_lst,其下标与 parent列表的下标相同。
o_ret out number
) as
var_parent t_parent;
var_child_lst t_child_lst;
var_child t_child;
var_parent_id number;
var_child_id number;
begin
for i in 1..i_parent_lst.count loop
--取得parent各列的值
var_parent := i_parent_lst(i); --取得parent_id;
select seq_p_c_id.nextVal into var_parent_id from dual;
--插入parent表
insert into parent(
id,
name,
title
)
values(
var_parent_id,
var_parent.name,
var_parent.title
);
--取得该parent对应的child列表
var_child_lst := i_child_map_lst(i); for j in 1..var_child_lst.count loop
var_child := var_child_lst(j);
--取得child_id;
select seq_p_c_id.nextVal into var_child_id from dual; --插入child表
insert into child(
id,
parent_id,
child_name,
child_title,
child_content,
child_time
)
values(
var_child_id,
var_parent_id,
var_child.child_name,
var_child.child_title,
var_child.child_content,
systimestamp
);
end loop;
end loop;
o_ret := 0; exception when others then
begin
o_ret := -1;
raise;
end;
end proc_ins_parent_child;
/
若是要求一条SQL语句往多张表插数据:INSERT ALL
INTO T1 (C1, C2, ...) VALUES (C1, C2, ...)
INTO T2 (C1, C2, ...) VALUES (C1, C2, ...)
...
SELECT C1, C2, ... FROM T9;
INSERT ALL | FIRST
when condition then
INTO T1 (C1, C2, ...) VALUES (C1, C2, ...)
when condition then
INTO T2 (C1, C2, ...) VALUES (C1, C2, ...)
...
SELECT C1, C2, ... FROM T9;
Begin Insert into a values('a') ;
insert into a values('b') ; insert into b valuse(1) ;
insert into b values(100);
……
……
End ;