ocp 试题学习中 _转子查询返回的数据行是如何用来插入多个表中-优快云博客

本文讨论了如何使用子查询和条件插入语句优化多表数据的批量插入过程，通过实例展示了如何将子查询结果高效地分配到不同的目标表中。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://www.itpub.net/thread-1802215-1-1.html  转


多表插入――有条件的insert all [复制链接]

子查询返回的数据行是如何用来插入多个表中
small_customers
medium_customers
large_customers

create table small_customers(customer_id number,sum_orders 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_total number);

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 all
when sum_orders < 10000 then
into small_customers
when sum_orders >= 10000 and sum_orders < 200000 then
into medium_customers
else
into large_customers
select customer_id,sum(order_total) sum_orders
from orders
group by customer_id;
commit;

SQL> select * from small_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          1        600

SQL> select * from medium_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          2     130000

SQL> select * from large_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
          3     202000