Oracle合併資料 - tw511教學網

在本教學中將學習如何使用Oracle MERGE語句來執行更新或基於指定條件插入資料。

Oracle MERGE語句簡介

Oracle MERGE語句從一個或多個源表中選擇資料並更新或將其插入到目標表中。 MERGE語句可指定一個條件來確定是更新資料還是將資料插入到目標表中。

以下說明了Oracle MERGE語句的語法：

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

下面來仔細看看上面MERGE語句的語法：

首先，指定要在INTO子句中更新或插入的目標表(target_table)。
其次，指定要更新或插入USING子句中的資料源(source_table)。
第三，指定合併操作在ON子句中更新或插入的搜尋條件。

對於目標表中的每一行，Oracle都會評估搜尋條件：

如果結果為true，則Oracle使用源表(source_table)中的相應資料更新該行。
如果任何行的結果為false，則Oracle將源表(source_table)中相應的行插入到目標表(target_table)中。

當想要在單個操作中組合多個INSERT，UPDATE和DELETE語句時，MERGE語句變得很方便。

因為MERGE是確定性語句，所以不能在同一個MERGE語句中多次更新目標表的同一行。

可以將一個可選的DELETE WHERE子句新增到MATCHED子句中，以在合併操作之後進行清理。 DELETE子句只刪除目標表中與ON和DELETE WHERE子句匹配的行。

Oracle MERGE前提條件

要執行MERGE語句，必須在源表上具有INSERT和UPDATE物件許可權。如果使用DELETE子句，則還必須在目標表上具有DELETE物件特權。

Oracle MERGE範例

假設有兩個表：members和member_staging。

每當有一個新會員資訊時，則插入一個新的行記錄到members表。然後，members表中的資料將與member_staging表的資料合併。

以下語句用於建立members和member_staging表：

CREATE TABLE members (
    member_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    rank VARCHAR2(20)
);

CREATE TABLE member_staging AS 
SELECT * FROM members;

使用以下INSERT語句將範例資料插入到members和member_staging表中：

-- insert into members table    
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Gold');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Suarez','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Garza','Silver');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(7,'Ossie','Summers','Gold');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(8,'Paige','Mcfarland','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(9,'Ronna','Britt','Platinum');
INSERT INTO members(member_id, first_name, last_name, rank) VALUES(10,'Tressie','Short','Bronze');

-- insert into member_staging table
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(1,'Abel','Wolf','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(2,'Clarita','Franco','Platinum');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(3,'Darryl','Giles','Bronze');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(4,'Dorthea','Gate','Gold');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(5,'Katrina','Wheeler','Silver');
INSERT INTO member_staging(member_id, first_name, last_name, rank) VALUES(6,'Lilian','Stark','Silver');

將members表中的資料更新到member_staging表時，應該執行以下操作：

更新成員id為1,3,4和6的行記錄，因為表中這些成員的排名或姓氏是不同的。
要插入成員id為7到10的行記錄，這是因為這些行存在於members表中，但不存在於member_staging表中。

總共有8行資料需要合併。參考以下圖示 -

以下是一次性執行所有這些操作的MERGE語句。

MERGE INTO member_staging x
USING (SELECT member_id, first_name, last_name, rank FROM members) y
ON (x.member_id  = y.member_id)
WHEN MATCHED THEN
    UPDATE SET x.first_name = y.first_name, 
                        x.last_name = y.last_name, 
                        x.rank = y.rank
    WHERE x.first_name <> y.first_name OR 
           x.last_name <> y.last_name OR 
           x.rank <> y.rank 
WHEN NOT MATCHED THEN
    INSERT(x.member_id, x.first_name, x.last_name, x.rank)  
    VALUES(y.member_id, y.first_name, y.last_name, y.rank);

merge語句根據member_id列中的值(參見上面的ON子句)，將member表中的每一行與member_staging表中的每一行進行比較。

如果兩個表的member_id列中的值相等，MERGE語句只有在兩個表的first_name,last_name或rank列的不值時，才將members表中的first_name,last_name或rank列的不值作為member_staging對應列的值來更新，否則它將members的行直接插入member_staging表。

Oracle按照預期返回了8行合併資料。

在本教學中，您已學習如何使用Oracle MERGE語句根據指定的條件更新或插入資料。