`

利用ORACLE的MINUS函数和OVER函数,直接通过视图实现两个记录集的比较。(转载)

 
阅读更多

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函数的认知。 
 
-----------------------------------------------------------------   

分享到:
评论

相关推荐

    MINUS函数和OVER函数[参考].pdf

    本文将详细讲解如何使用 MINUS 函数和 OVER 函数,直接通过视图实现两个记录集的比较。 MINUS 函数 MINUS 函数是 Oracle 中的一个集合操作符,用于从一个查询结果中删除另一个查询结果中的记录。MINUS 函数可以...

    ORACLE技巧 - 不同记录集的横向合并

    本文将重点介绍如何运用Oracle中的`MINUS`函数和`OVER`函数,通过构建视图的方式来直接比较两个记录集的差异。 #### Oracle数据库中记录集的比较方法 在Oracle数据库中,对于两个记录集的比较,通常会涉及到以下几...

    oracle常用知识

    多年来的一点oracle知识积累decode替代case,Drop含有特殊字符的表,Oracle的物化视图的建立方法,动态查询语句返回结果集,利用ORACLE的MINUS函数和OVER函数,直接通过视图实现两个记录集的比较,在oracle中生成...

    Oracle函数及常用sql

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其强大的功能和灵活性使得它在企业级应用中占据了重要地位。...通过深入学习和实践,可以充分利用Oracle的强大功能,高效地管理数据并解决复杂问题。

    oracle常用的100个函数

    以上仅列举了部分常用函数,Oracle还提供了许多其他高级功能,如游标、存储过程、触发器、包、索引、分区、物化视图等,这些都极大地增强了数据库的灵活性和效率。熟练掌握这些函数和概念,能帮助DBA和开发人员更好...

    超详细Oracle教程.pdf

    - **集合操作**:探讨如何使用UNION、INTERSECT、MINUS等操作符处理多个查询结果集。 #### 十、数据字典 - **数据字典概念**:解释数据字典的作用及其在Oracle数据库中的重要性。 - **V$视图**:介绍如何使用V$视图...

    超详细Oracle教程

    - **Oracle与Java结合**:Oracle利用Java技术实现了其软件的跨平台兼容性,这不仅增强了Oracle Fusion Middleware的能力,还为其在云计算领域的发展奠定了基础。 #### 二、数据库基础 - **基础知识**:介绍数据库的...

    (精)Oracle常用傻瓜问题1000问

    - **描述**:返回两个表`BSEMPMS_OLD`和`BSEMPMS_NEW`的交集。 - `SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;` - **描述**:返回两个表的并集,去除重复记录。 - `SELECT * FROM BSEMPMS_OLD ...

    Oracle数据库SQL学习教程

    - 用于合并两个或多个SELECT语句的结果集,并自动去除重复行。 - 示例: `SELECT column_name FROM table1 UNION SELECT column_name FROM table2;` **5.2 UNION ALL** - 合并结果集,包括重复行。 - 示例: `SELECT ...

    精通oracle的sql语句

    - **示例**: 若有两个表,一个是商店信息,另一个是产品信息,可以通过连接操作合并这两个表的信息。 - **CONCATENATE** - **功能**: 合并多个字符串。 - **示例**: 将城市名和销售额合并成一个字符串: ```sql...

    淘宝网开发人员数据库知识手册.docx

    **第二节 比较条件**:用于比较两个值,如`=`(等于)、`!=`(不等于)等。 **第三节 逻辑条件**:用于构建复杂的逻辑判断,如`AND`(与)、`OR`(或)等。 **第四节 成员关系条件**:用于判断某个值是否存在于一...

Global site tag (gtag.js) - Google Analytics