multitable insert command

conn hr/hr

1，建立測試表

SQL> create table small_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));

Table created.

SQL> create table medium_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));

Table created.

SQL> create table large_orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));

Table created.

SQL> create table orders(order_id int, order_total number, sales_rep_id varchar2(4), customer_id varchar2(10));

Table created.

 

2，插入測試資料：

insert into orders(order_id,order_total,sales_rep_id,customer_id) values(1,1000,'0001','0000000001');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(2,2000,'0002','0000000002');

insert into orders(order_id,order_total,sales_rep_id,customer_id) values(3,10000,'0003','0000000003');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(4,20000,'0004','0000000004');

insert into orders(order_id,order_total,sales_rep_id,customer_id) values(5,100000,'0005','0000000005');
insert into orders(order_id,order_total,sales_rep_id,customer_id) values(6,200000,'0006','0000000006');

 

3，有條件的多表插入(conditional insert all)：

INSERT ALL
   WHEN order_total < 10000 THEN
      INTO small_orders
   WHEN order_total >= 10000 AND order_total < 100000 THEN
      INTO medium_orders
   WHEN order_total >=100000 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

 

4，無條件多表插入

INSERT ALL
       INTO small_orders
      INTO medium_orders
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

 

 

,5，Insert First

SQL> delete from small_orders;

2 rows deleted.

SQL> delete from medium_orders;

2 rows deleted.

SQL> delete from large_orders;

2 rows deleted.

 

INSERT FIRST
   WHEN ottl < 10000 THEN
      INTO small_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl >= 10000 and ottl < 100000 THEN
      INTO medium_orders
         VALUES(oid, ottl, sid, cid)
   WHEN ottl >= 100000 THEN
      INTO large_orders
         VALUES(oid, ottl, sid, cid)
   SELECT order_id oid, order_total ottl, sales_rep_id sid, customer_id cid
      FROM orders;

 

6,The difference between insert all and insert first

INSERT ALL
   WHEN order_total < 1000000 THEN
      INTO small_orders
   WHEN  order_total < 1000000 THEN
      INTO medium_orders
   WHEN order_total >=10 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

18 rows created.

三個表都將插入6條記錄

INSERT FIRST
   WHEN order_total < 1000000 THEN
      INTO small_orders
   WHEN  order_total < 1000000 THEN
      INTO medium_orders
   WHEN order_total >=10 THEN
      INTO large_orders
   SELECT order_id, order_total, sales_rep_id, customer_id
      FROM orders;

6 rows created.

只有第一個表small_orders插入了6條記錄，其他的兩個表沒有被插入記錄。

 

從上面的兩個例子可以看出INSERT FIRST與INSERT ALL的區別：
INSERT FIRST只檢查第一個條件，如果第一個條件滿足，雖然第二個條件也滿足也不會去檢查，除非第一個條件不滿足的情況，才會檢查第二個條件；
INSERT ALL則檢查所有條件；