Introduction to Oracleinsertall statements

In Oracle, the insert ALL statement is described as follows:

1. insert all unconditionally

 

Create table t1 (product_id NUMBER, product_name VARCHAR2 (80), month number); insert into t1 VALUES (111, 'apple', 1); insert into t1 VALUES (222, 'orange', 1); insert into t1 VALUES (333, 'Banana ', 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, MONTHFROM t1; COMMIT; SELECT * FROM t2 order by product_id, product_name, MONTH;

------------------------------
111 Apple 1
111 Apple 2
111 apple 3
111 Apple 4
222 oranges 1
222 oranges 2
222 oranges 3
222 oranges 4
333 bananas 1
333 bananas 2
333 bananas 3
333 bananas 4

12 rows have been selected.

2. Conditional 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. Conditional insert first

If the value of the first WHEN clause is true, the Oracle server executes the corresponding INTO clause for the given row,
And skip the following WHEN clause (the following when statements do not consider meeting the record of the first When clause, even if the record meets the conditions in the when Statement ).

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.