Oracle 的 INSERT ALL和INSERT FIRST

    描述性的東西就不來了，搞技術的，最喜歡實在的例項。透過下面的例子，大家很快就能明白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後,凡是符合第一個條件的就都插入第一個表,其他的資料才在以後的條件裡再判斷。