oracle數(shù)據(jù)匹配merge into的實例詳解

前言：

 很久之前，估計在2010年左右在使用Oralce，當(dāng)時有個需求就是需要對兩個表的數(shù)據(jù)進行匹配，這兩個表的數(shù)據(jù)結(jié)構(gòu)一致，一個是正式表，一個是臨時表，這兩表數(shù)據(jù)量還算是比較大幾百M。業(yè)務(wù)需求是用臨時表中的數(shù)據(jù)和正式表的匹配，所有字段都需要一一匹配，而且兩表還沒有主鍵，這是一個比較麻煩和糟糕的事情。

場景：

1、如果兩表所有字段值都一致則不處理；
2、如果有部分字段不一致則更新；
3、如果正式表中數(shù)據(jù)在臨時表中不存在，則需要刪除；

滿足上面場景的三個功能即可，可以用程序如(java,C,C#)等各種實現(xiàn)，也可以使用存儲過程oracle實現(xiàn)；

考慮之后用程序來做，這肯定是可以實現(xiàn)，但是撇開繁瑣的數(shù)據(jù)讀取、操作sql之外，還需要匹配，而且效率也是個問題；就決定用存儲過程來實現(xiàn)，先前是使用了if exists來匹配。后面發(fā)現(xiàn)效率不高，比較慢；后續(xù)改進之后引入了Oracle merge into來實現(xiàn)；

具體事例代碼(字段刪除了大部分)如下：

1、對臨時表L_TABLE中數(shù)據(jù)與正式表Z_TABLE比較，如果各個字段值比較不相等則認為臨時表的這行數(shù)據(jù)是新增的，然后插入到正式表中。

merge into Z_TABLE t1   using (     select        S_SYSTEM_ID,       S_PORT_ID,       S_SYSTEM_NAME    where S_SYSTEM_NAME = "廣東"    from L_TABLE   ) t2   on(   t1.S_PORT_ID=t2.S_PORT_ID and t1.S_SYSTEM_ID=t2.S_SYSTEM_ID and nvl(t1.S_SYSTEM_NAME,'1')=nvl(t2.S_SYSTEM_NAME,'1')   )   WHEN NOT matched THEN   INSERT (    S_SYSTEM_ID,    S_PORT_ID,    S_SYSTEM_NAME    )     VALUES (    t2.S_SYSTEM_ID, t2.S_PORT_ID,t2.S_SYSTEM_NAME    )  

注：

1）上面代碼on(nvl(t1.S_SYSTEM_NAME,'1')=nvl(t2.S_SYSTEM_NAME,'1')，用了nvl函數(shù) 這個地方需要特別注意，當(dāng)初測試的時候發(fā)現(xiàn)有些字段為空null，null和null之前是不能用相等來處理的，所以對于那些為null或者“”的字段統(tǒng)一轉(zhuǎn)為一個字符串來做匹配，當(dāng)作相等，要不然會出現(xiàn)匹配不上的問題。

2）在查詢臨時表的時候加了個條件約束where S_SYSTEM_NAME = "廣東";之前的處理是全量的，查詢整個大表，后面發(fā)現(xiàn)效率不是非常完美；加入條件判斷之后相當(dāng)于是每次只去少量的數(shù)據(jù)用來和正式表做匹配，如此便可以減輕數(shù)據(jù)庫臨時空間的負擔(dān)，多循環(huán)幾個城市省份分別去做，可以加快速度。

2、刪除正式表中Z_TABLE中多余的數(shù)據(jù)，最后臨時表和正式表數(shù)據(jù)量相等；

在之前第1步的時候如果有一行數(shù)據(jù)的某個字段不相同的話，是會重新插入一行數(shù)據(jù)到正式表中，而不是更新，所以正式表中會多出一些多余的數(shù)據(jù)也就是和臨時表不完全相等的數(shù)據(jù)。

merge into Z_TABLE t1   using (     select S_SYSTEM_ID, S_PORT_ID,S_SYSTEM_NAME from Z_TABLE   minus     select S_SYSTEM_ID, S_PORT_ID,S_SYSTEM_NAME from L_TABLE   ) t2   on (   t1.S_PORT_ID=t2.S_PORT_ID and t1.S_SYSTEM_ID=t2.S_SYSTEM_ID and nvl(t1.S_SYSTEM_NAME,'1')=nvl(t2.S_SYSTEM_NAME,'1')   )   when matched then    update set t1.additionalInfo = 'del'    delete WHERE t1.additionalInfo = 'del' 

上面sql意思是挑選出正式表和臨時表中的差異數(shù)據(jù)(包括各個字段值的差異),這里用了Oracle的minus函數(shù)進行刷選，其實這部分就是需要刪除的數(shù)據(jù)，將這部分數(shù)據(jù)集合拿去和正式表中進行匹配，如果在正式表中存在則更新刪除標(biāo)識，進行刪除。 

注：

因為兩表都是沒有主鍵，所以無法使用唯一判斷用來刪除作為刪除依據(jù)，而且merge into的語句測試了下delete必須要跟在update后，這點比較糾結(jié)，所以只能給正式表加一個擴展字段additionalInfo 來作為刪除的標(biāo)記，以便刪除處理。

通過以上兩個步驟，正式表和臨時表的數(shù)據(jù)已同步完成！

如有疑問請留言或者到本站社區(qū)交流討論，感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！