[Oracle] Insert All神奇
无条件插入
Oracle中间insert all它指的是相同的数据组成不同的表。如果有需求现在:该t插入数据表t1,t2,假设你不知道insert all。您可以使用insert插入2次要,例如,见下文:
insert into t1(object_name,object_id) select * from t; insert into t2(object_name,object_id) select * from t; commit;其实。以上这样的写法是错误的,由于在两次insert的过程中,t表的数据有可能已经发生了变化,也就是说。t1,t2表得到的数据有可能不一样。正确的写法应该是採用insert all:
insert all into t1(object_name,object_id) into t2(object_name,object_id) select * from t; commit;
有条件的插入
两者差别:
insert first:对于每一行数据,仅仅插入到第一个when条件成立的表,不继续检查其它条件。
insert all : 对于每一行数据,对每个when条件都进行检查。假设满足条件就运行插入操作。
--insert first --前面等于1的条件被<=5含在内,FIRST就表示前面插入了。后面不会再插入了。insert first when object_id = 1 then into t1(object_name,object_id) when object_id <=5 then into t2(object_name,object_id) select * from t; commit; select * from t1; OBJECT_NAME OBJECT_ID --------------------------------- --- ICOL$ 1 select * from t2; OBJECT_NAME OBJECT_ID --------------------------------- --- I_USER1 2 CON$ 3 UNDO$ 4 C_COBJ# 5 --insert all insert all when object_id = 1 then into t1(object_name,object_id) when object_id <=5 then into t2(object_name,object_id) select * from t; commit; SQL> select * from t1; OBJECT_NAME OBJECT_ID --------------------------------- --- ICOL$ 1 SQL> select * from t2; OBJECT_NAME OBJECT_ID --------------------------------- --- ICOL$ 1 I_USER1 2 CON$ 3 UNDO$ 4 C_COBJ# 5
行转列插入
select * from sales_source_data; EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI ----------- ---------- ---------- ---------- ---------- ---------- ---------- 176 6 2000 3000 4000 5000 6000 insert all into sales_info values(employee_id,week_id,sales_mon) into sales_info values(employee_id,week_id,sales_tue) into sales_info values(employee_id,week_id,sales_wed) into sales_info values(employee_id,week_id,sales_thur) into sales_info values(employee_id,week_id,sales_fri) select employee_id,week_id,sales_mon,sales_tue, sales_wed,sales_thur,sales_fri from sales_source_data; select * from sales_info; EMPLOYEE_ID WEEK SALES ----------- ---------- ---------- 176 6 2000 176 6 3000 176 6 4000 176 6 5000 176 6 6000
多表插入语句的限制条件
1. 仅仅能对表运行多表插入语句,不能对视图或物化视图运行;2. 不能对远端表运行多表插入语句。
3. 不能使用表集合表达式。
4. 不能超过999个目标列;
5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时。多表插入语句不能并行运行;
6. 多表插入语句不支持运行计划稳定性;
7. 多表插入语句不能在子查询序列被用于。
版权声明:本文博客原创文章,博客,未经同意,不得转载。