大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
Oracle 中insert语句的高级用法，INSERT ALL 语句介绍：
1、无条件insert all 全部插入
CREATE TABLE t1(product_id NUMBER, product_name VARCHAR2(80),MONTH NUMBER);
INSERT INTO t1 VALUES(111, '苹果',1);
INSERT INTO t1 VALUES(222, '橘子',1);
INSERT INTO t1 VALUES(333, '香蕉',1);
COMMIT;
CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1=2;
INSERT ALL
INTO t2
VALUES (product_id, product_name,MONTH)
INTO t2
VALUES (product_id, product_name,MONTH+1)
INTO t2
VALUES (product_id, product_name,MONTH+2)
INTO t2
VALUES (product_id, product_name,MONTH+3)
SELECT product_id, product_name, MONTH
FROM t1;
COMMIT;
SELECT * FROM t2 ORDER BY product_id, product_name, MONTH;———- ———- ———-
111 苹果 1
111 苹果 2
111 苹果 3
111 苹果 4
222 橘子 1
222 橘子 2
222 橘子 3
222 橘子 4
333 香蕉 1
333 香蕉 2
333 香蕉 3
333 香蕉 4
已选择12行。
2、有条件insert all
CREATE TABLE small_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sale_rep_id NUMBER(6)
);
CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE TABLE large_orders AS SELECT * FROM small_orders;
CREATE TABLE special_orders
(order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
order_total NUMBER(8,2),
sale_rep_id NUMBER(6),
credit_limit NUMBER(9,2),
cust_email VARCHAR2(30)
);
INSERT ALL
WHEN order_total < 100000 THEN
INTO small_orders
WHEN order_total > 100000 AND order_total < 200000 THEN
INTO medium_orders
ELSE
INTO large_orders
SELECT order_id, customer_id, order_total, sales_rep_id
FROM orders;
3、有条件insert first
如果第一个 WHEN 子句的值为 true，Oracle 服务器对于给定的行执行相应的 INTO 子句，
并且跳过后面的 WHEN 子句(后面的when语句都不再考虑满足第一个When子句的记录，即使该记录满足when语句中的条件)。
INSERT FIRST
WHEN ottl < 100000 THEN
INTO small_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 100000 and ottl < 200000 THEN
INTO medium_orders
VALUES(oid, ottl, sid, cid)
WHEN ottl > 290000 THEN
INTO special_orders
WHEN ottl > 200000 THEN
INTO large_orders
VALUES(oid, ottl, sid, cid)
SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem
FROM orders o, customers c
WHERE o.customer_id = c.customer_id;
SELECT * FROM small_orders;
SELECT * FROM medium_orders;
SELECT * FROM large_orders;
SELECT * FROM special_orders;———————————
By Dylan.
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/160958.html原文链接：https://javaforall.net
