[Oracle] Merge語句

標籤：word   業務   not   添加   簡單   subquery   value   upd   存在   

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的語句中，顯的比較麻煩並且easy出錯。假設瞭解MERGE，我們能夠不藉助預存程序，直接用單條SQL便實現了該商務邏輯，且代碼非常簡潔。詳細例如以下：

MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYWHEN NOT MATCHED THENINSERTVALUES (T1.NAME,T1.MONEY);

Merge的四大靈活之處上面講了Merge的文法和基本使用方法，其實Merge能夠很靈活。1.UPDATE和INSERT動作可僅僅出現其一(9I必須同一時候出現。)

--我們可選擇只UPDATE目標表MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEY;--也可選擇只INSERT目標表而不做不論什麼UPDATE動作MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN NOT MATCHED THENINSERTVALUES (T1.NAME,T1.MONEY);

2.可對MERGE語句加條件

MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYWHERE T1.NAME='A';

3.可用DELETE子句清除行

/*在這樣的情況下，首先是要先滿足T1.NAME=T2.NAME的記錄，假設T2.NAME=’A’並不滿足T1.NAME=T2.NAME過濾出的記錄集，那這個DELETE是不會生效的。在滿足的條件下，能夠刪除目標表的記錄。*/MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYDELETE WHERE (T2.NAME = 'A');

4.可採用無條件方式Insert

/*方法非常easy，在文法ONkeyword處寫上恒不等條件（如1=2）後，MATCHED語句的INSERT就變為無條件INSERT了，詳細例如以下*/MERGE INTO T2 USING T1 ON (1=2) WHEN NOT MATCHED THEN INSERTVALUES (T1.NAME,T1.MONEY);

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

MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.NAME=T1.NAME;ORA-38104: 無法更新 ON 子句中引用的列: "T2"."NAME"

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

MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEYDELETE 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                            10B                            20SELECT * FROM T2;NAME                      MONEY-------------------- ----------A                            30C                            20MERGE 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                            10B                            20SELECT * FROM T2;NAME                      MONEY-------------------- ----------C                            20

4.更新同一張表的資料,需操心USING的空值

SELECT * FROM T2;NAME                      MONEY-------------------- ----------A                            30C                            20/*需求為對T2表進行自我更新。假設在T2表中發現NAME=D的記錄，就將該記錄的MONEY欄位更新為100，假設NAME=D的記錄不存在，則自己主動添加。NAME=D而且MONEY=100的記錄。依據文法完畢例如以下代碼:*/MERGE INTO T2USING (select * from t2 where NAME='D') TON (T.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=100WHEN NOT MATCHED THENINSERTVALUES ('D',200);--可是查詢發現。本來T表應該由於NAME=D不存在而要添加記錄。可是實際卻根本無變化。SQL> SELECT * FROM T2;NAME                      MONEY-------------------------------------------------------A                            30C                            20/*   原來是由於此時select * from t2 where NAME='D'為NULL,所以出現了無法插入的情況。   我們能夠利用COUNT(*)的值不會為空白的特點來等價改造。詳細例如以下:*/MERGE INTO T2USING (select COUNT(*) CNT from t2 where NAME='D') TON (T.CNT<>0)WHEN MATCHED THENUPDATESET T2.MONEY=100WHEN NOT MATCHED THENINSERTVALUES ('D',100);SQL> SELECT * FROM T2;NAME                      MONEY-------------------------------A                            30C                            20D                           100

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

---構造資料，請注意這裡多插入一條A記錄，就產生了ORA-30926錯誤INSERT INTO T1 VALUES ('A',30);COMMIT;---此時繼續運行例如以下MERGE INTO T2USING T1ON (T1.NAME=T2.NAME)WHEN MATCHED THENUPDATESET T2.MONEY=T1.MONEY+T2.MONEY;ORA-30926: 無法在源表中獲得一組穩定的行/*oracle中的merge語句應該保證on中的條件的唯一性，T1.NAME=T2.NAME的時候。T1表記錄相應到了T2表的兩條記錄，所以就出錯了。
解決方案非常easy。比方我們能夠對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須要引起懷疑，不太應該。
 

[Oracle] Merge語句