oracle 多表插入 insert all 用法(即列转行)

建表语句：

 

create table ORDERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);   
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 order_sums(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);  

insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (1001, 20);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (1002, 30);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (800, 5);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (900, 6);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (700, 7);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (10000, 100);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (20000, 200);
insert into ORDERS (CUSTOMER_ID, SUM_ORDERS) values (30000, 300);

insert into ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (1000, 10, 20, 30);
insert into ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (900, 1, 2, 3);
insert into ORDER_SUMS (CUSTOMER_ID, SMALL_SUM_ORDERS, MEDIUM_SUM_ORDERS, LARGE_SUM_ORDERS) values (10000, 100, 200, 300);

 

1.插入到多张表用法

insert all 
  when customer_id < 1000 then 
     into small_customers 
  when customer_id >= 1000 and customer_id < 10000 then 
     into medium_customers 
  else 
     into large_customers
  select customer_id, sum_orders from orders;

 

 

2.多列转一行用法

  insert all 
    into small_customers(customer_id, sum_orders)
      values(customer_id, small_sum_orders) 
    into small_customers(customer_id, sum_orders)
      values(customer_id, medium_sum_orders) 
    into small_customers(customer_id, sum_orders)
      values(customer_id, large_sum_orders)
    select customer_id,
           small_sum_orders,
           medium_sum_orders,
           large_sum_orders
      from order_sums;

 

评论

1 楼 lihao312 2012-10-23  

insert all功能好强大哦 之前都没有用到过