程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> [Oracle]Merge語句

[Oracle]Merge語句

編輯：Oracle教程

Merge的語法如下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] 
{ table | view | subquery } [t_alias] ON ( condition ) 
WHEN MATCHED THEN merge_update_clause 
WHEN NOT MATCHED THEN merge_insert_clause;

MERGE是什麼，如何使用呢？讓我們先看一個簡單的需求：

需求是，從T1表更新數據到T2表中，如果T2表的NAME 在T1表中已存在，就將MONEY累加，如果不存在，將T1表的記錄插入到T2表中。

大家知道，在等價的情況下，一定需要至少兩條語句，一條為UPDATE，一條為INSERT,而且語句中必須要與判斷的邏輯，或者寫在過程中，如果是單條語句，就要寫全條件，
寫在UPDATE和INSERT的語句中，顯的比較麻煩而且容易出錯。如果了解MERGE，我們可以不借助存儲過程，直接用單條SQL便實現了該業務邏輯，且代碼很簡潔，具體如下：

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHEN NOT MATCHED THEN
INSERT
VALUES (T1.NAME,T1.MONEY);

Merge的四大靈活之處

上面講了Merge的語法和基本用法，事實上Merge可以非常靈活。 1.UPDATE和INSERT動作可只出現其一(9I必須同時出現！)

--我們可選擇僅僅UPDATE目標表
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;

--也可選擇僅僅INSERT目標表而不做任何UPDATE動作

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN NOT MATCHED THEN
INSERT
VALUES (T1.NAME,T1.MONEY);

2.可對MERGE語句加條件

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
WHERE T1.NAME='A';

3.可用DELETE子句清除行

/*

在這種情況下，首先是要先滿足T1.NAME=T2.NAME的記錄，如果T2.NAME=’A’並不滿足T1.NAME=T2.NAME過濾出的記錄集，
那這個DELETE是不會生效的，在滿足的條件下，可以刪除目標表的記錄。

*/

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (T2.NAME = 'A');

4.可采用無條件方式Insert

/*
方法很簡單，在語法ON關鍵字處寫上恆不等條件（如1=2）後，MATCHED語句的INSERT就變為無條件INSERT了，具體如下
*/

MERGE INTO T2
 USING T1
 ON (1=2)
 WHEN NOT MATCHED THEN
 INSERT
VALUES (T1.NAME,T1.MONEY);

Merge的誤區

1. 不能更新ON子句引用的列

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.NAME=T1.NAME;

ORA-38104: 無法更新 ON 子句中引用的列: "T2"."NAME"

2. DELETE子句的WHERE順序必須最後

MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY
DELETE WHERE (T2.NAME = 'A')
WHERE T1.NAME='A';

ORA-00933: SQL 命令未正確結束

3.DELETE 子句只可以刪除目標表，而無法刪除源表

/*
 這裡需要引起注意，無論DELETE WHERE (T2.NAME = 'A' )這個寫法的T2是否改寫為T1，效果都一樣，都是對目標表進行刪除！
*/

SELECT * FROM T1;
NAME                      MONEY
-------------------- ----------
A                            10
B                            20

SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20

MERGE INTO T2
  USING T1
  ON (T1.NAME=T2.NAME)
  WHEN MATCHED THEN
  UPDATE
  SET T2.MONEY=T1.MONEY+T2.MONEY
  DELETE WHERE (T2.NAME = 'A' );
  
  
SELECT * FROM T1;

NAME                      MONEY
-------------------- ----------
A                            10
B                            20


SELECT * FROM T2;

NAME                      MONEY
-------------------- ----------
C                            20

4.更新同一張表的數據,需擔心USING的空值

SELECT * FROM T2;
NAME                      MONEY
-------------------- ----------
A                            30
C                            20

/*

需求為對T2表進行自我更新，如果在T2表中發現NAME=D的記錄，就將該記錄的MONEY字段更新為100，如果NAME=D的記錄不存在，
則自動增加，NAME=D並且MONEY=100的記錄。根據語法完成如下代碼:

*/

MERGE INTO T2
USING (select * from t2 where NAME='D') T
ON (T.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',200);

--但是查詢發現，本來T表應該因為NAME=D不存在而要增加記錄，但是實際卻根本無變化。
SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------------------------------
A                            30
C                            20

/*
   原來是因為此時select * from t2 where NAME='D'為NULL,所以出現了無法插入的情況，
   我們可以利用COUNT(*)的值不會為空的特點來等價改造，具體如下:
*/

MERGE INTO T2
USING (select COUNT(*) CNT from t2 where NAME='D') T
ON (T.CNT<>0)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=100
WHEN NOT MATCHED THEN
INSERT
VALUES ('D',100);

SQL> SELECT * FROM T2;
NAME                      MONEY
-------------------------------
A                            30
C                            20
D                           100

5. 必須要在源表中獲得一組穩定的行

---構造數據，請注意這裡多插入一條A記錄，就產生了ORA-30926錯誤
INSERT INTO T1 VALUES ('A',30);
COMMIT;

---此時繼續執行如下
MERGE INTO T2
USING T1
ON (T1.NAME=T2.NAME)
WHEN MATCHED THEN
UPDATE
SET T2.MONEY=T1.MONEY+T2.MONEY;
ORA-30926: 無法在源表中獲得一組穩定的行

/*
oracle中的merge語句應該保證on中的條件的唯一性，T1.NAME=T2.NAME的時候，T1表記錄對應到了T2表的兩條記錄，所以就出錯了。
解決方法很簡單，比如我們可以對T1表和T2表的關聯字段建主還鍵，這樣基本上就不可能出現這樣的問題，而且一般而言，MERGE語句的關聯字段互相有主鍵，
MERGE的效率將比較高！或者是將T1表的ID列做一個聚合，這樣歸並成單條，也能避免此類錯誤。如：
*/   

MERGE INTO T2
  USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1
  ON (T1.NAME=T2.NAME)
  WHEN MATCHED THEN
  UPDATE
  SET T2.MONEY=T1.MONEY+T2.MONEY;

--正常情況下，一般出現重復的NAME需要引起懷疑，不太應該。