[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/all 是对每一行来进行判断
两者区别:
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

行转列插入

insert all还可以实现行转列插入:
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

[Oracle] Insert All的妙用,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。