insert first和insertall的区别

insert first 和insert all

第一个区别就是

insert all 后面可以不用加条件判断语句

但是insert first 后面必须加条件 判断语句：

两张例子表：

SCOTT>create table t1 (id number(30),name varchar2(100));


Table created.


SCOTT>create table t2 (id number(30),name varchar2(100));

INSERT ALL：

SCOTT>insert all
  2  into t1 
  3  into t2
  4  select object_id,object_name from all_objects where rownum<20;


38 rows created.

INSERT FIRST:

SCOTT>insert first
  2  into t1
  3  into t2
  4  select object_id,object_name from all_objects where rownum<20;
into t1
*
ERROR at line 2:
ORA-00905: missing keyword

INSERT ALL 可以没有when 条件限制 但是INSERT FIRST 不可以；

第二个区别：

INSERT ALL:

insert all
   when mod(rn,2)=0 then 
  into t1
  when mod(rn,2)=0 then
 into t2
  6   select rownum rn ,object_name from all_objects where rownum<=20;


20 rows created.


SCOTT>select * from t1;
please input enter 


ID NAME
---------- ----------------------------------------------------------------------------------------------------
2 I_USER1
4 UNDO$
6 I_OBJ#
8 I_IND1
10 I_OBJ5
12 FILE$
14 I_FILE#_BLOCK#
16 I_CON1
18 I_TS#
20 IND$


10 rows selected.


SCOTT>select * from t2;
please input enter 


ID NAME
---------- ----------------------------------------------------------------------------------------------------
2 I_USER1
4 UNDO$
6 I_OBJ#
8 I_IND1
10 I_OBJ5
12 FILE$
14 I_FILE#_BLOCK#
16 I_CON1
18 I_TS#
20 IND$


10 rows selected.

INSERT FIRST：

SCOTT>insert first
  2  when mod(rn,2)=0 then
 into t1
  when mod(rn,2)=0 then
 into t2
  6   select rownum rn ,object_name from all_objects where rownum<=20;


10 rows created.


SCOTT>select * from t1;
please input enter 


ID NAME
---------- ----------------------------------------------------------------------------------------------------
2 I_USER1
4 UNDO$
6 I_OBJ#
8 I_IND1
10 I_OBJ5
12 FILE$
14 I_FILE#_BLOCK#
16 I_CON1
18 I_TS#
20 IND$


10 rows selected.


SCOTT>select * from t2;


no rows selected

可以看出当when后面的条件一直的时候 all是会对插入的每个表做判断，而first中只会把匹配的结果放在第一个表中 这里就是t1中。

当when的条件不一致的话 first和all的结果是一致的。