The Oracle Insert All statement describes

Source: Internet
Author: User

For advanced usage of INSERT statements in Oracle, the Insert all statement describes:


1. Insert all inserts unconditionally


CREATE TABLE T1 (product_id number, product_name VARCHAR2, MONTH number), insert into T1 VALUES (111, ' Apple ', 1); INSERT INTO T1 VALUES (222, ' oranges ', 1); INSERT into T1 values (333, ' bananas ', 1); COMMIT; CREATE TABLE T2 as SELECT * from T1 WHERE 1=2;insert all    to 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 Apples 1
111 Apples 2
111 Apples 3
111 Apples 4
222 Oranges 1
222 Oranges 2
222 Oranges 3
222 Oranges 4
333 Banana 1
333 Banana 2
333 Banana 3
333 Banana 4


12 rows have been selected.



2, conditional insert All


CREATE TABLE small_orders (order_id number   () not NULL, customer_id number    (6) is 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   (a)  is not NULL, customer_id number    (6)   is not NULL, Order_ Total number (    8,2), sale_rep_id number    (6), Credit_limit number   (9,2), Cust_email     VARCHAR2 (());  Nsert All to    Order_total < 100000 then to        small_orders when    order_total > 100000 and Order_total < 200000 then to        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 the TRUE,ORACLE server executes the appropriate into clause for the given row,
And skips the later when clause (the subsequent when statement no longer considers the record that satisfies the first when clause, even if the record satisfies the condition in the When statement).


INSERT first when   Ottl < 100000 to      small_orders         VALUES (OID, Ottl, sid, Cid) when   Ottl > 10000  0 and Ottl < 200000 then to      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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.