*****************************************************
作者:赵安宁
发表:CSDN
日期:20090828
*****************************************************
在oracle中关于多表插入的有四种分别是:
1.无条件的多表insert all
2.带条件的多表insert all
3.带条件的多表insert first
4.Pivoting insert
语法:
INSERT
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
(subquery)
用例子说明,表结构如下:
drop table table_all;
create table table_all(
main varchar2(10),
col1 varchar2(10),
col2 varchar2(10),
col3 varchar2(10),
col4 varchar2(10),
col5 varchar2(10),
col6 varchar2(10)
);
---插入数据
insert into table_all select 'main1','1','2','3','4','5','6' from dual;
insert into table_all select 'main2','1','2','3','4','5','6' from dual;
insert into table_all select 'main3','1','2','3','4','5','6' from dual;
insert into table_all select 'main4','1','2','3','4','5','6' from dual;
insert into table_all select 'main5','1','2','3','4','5','6' from dual;
insert into table_all select 'main6','1','2','3','4','5','6' from dual;
insert into table_all select 'main7','7','7','7','4','5','6' from dual;
insert into table_all select 'main7','7','7','7','4','5','6' from dual;
insert into table_all select 'main7','7','7','7','4','5','6' from dual;
insert into table_all select 'main7','7','7','7','4','5','6' from dual;
insert into table_all select 'main7','8','8','8','4','5','6' from dual;
---子表
create table table_b as select main,col1,col2,col3 from table_all where 1=0
create table table_a as select main,col4,col5,col6 from table_all where 1=0
create table table_col as select main,col1 col from table_all where 1=0
create table table_all_1 as select * from table_all where 1=0
create table table_ all_2 as select * from table_all where 1=0
create table table_ all_3 as select * from table_all where 1=0
无条件的多表insert all
insert all
into table_b (main,col1,col2,col3) values (main,col1,col2,col3)
into table_a (main,col4,col5,col6) values (main,col4,col5,col6)
select main,col1,col2,col3,col4,col5,col6 from table_all;
2.带条件的多表insert all
insert all
when main = 'main7' then
into table_all_1
when col1 = '8' then
into table_all_2
else
into table_all_3
select * from table_all
3.带条件的多表insert first
delete from table_all_1
delete from table_all_2
delete from table_all_3
insert first
when main = 'main7' then
into table_all_1
when col1 = '8' then
into table_all_2
else
into table_all_3
select * from table_all
insert all与insert first 的区别在与:即如果使用first,当第一个when条件满足时,执行第一个into语句,在后面的条件不再判断,直接跳到下一行数据.
查看table_all_2 可以发现:insert all语句执行后 有数据,而在 insert first 语句执行后没有数据。
4.Pivoting insert
insert all
into table_col values (main,col1)
into table_col values(main,col2)
into table_col values(main,col3)
into table_col values(main,col4)
into table_col values(main,col5)
into table_col values(main,col6)
select * from table_all where main='main1'