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

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

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

赞 (0)

相关推荐

  • oracle数据库导入导出命令解析

    Oracle数据导入导出imp/exp 功能:Oracle数据导入导出imp/exp就相当与oracle数据还原与备份. 大多情况都可以用Oracle数据导入导出完成数据的备份和还原(不会造成数据的丢失). Oracle有个好处,虽然你的电脑不是服务器,但是你装了oracle客户端,并建立了连接(通过Net Configuration Assistant添加正确的服务命名,其实你可以想成是客户端与服务器端 修了条路,然后数据就可以被拉过来了) 这样你可以把数据导出到本地,虽然可能服务器离你很远.

  • ORACLE SQL语句优化技术要点解析

    操作符优化: IN 操作符 用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格. 但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询. 由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐方案:

  • Linux下修改Oracle监听地址的方法

    lisenter.ora 目录在 /opt/oracle/11g/network/admin LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.111.123)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /opt/oracle tnsnames.o

  • ORACLE批量导入图片到BLOB字段代码示例

    要插入图片的表不是固定的,而且是批量插入很多张,还要考虑到因为图片的文件名错误,修改后要再次插入,此时应避免已经插入的重复执行操作, 浪费时间. 所以就选择先用一张临时表来暂时保存从文件系统读取的照片,用DBMS_LOB的方法来处理. 第一步:创建一个数据库可以访问的目录(注意:这个目录是数据库服务器上的目录,不是客户机上的) -- Create directory create or replace directory 图片目录 as 'E:\照片'; 第二步:将图片文件放入刚建好的目录下面,

  • 浅谈oracle rac和分布式数据库的区别

    1.分布式数据库是多个数据库,而rac只是一个库多个实例: 2.rac事务上没有协调的问题,而分布式数据库由于是多个库需要事务上的协调: 3.分布式数据库数据是分散存储在各个节点,但是设备一般都是廉价的设备,经常出现节点故障,不过对用户来说是透明的:.RAC是ORACLE集群,数据是共享存储,只有一份,每个节点都不存放数据.节点可以宕,但是数据不会丢失: 4.分布式数据库支持的节点多,增加节点基本为线性增加:rac支持的节点数少,增加节点性能不是线性增加: 5.Oracle最大的问题在于shar

  • Python使用cx_Oracle调用Oracle存储过程的方法示例

    本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法.分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL. 首先,在数据库端创建简单的存储过程. create or replace procedure test_msg(i_user in varchar2, o_msg out varchar2) is begin o_msg := i_user ||', Good Morning!'; end; 然后,开始在Pytho

  • 常用的Oracle doc命令(收藏)

    1.连接数据库 普通用户连接数据库: conn scott/tiger –(默认的用户名/密码).conn 即"connection"连接数据库的意思 超级管理员连接: Conn sys/sys as sysdba–oracle数据库不区分大小写 2.sql文件 保存sql文件 save c:\test.txt 编辑sql语句,用记事本编辑 ed c:\1.txt –编辑 SQL 语句 ed –直接打开上一次缓存sql语句 描述数据表的结构 desc employee; –打开empl

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

    oracle数据匹配merge into的实例详解 前言: 很久之前,估计在2010年左右在使用Oralce,当时有个需求就是需要对两个表的数据进行匹配,这两个表的数据结构一致,一个是正式表,一个是临时表,这两表数据量还算是比较大几百M.业务需求是用临时表中的数据和正式表的匹配,所有字段都需要一一匹配,而且两表还没有主键,这是一个比较麻烦和糟糕的事情. 场景: 1.如果两表所有字段值都一致则不处理: 2.如果有部分字段不一致则更新: 3.如果正式表中数据在临时表中不存在,则需要删除: 满足上面场

  • 对dataframe数据之间求补集的实例详解

    python的pandas库,对于dataframe数据,有merge命令可以完成dataframe数据之间的求取交集并集等命令. 若存在df1与df2 ,他们的交集df3=pd.merge(df1,df2,on=[.....]).但是又想通过df3求df3与df1的补集时发现没有该命令. 求df3(子集)与df1补集: #x为子集 def Complement(x,y): import numpy as np array1 = np.array(x) list1=array1.tolist()

  • Oracle sqlldr导入一个日期列实例详解

    Oracle sqlldr导入一个日期列实例详解 1. LOAD DATA INFILE * INTO TABLE test FIELDS TERMINATED BY X'9' TRAILING NULLCOLS ( c2 "upper(:c2)", c3 date "yyyymmdd", c1 "SEQ_test_c1.nextval" ) BEGINDATA AAAAAAAAAA 20051201 BBBBBBBBBB 20050112 CR

  • mybatis实现对数据的增删查改实例详解

    mybatis实现对数据的增删查改实例详解

    前期准备 新建java工程或java wweb工程,需要导入以下的包, 基本工作已经完成,接下来开始进入正题. 新建实体类 新建与数据库表对应的实体类 package com.edu.hpu.domain; /** * @author Administrator *user表所对应的实体类 */ public class User { //实体类的属性和表的字段名称一一对应 private int id; private String name; private int age; //对属性进行

  • Python数据可视化:幂律分布实例详解

    1.公式推导 对幂律分布公式: 对公式两边同时取以10为底的对数: 所以对于幂律公式,对X,Y取对数后,在坐标轴上为线性方程. 2.可视化 从图形上来说,幂律分布及其拟合效果: 对X轴与Y轴取以10为底的对数.效果上就是X轴上1与10,与10与100的距离是一样的. 对XY取双对数后,坐标轴上点可以很好用直线拟合.所以,判定数据是否符合幂律分布,只需要对XY取双对数,判断能否用一个直线很好拟合就行.常见的直线拟合效果评估标准有拟合误差平方和.R平方. 3.代码实现 #!/usr/bin/env

  • Spark中的数据读取保存和累加器实例详解

    目录 数据读取与保存 Text文件 Sequence文件 Object对象文件 累加器 累加器概念 系统累加器 数据读取与保存 Text文件 对于 Text文件的读取和保存 ,其语法和实现是最简单的,因此我只是简单叙述一下这部分相关知识点,大家可以结合demo具体分析记忆. 1)基本语法 (1)数据读取:textFile(String) (2)数据保存:saveAsTextFile(String) 2)实现代码demo如下: object Operate_Text { def main(args

  • python中DataFrame数据合并merge()和concat()方法详解

    python中DataFrame数据合并merge()和concat()方法详解

    目录 merge() 1.常规合并 ①方法1 ②方法2 重要参数 合并方式 left right outer inner 2.多对一合并 3.多对多合并 concat() 1.相同字段的表首位相连 2.横向表合并(行对齐) 3.交叉合并 总结 merge() 1.常规合并 ①方法1 指定一个参照列,以该列为准,合并其他列. import pandas as pd df1 = pd.DataFrame({'id': ['001', '002', '003'], 'num1': [120, 101,

  • Oracle中nvl()和nvl2()函数实例详解

    目录 1.nvl:根据参数1是否为null返回参数1或参数2的值 [样例展示一]:expression1值为null: [样例展示二]:expression1值不为null: [样例展示三]:expression1.expression2值均为null: 2.nvl2:根据参数1是否为null返回参数2或参数3的值 [样例展示一]:expression1值不为null: [样例展示二]:expression1值为null: [样例展示三]:expression1为null,且expression

  • Oracle merge合并更新函数实例详解

    前言 MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句. 通过MERGE语句,根据一张表或多表联合查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT.这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE.通过这个MERGE你能够在一个SQL语句中对一个表同时执行INSERT和UPDATE操作. 本博客介绍一下Oracle merge合并函数,业务场景:新增数据的时候要先查询数据库是否已

  • Oracle数据库中通用的函数实例详解

    Oracle数据库中通用的函数实例详解

    目录 一. Scott用户下的表结构 1.如果自己没有Scoot表就可以自己创建一个 二.单行函数 1.字符函数 2.数值函数 三.多行函数(聚合函数) 1.统计记录数 2.最小值查询 min() 3.最大值查询 max() 4.平均值查询 avg() 5.求和函数 四.分组统计 总结 一. Scott用户下的表结构 SCOTT.是在Oracle数据库中,一个示例用户的名称.其作用是为初学者提供一些简单的应用示例,不过其默认是锁定状态,在安装时,根据用户需要,在“数据库配置助手”界面完成后,弹出

随机推荐