Oracleinsertall語句介紹

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.