multitable insert - 多表插入语句

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/7/30
Subject: 多表插入 multitable insert
To: xiewenxiewen at gmail.com


多表插入是oracle 9i新增功能, 实现一条insert语句插入多个目标表，提高性能，简化代码

1.举例:
drop table a;
create table a(a int, b varchar2(50));
drop table b;
create table b(a int, b date);
insert all
into a values (object_id, object_name)
into b values (object_id, created)
select object_id, object_name,object_type,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;
rollback;

看到两个表都插入记录了



2.可以增加条件语句，有选择的插入记录
delete from a;
delete from b;
insert all
when otype = 'VIEW' and name ='USER_TABLES' then
into a (a,b) values(id,name)
when mod(id,2)=0 then
into b (a) values (id)
else
into b(a) values (id+10000)
select object_id id, object_name name ,object_type otype,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;



3.
insert all 的意思是，oracle依次判断每一个when条件是否满足，满足就插入记录，不满足的不插入
还有一种是insert first，意思是，依次判断每一个条件，不满足的不插入，只要发现第一个条件满足了就插入，然后跳过剩下的条件，不继续判断了

比如:
delete from a;
delete from b;
insert first
when otype = 'VIEW' and name ='USER_TABLES' then
into a (a,b) values(id,name)
when mod(id,2)=0 then
into b (a) values (id)
else
into b(a) values (id+10000)
select object_id id, object_name name ,object_type otype,created
from all_objects
where object_name like 'USER_TABLE%';


select * from a;
select * from b;



4.序列不能用在select里面
delete from a;
delete from b;
create sequence s;


insert all
into a(a) values (s)
into b(a) values (s)
select s.nextval s
from dual;


但可以用在into values后
insert all
into a(a) values (s.nextval)
into b(a) values (s.nextval)
select 1
from dual;


select * from a;
select * from b;

drop sequence s;



Xie wen（谢文）
Network & Operations, Mobile Software Solutions (MDB) MOTOROLA Inc.
No. 108 Jian Guo Road, Chao Yang District, Beijing 100022 P. R. China
e-mail wenxie at motorola.com




-fin-