oracle insert的扩展

⑴ insert into table_name [(column[,column...])] select_statement
             每次只能插入一个表，但性能上比写多条insert语句要高。另外，oracle还提供create table table_name as select ...,as不可少！实现直接创建并且插入表，依赖于后面的select语句的结果，如：create table new_table as select * from source_table where 1=0
             案例：
             表mxt新增一列time，那么这个列会被置在最后，现想把此列放于第2
             create table copy_mxt as select id,time,name from mxt;
             drop table mxt;
             rname copy_mxt to mxt;

    向多个表发散弹枪：

          类型：
            ① 无条件的insert
            ② 带条件的insert all
            ③ 带条件的insert first
            ④ pivoting insert

          多表insert的使用限制：
            ① 只能对table使用insert，不能对视图或物化视图使用
            ② 不能对远程表使用这个插入操作
            ③ 多表插入时，不能指定一个表的集操作
            ④ 多表insert的into的目标表加在一起的列数不能超过999

          ㈠ 无条件的insert
             语法：
             insert all
             into dest_table1 [column(,column...)] [values (...)]
             into dest_table2 [column(,column...)] [values (...)]
             ...
             select_statement  --values中指定是select结果列，如果没有values指定列，那么表示目标表的列类型和顺序与select语句查询的结果完全一致。建议不要省略目标表中的列和values，增强可读性。
             例子：

[sql] view plaincopyprint?

1. create table emp_1as select employee_id,last_namefrom employees where 1=0; 
2. create table emp_2as select *from employees where 1=0; 
3. create table emp_3as select *from employees where 1=0; 
4. insert all 
5. --不指定emp_1后面的列，也没有values指定列，那么emp_1所有列类型和顺序与查询的列的类型和顺序一致 
6. --也就是emp_1中只有查询结果中的那几列，而且类型和顺序一致 
7.   into emp_1 
8. --指定了emp_2后面的列，但没有values指定列，表示emp_2中要插入的列被选择出来，与查询结果列的类型和顺序一致 
9. --emp_2也有很多列，不止这两列 
10.   into emp_2(employee_id,last_name) 
11. --指定emp_3后面的列和values指定列，那么values后面的列名必须与查询结果一致，如果查询有别名，必须在values中使用别名 
12. --emp_3指定的列的类型和顺序必须与values保持一致 
13. --emp_3也可能列数大于指定列数 
14.   into emp_3(employee_id,last_name) values(e_id,e_last_name) 
15.   select employee_id e_id,last_name e_last_name  
16.    from employees 

create table emp_1 as select employee_id,last_name from employees where 1=0;create table emp_2 as select * from employees where 1=0;create table emp_3 as select * from employees where 1=0;insert all--不指定emp_1后面的列，也没有values指定列，那么emp_1所有列类型和顺序与查询的列的类型和顺序一致--也就是emp_1中只有查询结果中的那几列，而且类型和顺序一致  into emp_1--指定了emp_2后面的列，但没有values指定列，表示emp_2中要插入的列被选择出来，与查询结果列的类型和顺序一致--emp_2也有很多列，不止这两列  into emp_2(employee_id,last_name)--指定emp_3后面的列和values指定列，那么values后面的列名必须与查询结果一致，如果查询有别名，必须在values中使用别名--emp_3指定的列的类型和顺序必须与values保持一致--emp_3也可能列数大于指定列数  into emp_3(employee_id,last_name) values(e_id,e_last_name)  select employee_id e_id,last_name e_last_name    from employees

          ㈡ 带条件的insert
             包括：insert all和insert first
             语法：
             insert all
             when condition_statement
             then into dest_table1[指定列] values[查询中的列]
             when condition_statement
             then into dest_table2[指定列] values[查询中的列]
             select_statement
              注释：带条件的insert all与insert first的区别在于只按条件对查询的结果进行刷选，不会考虑前面已经匹配过的记录，每次都是全量匹配，而insert first对已经匹配过的记录不予考虑，下一个when会自动过滤掉上一个匹配过的行记录
             例子：

[sql] view plaincopyprint?

1. insert all 
2.   --将查询结果s_id>20的插入，条件中指定的列必须与查询结果名字一致，如果有别名，请用别名 
3.   when e_id>20 
4.     then into emp_1 
5.   --s_last_name为M开头的插入，可能插入的行与s_id有重复 
6.   when e_last_name like'M%' 
7.     then into emp_2(employee_id,last_name) 
8.   --如果指定else，则不满足上面条件的插入到emp_3,插入的行不会与上面两个重复 
9.   else 
10.     into emp_3(employee_id,last_name)values(e_id,e_last_name) 
11.   select employee_id e_id,last_name e_last_name 
12.     from employees 

insert all  --将查询结果s_id>20的插入，条件中指定的列必须与查询结果名字一致，如果有别名，请用别名  when e_id>20    then into emp_1  --s_last_name为M开头的插入，可能插入的行与s_id有重复  when e_last_name like 'M%'    then into emp_2(employee_id,last_name)  --如果指定else，则不满足上面条件的插入到emp_3,插入的行不会与上面两个重复  else    into emp_3(employee_id,last_name) values(e_id,e_last_name)  select employee_id e_id,last_name e_last_name    from employees

          ㈢ pivoting insert（旋转插入）
             关系型数据库三范式，归纳起来三句话：
             ① 行不可再分
             ② 列不可再分
             ③ 列不可重复
             但很多情况下，范式都会被破坏。使用旋转插入可以实现将非关系表记录转换为关系表在数据库存储。和insert all类似，不过这里是向一个表发散弹枪，将列转变成行。
             例子：

[sql] view plaincopyprint?

1. create table sales (year_2008 number,year_2009 number,year_2010 number,year_2011 number,year_2012 number) 
2. insert into salesvalues(191,922,392,942,592) 
3. select * from sales 
4.  
5. create table cow_sales(year_sales number) 
6.  
7. insert all 
8.   into cow_sales values(year_2008) 
9.   into cow_sales values(year_2009) 
10.   into cow_sales values(year_2010) 
11.   into cow_sales values(year_2011) 
12.   into cow_sales values(year_2012) 
13.   select * from sales 
14.  
15. select * from cow_sales 

create table sales (year_2008 number,year_2009 number,year_2010 number,year_2011 number,year_2012 number)insert into sales values(191,922,392,942,592)select * from salescreate table cow_sales(year_sales number)insert all  into cow_sales values(year_2008)  into cow_sales values(year_2009)  into cow_sales values(year_2010)  into cow_sales values(year_2011)  into cow_sales values(year_2012)  select * from salesselect * from cow_sales

原文地址：http://blog.csdn.net/linwaterbin/article/details/7994147