注:测试数据是引用的
测试目的:了解 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语句会被执行