INSERT FIRST和INSERT ALL

原文链接：http://wenku.baidu.com/link?url=_tAqtLjlfF8-C6xDyDif5UpO5MsFQsGT_0igrXvf28ENKpZiZuOwENLWtzn_8vEtokx_suv84EwIFDFHLIbRqkCCEx1NKZpqRKaLBIyZlRO

利用insert first/all使得INSERT语句可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。

语法 ：
insert  ALL | FIRST 
WHEN condition THEN

 insert_into_clause [values_clause] 
[insert_into_clause [values_clause]]... 
[WHEN condition THEN 

insert_into_clause [values_clause] 
[insert_into_clause [values_clause]]... ]... 
[ELSE 

insert_into_clause [values_clause] 
[insert_into_clause [values_clause]]... ] 
insert first/all 是对每一行来进行判断
两者区别：
insert first：对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。
insert all ：对于每一行数据，对每一个when条件都进行检查，如果满足条件就执行插入操作。

实例一
建表：

CREATE TABLE a(a NUMBER,b NUMBER);
CREATE TABLE b AS SELECT * FROM a;
CREATE TABLE c AS SELECT * FROM a;

初始化数据：

INSERT ALL 
       INTO a VALUES(1,0)
       INTO a VALUES(1,1)
       INTO a VALUES(1,2)
       INTO a VALUES(2,0)
       INTO a VALUES(3,0)
SELECT * FROM dual;

INSERT FIRST：

INSERT FIRST
 WHEN a=1 THEN INTO b
 WHEN b=0 THEN INTO c
SELECT * FROM a;

注意c表中没有1 0这条记录。

清表：

TRUNCATE TABLE b;
TRUNCATE TABLE c;

INSERT ALL：

INSERT ALL
 WHEN a=1 THEN INTO b
 WHEN b=0 THEN INTO c
SELECT * FROM a;

注意c表有1 0这条记录。

多表插入语句的限制条件：(摘自http://yangtingkun.itpub.net/post/468/27038)
只能对表执行多表插入语句，不能对视图或物化视图执行；
不能对远端表执行多表插入语句；
不能使用表集合表达式；
不能超过999个目标列；
在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；
多表插入语句不支持执行计划稳定性；
多表插入语句中的子查询不能使用序列。

实例二

原文链接：http://www.2cto.com/database/201210/162824.html

建表：

CREATE TABLE ORDERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);     
CREATE TABLE SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);     
CREATE TABLE MEDIUM_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);   
CREATE TABLE LARGE_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);     
CREATE TABLE ORDER_SUMS(CUSTOMER_ID NUMBER,SMALL_SUM_ORDERS  NUMBER, MEDIUM_SUM_ORDERS  NUMBER, LARGE_SUM_ORDERS  NUMBER);

初始化数据： 

INSERT ALL
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (100, 1) 
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (200, 2) 
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (300, 3)  
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10) 
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (2000, 20)  
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (3000, 30)  
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (10000, 100)
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (20000, 200)
       INTO ORDERS (CUSTOMER_ID, SUM_ORDERS) values (30000, 300)
       INTO ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (100, 1, 2, 3)
       INTO ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (1000, 10, 20, 30)
       INTO ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (10000, 100, 200, 300)
SELECT * FROM dual; 

结果：

插入到多张表

INSERT ALL
 WHEN CUSTOMER_ID < 1000 THEN INTO small_customers
 WHEN CUSTOMER_ID >= 1000 AND CUSTOMER_ID<10000 THEN INTO medium_customers
 ELSE INTO large_customers
SELECT * FROM ORDERS;

结果：

2.列转行 

清表：

TRUNCATE TABLE small_customers;

INSERT ALL 
    INTO small_customers(CUSTOMER_ID, sum_orders) VALUES(CUSTOMER_ID, small_sum_orders)
    INTO small_customers(CUSTOMER_ID, sum_orders) VALUES(CUSTOMER_ID, medium_sum_orders)
    INTO small_customers(CUSTOMER_ID, sum_orders) VALUES(CUSTOMER_ID, large_sum_orders)
SELECT * from order_sums;  

结果：