oracle数据匹配merge into的实例详解

数据库  /  管理员 发布于 6年前   361

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 来作为删除的标记，以便删除处理。

通过以上两个步骤，正式表和临时表的数据已同步完成！

如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！