多表插入

注：测试数据是引用的

测试目的：了解 All/First的区别

测试数据：

1、建table

  create table small_customers (customer_id number,sum_orderes number);

  create table medium_customers(customer_id number,sum_orders number);

  create table large_customers(customer_id number,sum_orders number);

  create table orders(customer_id number,order_tatal number);

2、导入测试数据

insert into orders values(1,200);

insert into orders values(1,400);

insert into orders values(2,50000);
insert into orders values(2,80000);
insert into orders values(3,200000);

insert into orders values(3,2000);

insert into orders values(1,8000);

insert into orders values(1,10000);

3、测试过程

  A、关键字用 All

        XSH>insert all
        2   when sum_orders <10000 then
        3    into small_customers
        4    when sum_orders>=8000 and sum_orders<200000 then
        5   into medium_customers
        6    else
        7    into large_customers
        8    select customer_id,order_total sum_orders
        9  from orders
       10  /

        9 rows created.

  结果：

       XSH>select * from small_customers;

       CUSTOMER_ID SUM_ORDERES

- ---------- -----------
          1         200
          1         400
          3        2000
          1        8000

XSH>select * from medium_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          2      50000
          2      80000
          1       8000
          1      10000

  XSH>select * from large_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          3     200000

order_total=8000 的这笔记录 同时满足条件1和条件2，由于关键字用的是All，所以，同时存在于 small_customers和 medium_customers中

B、关键字用 First

     先将测试数据复原

XSH>insert first
  2   when sum_orders <10000 then
  3    into small_customers
  4    when sum_orders>=8000 and sum_orders<200000 then
  5   into medium_customers
  6    else
  7    into large_customers
  8    select customer_id,order_tatal sum_orders
  9  from orders;

8 rows created.

查看结果

XSH>select * from small_customers;

CUSTOMER_ID SUM_ORDERES
----------- -----------
          1         200
          1         400
          3        2000
          1        8000

XSH>select * from medium_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          2      50000
          2      80000
          1      10000

XSH>select * from large_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          3     200000

由于关键字使用了first 所以尽管orders 中order_tatal=8000满足条件1和条件2，但是条件1在前，所以该条记录只insert 到small_customers 中

总结：

 All：所有满足条件的into 语句都会被执行

first：只有满足条件的第一条into语句会被执行