Oracle選擇插入資料


在本教學中,您將學習如何使用Oracle INSERT INTO SELECT語句將資料從SELECT語句的結果中插入到表中。

Oracle INSERT INTO SELECT語句概述

有時候,想要將從其它表中選擇資料並將其插入到另一個表中。要做到這一點,可使用Oracle INSERT INTO SELECT語句,如下所示:

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;

Oracle INSERT INTO SELECT語句要求源表和目標表匹配資料型別。

Oracle INSERT INTO SELECT範例

1. 插入所有銷售資料範例

下面了演示如何使用insert into select語句,首先建立一個名為sales的表。

CREATE TABLE sales (
    customer_id NUMBER,
    product_id NUMBER,
    order_date DATE NOT NULL,
    total NUMBER(9,2) DEFAULT 0 NOT NULL,
    PRIMARY KEY(customer_id,
                product_id,
                order_date)
);

以下語句將ordersorder_items表中的銷售摘要插入到sales表中,參考以下實現語句 -

INSERT INTO  sales(customer_id, product_id, order_date, total)
SELECT customer_id,
       product_id,
       order_date,
       SUM(quantity * unit_price) amount
FROM orders
INNER JOIN order_items USING(order_id)
WHERE status = 'Shipped'
GROUP BY customer_id,
         product_id,
         order_date;

以下語句從sales表中檢索資料以驗證插入結果:

SELECT *
FROM sales
ORDER BY order_date DESC,
         total DESC;

執行上面查詢語句,得到以下結果 -

2. 插入部分銷售資料範例

假設只想將2017年的銷售摘要資料複製到新表中。 為此,首先建立一個名為sales_2017的新表,如下所示:

CREATE TABLE sales_2017 
AS SELECT
    *
FROM
    sales
WHERE
    1 = 0;

WHERE子句中的條件是確保sales表中的資料不會被複製到sales_2017表中。

其次,使用Oracle INSERT INTO SELECTWHERE子句將2017年的銷售資料複製到sales_2017表中:

INSERT INTO  sales_2017
    SELECT customer_id,
           product_id,
           order_date,
           SUM(quantity * unit_price) amount
    FROM orders
    INNER JOIN order_items USING(order_id)
    WHERE status = 'Shipped' AND EXTRACT(year from order_date) = 2017        
    GROUP BY customer_id,
             product_id,
             order_date;

在此範例中,沒有在INSERT INTO子句中指定列列表,因為SELECT語句的結果具有與sales_2017表的列對應的值。 另外,在SELECT語句的WHERE子句中新增了更多的條件,以在2017年僅檢索銷售資料。

以下查詢選擇sales_2017表中的所有資料:

SELECT *
FROM sales_2017
ORDER BY order_date DESC,
         total DESC;

執行上面查詢語句,得到以下結果 -

3. 插入部分資料和文字值範例

假設,想要傳送電子郵件給所有客戶告知新產品上市。 要做到這一點,可以將客戶資料複製到單獨的表並跟蹤電子郵件傳送狀態。

首先,建立一個名為customer_lists的新表,如下所示:

-- oracle 12c寫法
CREATE TABLE customer_lists(
    list_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name varchar2(255) NOT NULL,
    last_name varchar2(255) NOT NULL,
    email varchar2(255) NOT NULL,
    sent NUMBER(1) NOT NULL,
    sent_date DATE,
    PRIMARY KEY(list_id)
);

-- oracle 11g寫法
drop sequence customer_lists_seq;
create sequence customer_lists_seq
 increment by 1
 start with 1
 maxvalue 9999999999
 nocache;

CREATE TABLE customer_lists(
    list_id NUMBER,
    first_name varchar2(255) NOT NULL,
    last_name varchar2(255) NOT NULL,
    email varchar2(255) NOT NULL,
    sent NUMBER(1) NOT NULL,
    sent_date DATE,
    PRIMARY KEY(list_id)
);

其次,將contacts表中的資料複製到customer_lists表中:

-- oracle 12c寫法
INSERT INTO
        customer_lists(
            first_name,
            last_name,
            email,
            sent
        ) SELECT
            first_name,
            last_name,
            email,
            0
        FROM
            contacts;

-- oracle 11g寫法
INSERT INTO
        customer_lists(
            list_id,
            first_name,
            last_name,
            email,
            sent
        ) SELECT
            customer_lists_seq.nextval,
            first_name,
            last_name,
            email,
            0
        FROM
            contacts;

在這個例子中,除了從contacts表中檢索資料之外,我們還使用文字值:0 作為sent列的初始值。

以下查詢從customer_lists表中檢索資料:

請注意,這個例子只是為了演示,可以將DEFAULT 0新增到sent列的定義中。

在本教學中,您已經學習了如何使用Oracle INSERT INTO SELECT語句將查詢結果向其它表中插入資料。