[Oracle] Insert All的妙用

无条件的插入

Oracle中的insert all是指把同一批数据插入到不同的表中，假如现在有个需求：把t表中的数据分别插入t1,t2，如果你不知道insert all，你可能会使用insert插入2次，如下所示：

[sql]  view plain copy print ?

1. insert  into t1(object_name,object_id) select * from t;  
2. insert  into t2(object_name,object_id) select * from t;  
3. commit;  

事实上，以上这种写法是错误的，因为在两次insert的过程中，t表的数据有可能已经发生了变化，也就是说，t1,t2表得到的数据有可能不一样，正确的写法应该是采用insert all：

[sql]  view plain copy print ?

1. insert all  
2. into t1(object_name,object_id)  
3. into t2(object_name,object_id)  
4. select * from t;  
5. commit;  

有条件的插入

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

看下面的例子：

[sql]  view plain copy print ?

1. --insert first  
2. --前面等于1的条件被<=5含在内，FIRST就表示前面插入了，后面不会再插入了。  
3. insert first  
4. when object_id = 1 then  
5. into t1(object_name,object_id)  
6. when object_id <=5 then                        
7. into t2(object_name,object_id)  
8. select * from t;  
9. commit;  
10.   
11. select * from t1;  
12.   
13. OBJECT_NAME                OBJECT_ID  
14. --------------------------------- ---  
15. ICOL$                              1  
16.   
17. select * from t2;  
18.   
19. OBJECT_NAME                OBJECT_ID  
20. --------------------------------- ---  
21. I_USER1                            2  
22. CON$                               3  
23. UNDO$                              4  
24. C_COBJ#                            5  
25.   
26. --insert all  
27. insert all  
28. when object_id = 1 then  
29. into t1(object_name,object_id)  
30. when object_id <=5 then                        
31. into t2(object_name,object_id)  
32. select * from t;  
33. commit;  
34.   
35. SQL> select * from t1;  
36.   
37. OBJECT_NAME                OBJECT_ID  
38. --------------------------------- ---  
39. ICOL$                              1  
40. SQL> select * from t2;  
41.   
42. OBJECT_NAME                OBJECT_ID  
43. --------------------------------- ---  
44. ICOL$                              1  
45. I_USER1                            2  
46. CON$                               3  
47. UNDO$                              4  
48. C_COBJ#                            5  

行转列插入

insert all还可以实现行转列插入：

[sql]  view plain copy print ?

1. select * from sales_source_data;  
2. EMPLOYEE_ID    WEEK_ID  SALES_MON  SALES_TUE  SALES_WED SALES_THUR  SALES_FRI  
3. ----------- ---------- ---------- ---------- ---------- ---------- ----------  
4.         176          6       2000       3000       4000       5000       6000  
5.   
6. insert all  
7. into sales_info values(employee_id,week_id,sales_mon)  
8. into sales_info values(employee_id,week_id,sales_tue)  
9. into sales_info values(employee_id,week_id,sales_wed)  
10. into sales_info values(employee_id,week_id,sales_thur)  
11. into sales_info values(employee_id,week_id,sales_fri)  
12. select employee_id,week_id,sales_mon,sales_tue,  
13. sales_wed,sales_thur,sales_fri  
14. from sales_source_data;  
15.   
16. select * from sales_info;  
17. EMPLOYEE_ID       WEEK      SALES  
18. ----------- ---------- ----------  
19.         176          6       2000  
20.         176          6       3000  
21.         176          6       4000  
22.         176          6       5000  
23.         176          6       6000  

多表插入语句的限制条件

 1. 只能对表执行多表插入语句，不能对视图或物化视图执行；
 2. 不能对远端表执行多表插入语句；
 3. 不能使用表集合表达式；
 4. 不能超过999个目标列；
 5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；
 6. 多表插入语句不支持执行计划稳定性；
 7. 多表插入语句中的子查询不能使用序列。