利用ORACLE的MINUS函式和OVER函式,直接通過檢視實現兩個記錄集的比較。(轉載)

luckyfriends發表於2012-12-19
1 前言  
-------------------------------------------------------------  
在程式設計過程中,往往遇到比較兩個記錄集的差異。如,判斷原來傳入的訂單資料與後來傳入的訂單資料之間的差異,並且將差異的資料顯示給使用者。  
實現的方式有多種,如程式設計儲存過程返回遊標,在儲存過程中對兩批資料進行比較...等等,當然返回差異資料的方式多種多樣,既可以是遊標,又可以臨時表或其它方式。  
本文主要論述利用ORACLE的MINUS函式和OVER函式,直接通過檢視實現兩個記錄集的比較。  
  
-------------------------------------------------------------  
2 實現步驟  
-------------------------------------------------------------  
2.1 利用MINUS函式,判斷原始表與比較表的增量差異<設,兩個記錄集分別以表的方式存在,為表A和表B。其中,A表為原始表,B表為後來產生的比較表,即要與A表進行比較的資料表>  
增量差異指,A中存在的記錄,哪些在B表中沒有的,也就是說,A表的記錄被修改或刪除  
  
2.2 利用MINUS函式,判斷比較表與原始表的增量差異  
即B表中存在的記錄,哪些在A表中沒有,也就是說,B表新增的或A表修改的記錄  
  
2.3 連線A-B的增量差異表和B-A的增量差異表,利用OVER函式判斷資料重複的次數  
如果資料重複次數為2,則該記錄的標識為“修改”;  
如果資料重複次數為1,且出現在A-B的增量差異表中,則該記錄的標識為“刪除”;  
如果資料重複次數為1,且出現在B-A的增量差異表中,則該記錄的標識為“新增”  
  
-------------------------------------------------------------  
3 例項演練  
-------------------------------------------------------------  
--3.1 建立資料表和例項環境<設原始記錄集為資料表A,比較記錄集為資料表B,當然實際應用過程中,參與比較的通常是檢視,不會是資料表>  
--測試環境配置  
Drop Table a;  
Drop Table b;  
Create Table a(a1 Numeric(28),a2 Varchar2(10));  
Create Table b(b1 nUMERIC(28),b2 VarChar2(10));  
Insert Into a Values (1,'a');  
Insert Into a Values (2,'ba');  
Insert Into a Values (3,'ca');  
Insert Into a Values (4,'da');  
Insert Into b Values (1,'a');  
Insert Into b Values (2,'bba');  
Insert Into b Values (3,'ca');  
Insert Into b Values (5,'dda');  
Insert Into b Values (6,'Eda');  
Commit;  
Select * from a;  
Select * From b;  
  
--3.2 建立比較檢視  
Create Or replace View VW_Test_Minus as   
    --標識重複出現的次數(次數=1->刪除或新增,次數=2->修改)  
    SELECT A1  
          ,a2  
          ,t --A表/B表標識  
          ,ROW_NUMBER() OVER (PARTITION BY A1 ORDER BY A1) RN --記錄重複次數  
      FROM   
         (  
             Select a1,a2,'A表' T   --檢視A表存在,B表沒有的記錄(修改或刪除)  
                from   
                   (  
                      (Select * from a )  
                      Minus   
                      (Select * From b)  
                   ) a2b  
             Union --聯合A表與B表不相同的記錄集  
             Select b1,b2,'B表' T   --檢視B表存在,A表沒有的記錄(修改或新增)  
                from   
                   (  
                      (Select * from b )  
                      Minus   
                      (Select * From a)  
                      ) b2a  
          ) F;  
/  
  
--3.3 比較結果集  
Select a1  
    ,a2  
    ,T  
    ,Rn  
    ,Decode(Rn --標識記錄變化  
       ,2,'修改'  
       ,Decode(T  
          ,'A表','刪除'  
          ,'新增')) Mark  
    From VW_Test_Minus  
       Where Rn=(Select Count(*) From VW_Test_Minus V Where V.a1=VW_Test_Minus.a1)  
;  
  
-------------------------------------------------------------  
4 後記  
-------------------------------------------------------------  
許多DBA都特別痛恨那些希望通過一句SELECT語句來實現複雜使用者需求的編碼人員,使用MINUS和OVER函式來實現資料比較,在執行效率上,可能會存在問題。  
本文的目的,並不在於討論程式執行的效率,而在於拋磚引玉,引起大家對OVER函式的重視和對MINUS函式的認知。 
http://www.cnblogs.com/lizw/archive/2007/04/26/729006.html

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14710393/viewspace-751343/,如需轉載,請註明出處,否則將追究法律責任。

相關文章