Oracle-Insert all语句学习

jxzkin

于 2012-12-24 11:49:21 发布

阅读量661

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle SQL

本文链接：https://blog.csdn.net/jxzkin/article/details/8424259

Oracle SQL 专栏收录该内容

8 篇文章

订阅专栏

本文通过创建不同规模客户的表格并使用SQL的insert all when条件语句来演示如何根据订单金额大小将数据分配到不同的客户级别表中。

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

1.测试数据

create table small_customers
(customer_id number,
 orders		 number);


create table medium_customers
(customer_id number,
 orders		 number);
 
create table large_customers
(customer_id number,
 orders		 number);


 
create table orders
(customer_id number,
 sum_orders	 number);


insert into orders values(1,9000);
insert into orders values(1,10000);
insert into orders values(1,100000);
commit;


kin@KIN>select * from orders;


CUSTOMER_ID SUM_ORDERS
----------- ----------
	  1	  9000
	  1	 10000
	  1	100000

2.测试语句

insert all
when sum_orders < 10000 then
into small_customers
when sum_orders >=10000 and sum_orders < 100000 then
into medium_customers
else
into large_customers
select * from orders;


kin@KIN>select * from small_customers;


CUSTOMER_ID	ORDERS
----------- ----------
	  1	  9000


kin@KIN>select * from medium_customers;


CUSTOMER_ID	ORDERS
----------- ----------
	  1	 10000


kin@KIN>select * from large_customers


CUSTOMER_ID	ORDERS
----------- ----------
	  1	100000