Oracle insert all語句介紹

來源:互聯網
上載者:User

Oracle 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;

更多詳情見請繼續閱讀下一頁的精彩內容:

  • 1
  • 2
  • 下一頁

相關文章

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.