Oracle 的 INSERT ALL和INSERT FIRST

fbug

描述性的东西就不来了，搞技术的，最喜欢实在的实例。通过下面的例子，大家很快就能明白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后,凡是符合第一个条件的就都插入第一个表,其他的数据才在以后的条件里再判断。