— Start
我估计有点 SQL 基础的人都会写 INSERT 语句。下面是 SQL 标准写法。
INSERT INTO employees (employee_id, name) VALUES (1, 'Zhangsan');
INSERT INTO employees VALUES (1, 'Shangbo');
其实, Oracle 还支持下面的写法,作用和上面的语句完全相同。
INSERT INTO (SELECT employee_id, name FROM employees) VALUES (2, 'Lisi');
此外,同其他数据库一样,Oracle 也支持下面这种写法。
INSERT INTO employees
SELECT 3, 'Wangwu' FROM DUAL;
下面这种写法可以实现列转行,如我们有下面的表存储原始数据,原始数据可能从文件中来。
create table sales_input_table (
prod_id number(9,0),
amt_mon number(9,6),
amt_tue number(9,6),
amt_wed number(9,6),
amt_thu number(9,6),
amt_fri number(9,6)
);
insert into sales_input_table values (1, 100.0, 200.0, 300.0, 400.0, 500.0);
下面我们通过一个 SQL 把上面的数据插入到下面的表中实现列转行。
CREATE TABLE sales (
prod_id number(9,0),
time_id date,
amount number(9,0)
);
INSERT ALL
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE, amt_mon)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 1, amt_tue)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 2, amt_wed)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 3, amt_thu)
INTO sales (prod_id, time_id, amount) VALUES (prod_id, CURRENT_DATE + 4, amt_fri)
SELECT prod_id, amt_mon, amt_tue, amt_wed, amt_thu, amt_fri FROM sales_input_table;
下面这种写法可以帮我们一次性把一个表中的数据倒入到多个表中,否则我们必须写多条 SQL 实现同样的功能。
INSERT ALL
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
注意,当 order_total 大于 200000 时,orders 会被插入到 large_orders 和 special_orders 中。这可能不是你想要的结果,如果你只想让 orders 插入到 special_orders 表中,你只需要把 ALL 替换成 FIRST, 如下。
INSERT FIRST
WHEN order_total <= 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total <= 200000 THEN
INTO medium_orders
WHEN order_total = 500000 THEN
INTO special_orders
WHEN order_total > 200000 THEN
INTO large_orders
ELSE
INTO large_orders
SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
—更多参见:Oracle SQL 精萃
— 声明:转载请注明出处
— Last edited on 2015-06-18
— Created by ShangBo on 2015-06-17
— End
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/160944.html原文链接:https://javaforall.cn