描述性的东西就不来了，搞技术的，最喜欢实在的实例。通过下面的例子，大家很快就能明白insert all 与 insert first 的功能，比文字描述更通俗易懂。
一、INSERT ALL 不带条件用法
SQL> create table t_table1(tid number,tname varchar(100));
Table created
SQL> create table t_table2(tid number,tname varchar(100));
Table created
SQL> insert all into t_table1
2 (tid, tname)
3 values
4 (object_id, object_name) into t_table2
5 (tid, tname)
6 values
7 (object_id, object_name)
8
select object_id, object_name, object_type
9 from
dba_objects
10 where wner = ‘TEST‘;
8440 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_table1;
COUNT(1)
----------
4220
SQL> select count(1) from t_table2;
COUNT(1)
----------
4220
SQL>
指定所有跟随的多表，都执行无条件的多表插入；
二、INSERT ALL 带条件用法
SQL> create table t_table(tid number,tname varchar(100));
Table created
SQL> create table t_index(iid number,iname varchar(100));
Table created
SQL> create table t_other(oid number,oname varchar(100));
Table created
SQL> insert all when object_type =
‘TABLE‘ then into t_table
2 (tid, tname)
3 values
4 (object_id, object_name) when
object_type = ‘INDEX‘ then into t_index
5 (iid,
iname)
6 values
7 (object_id,
object_name) else into t_other
8 (oid,
oname)
9 values
10 (object_id,
object_name)
11 select object_id, object_name,
object_type
12 from
dba_objects
13 where wner = ‘TEST‘;
4220 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_table;
COUNT(1)
----------
1025
SQL> select count(1) from t_index;
COUNT(1)
----------
1582
SQL> select count(1) from t_other;
COUNT(1)
----------
1613
SQL>
Oracle服务器通过相应的WHEN条件过滤，将查询结果分别插入到满足条件的表中；
三、INSERT FIRST 用法
SQL> create table t_table1(tid number,tname varchar(100));
Table created
SQL> create table t_table2(tid number,tname varchar(100));
Table created
SQL> create table t_table3(tid number,tname varchar(100));
Table created
SQL> insert first when object_id <
88554 then into t_table1
2 (tid, tname)
3 values
4 (object_id, object_name) when
object_id < 189490 then into t_table2
5 (tid,
tname)
6 values
7 (object_id,
object_name) else into t_table3
8 (tid,
tname)
9 values
10 (object_id,
object_name)
11 select object_id, object_name,
object_type
12 from
dba_objects
13 where wner = ‘TEST‘;
4220 rows inserted
SQL> commit;
Commit complete
SQL> select count(1) from t_table1;
COUNT(1)
----------
860
SQL> select count(1) from t_table2;
COUNT(1)
----------
2327
SQL> select count(1) from t_table3;
COUNT(1)
----------
1033
SQL>
可以看到,用FIRST后,凡是符合第一个条件的就都插入第一个表,其他的数据才在以后的条件里再判断。
Oracle 的 INSERT ALL和INSERT FIRST,布布扣,bubuko.com
Oracle 的 INSERT ALL和INSERT FIRST
原文：http://www.cnblogs.com/heidsoft/p/3570345.html