作者：faihiwang | 来源：互联网 | 2014-07-09 16:02
oracle多表插入insertall用法(即列转行)建表语句：Sql代码createtableORDERS(CUSTOMER_IDNUMBER,SUM_ORDERSNUMBER);createtablesmall_customers(CUSTOMER_IDNUMBER,SUM_ORDERSNUMBER);...
oracle多表插入insert all用法(即列转行)
建表语句：
Sql代码
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);
Sql代码
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);
www.2cto.com
Sql代码
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.插入到多张表用法
Sql代码
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;
www.2cto.com
2.多列转一行用法
Sql代码
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;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有