INSERT FIRST
同时插入到多张表，在这里整理下，以供查阅。先建两个测试表，ww1，ww2
SQL> desc ww1
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)
SQL> desc ww2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)
 TABLESPACENAME                                     VARCHAR2(30)
无条件INSERT，同时向这两表插入数据
insert all
 into ww1 values( object_id,object_name)
 into ww2 values(object_id,object_name,object_type)
 select objecT_id,object_name,object_type
 from user_objects
 where rownum<20
   /
已创建38行。
SQL> select count(*) from ww1;
  COUNT(*)
----------
        19
可以看到总共插入了38行，每个表插入了19行
条件INSERT，当每个不同条件成立的时候插入不同表
insert all
when object_type='TABLE' then into ww1 values (object_id,object_name)
when object_type='SYNONYM' then into ww2 values (objecT_id,object_name,object_type)
select object_id,object_name,object_type
from user_objects
  /
已创建727行。
SQL> select count(*) from ww1;
  COUNT(*)
----------
       718
SQL> select count(*) from ww2;
  COUNT(*)
----------
         9
可以看到，符合object_type='TABLE'的718条记录被插入到ww1表中，符合object_type='SYNONYM'的9条记录被插入到ww2表中，总共插入了718条记录。
条件FIRST INSERT
  1  select objecT_id ,object_type from user_objects
  2* where object_id<10
  3  /
 OBJECT_ID OBJECT_TYPE
---------- --------------------------------------
         2 CLUSTER
         3 INDEX
         4 TABLE
         5 TABLE
         6 CLUSTER
         7 INDEX
         8 CLUSTER
         9 INDEX
看到前9条记录中,INDEX 有4条.于是写如下SQL测试
insert first
when object_type='INDEX' then into ww values (object_id)
 when object_id<10 then into ww1 values (object_id,object_name)
    select object_id,object_name,object_type
   from user_objects
   /
已创建715行。
SQL> select count(*) from ww;
  COUNT(*)
----------
       710
SQL> select * from ww1;
        ID NAME
---------- ---------------------
         6 C_TS#
         5 CLU$
         8 C_FILE#_BLOCK#
         2 C_OBJ#
         4 TAB$
可以看到,用FIRST后,凡是符合第一个条件的就都插入第一个表,其他的数据才再以后的条件里再判断.
用这种写法,也可以达到行转列的目的.
TINSERT 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;