oracle數據匹配merge into的實例詳解

oracle數據匹配merge into的實例詳解

前言：

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

場景：

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

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

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

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

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

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函數 這個地方需要特別注意，當初測試的時候發現有些字段為空null，null和null之前是不能用相等來處理的，所以對于那些為null或者“”的字段統一轉為一個字符串來做匹配，當作相等，要不然會出現匹配不上的問題。

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

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

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

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意思是挑選出正式表和臨時表中的差異數據(包括各個字段值的差異),這里用了Oracle的minus函數進行刷選，其實這部分就是需要刪除的數據，將這部分數據集合拿去和正式表中進行匹配，如果在正式表中存在則更新刪除標識，進行刪除。 

注：

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

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

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