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

前言：

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

場(chǎng)景：

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

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

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

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

1、對(duì)臨時(shí)表L_TABLE中數(shù)據(jù)與正式表Z_TABLE比較，如果各個(gè)字段值比較不相等則認(rèn)為臨時(shí)表的這行數(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ù) 這個(gè)地方需要特別注意，當(dāng)初測(cè)試的時(shí)候發(fā)現(xiàn)有些字段為空null，null和null之前是不能用相等來處理的，所以對(duì)于那些為null或者“”的字段統(tǒng)一轉(zhuǎn)為一個(gè)字符串來做匹配，當(dāng)作相等，要不然會(huì)出現(xiàn)匹配不上的問題。

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

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

在之前第1步的時(shí)候如果有一行數(shù)據(jù)的某個(gè)字段不相同的話，是會(huì)重新插入一行數(shù)據(jù)到正式表中，而不是更新，所以正式表中會(huì)多出一些多余的數(shù)據(jù)也就是和臨時(shí)表不完全相等的數(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í)表中的差異數(shù)據(jù)(包括各個(gè)字段值的差異),這里用了Oracle的minus函數(shù)進(jìn)行刷選，其實(shí)這部分就是需要?jiǎng)h除的數(shù)據(jù)，將這部分?jǐn)?shù)據(jù)集合拿去和正式表中進(jìn)行匹配，如果在正式表中存在則更新刪除標(biāo)識(shí)，進(jìn)行刪除。 

注：

因?yàn)閮杀矶际菦]有主鍵，所以無法使用唯一判斷用來刪除作為刪除依據(jù)，而且merge into的語句測(cè)試了下delete必須要跟在update后，這點(diǎn)比較糾結(jié)，所以只能給正式表加一個(gè)擴(kuò)展字段additionalInfo 來作為刪除的標(biāo)記，以便刪除處理。

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

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

最新評(píng)論