oracle多表插入

*****************************************************

作者：赵安宁

发表：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'