Oracleinsertall语句介绍

Oracle 中insert语句的高级用法，INSERT ALL 语句介绍：

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行。

Oracle- insert性能优化

Oracle数据库中无法对数据表进行insert和update操作解决

Oracle数据库中无法对数据表进行insert和update操作解决

Oracle insert的扩展

Oracle的多表insert操作

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;

更多详情见请继续阅读下一页的精彩内容：