3,491
社区成员
发帖
与我相关
我的任务
分享
INSERT ALL
INTO T1 (C1, C2, ...) VALUES (C1, C2, ...)
INTO T2 (C1, C2, ...) VALUES (C1, C2, ...)
...
SELECT C1, C2, ... FROM T9;
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;
/