multitable insert command


		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 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;
 
5,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则检查所有条件；