第一种方法 ,纯SQL编写: insert into a(a_id,a1,a1)values(seq.nextval,"","") insert into b(b_id,b1,b2) values(seq.vextval,select a1,a2 from a where a_id='')
用存储过程来实现吧。先把seq查出来放在一个变量里面。取curValue的方式在多session调用的时候会问题,可能拿到的不是前面的那个。 参考: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; /
是不是插入一个表中数据之后,然后把这个表里面的所有数据插入另一个新表吗\你看看这个 declare v_a1 a.a1%type; v_a2 a.a2%type; cursor a_cursor is select a1,a2 from a;begin
open a_cursor; loop fetch a_cursor into v_a1,v_a2; exit when a_cursor%notfound; insert into b values(seq.nextval,v_a1,v_a2); end loop; close a_cursor;
我有2条数据需要插入A表和B表.插入A表后,我想用A表的sequence id,在往B表里插入数据..我不希望是从A表查出来sequence id往B表里插入.没有办法在望A表里插入数据时,能把sequence id都记录下来,直接在插入B表时使用吗?怎么实现?
insert into b (id)values(seq.currval);
这样不行么?
这时怎么得到2个sequence id,一下往b表插入2条.?
insert into b(b_id,b1,b2) values(seq.vextval,select a1,a2 from a where a_id='')
参考: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;
/
v_a2 a.a2%type;
cursor a_cursor is select a1,a2 from a;begin
open a_cursor;
loop
fetch a_cursor into v_a1,v_a2;
exit when a_cursor%notfound;
insert into b values(seq.nextval,v_a1,v_a2);
end loop;
close a_cursor;
end;