vnsc5858威尼斯城官网oracle数据匹配merge into的实例

时间:2020-04-30 22:58来源:计算机教程
oracle数据匹配merge into的实例详解   这几天,正好做oracle下数据库结构比较,比较具体详细差异,例如:2个库具体有那些表不同,不同在什么地方,主键、长度、类型......,要求是用一

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

   这几天,正好做oracle下数据库结构比较,比较具体详细差异,例如:2个库具体有那些表不同,不同在什么地方,主键、长度、类型......,要求是用一句sql实现,对于我这个sql菜鸟来说,的确是个挑战。好在有个SQL Server例子可以借鉴。先贴一下oracle下的sql

前言:

 1 
 2 
 3 --------------------------------------------------------------------------------------
 4 --oracle数据库帐套比较,
 5 --在本例中,databaseA数据库,
 6           --databaseB是数据库。
vnsc5858威尼斯城官网, 7 --在实际的运行过程中,请依据需要修改本sql
 8 --------------------------------------------------------------------------------------
 9 select (case when a.表名1 is null and b.序号=1 then '库1缺少表:'||b.表名2
10                      when b.表名2 is null and a.序号=1 then '库2缺少表:'||a.表名1
11                      when a.字段名 is null and exists(select 1 from (select table_name as 表名1  from DBA_TAB_COLUMNS where upper(DBA_TAB_COLUMNS.owner) =upper('databaseB')) where 表名1=b.表名2) then '库1 ['||b.表名2||'] 缺少字段:'||b.字段名
12                      when b.字段名 is null and exists(select 1 from (select table_name as 表名2  from DBA_TAB_COLUMNS where upper(DBA_TAB_COLUMNS.owner) =upper('databaseA')) where 表名2=a.表名1) then '库2 ['||a.表名1||'] 缺少字段:'||a.字段名
13                      when a.主键<>b.主键 then '主键不同'
14                      when a.类型<>b.类型 then '类型不同'
15                      when a.字段长度<>b.字段长度 then '字段长度不同'
16                      when a.精度<>b.精度 then '精度不同'                    
17                      when a.小数位<>b.小数位 then '小数位不同'
18                      when a.允许空<>b.允许空 then '允许空不同'
19                 else '' end) result,a.*,b.*      
20 from (select a.table_name as 表名1, a.column_name as 字段名,b.constraint_type as 主键, a.data_type as 类型,column_id as 序号, a.data_length as 字段长度, a.data_precision as 精度,  a.data_scale as 小数位, a.nullable as 允许空   
21    from all_tab_columns a left join (select d.constraint_type,e.column_name,e.table_name from all_constraints d,all_cons_columns e
22  where  d.owner  =  e.owner  and d.table_name  =  e.table_name and
23   d.constraint_name  =  e.constraint_name and d.constraint_type  = 'P') b on a.COLUMN_NAME = b.column_name and a.TABLE_NAME = b.table_name
24    where  a.owner  = upper('databaseB') order by 表名1 asc) a
25 
26 full join (select a.table_name as 表名2, a.column_name as 字段名,b.constraint_type as 主键, a.data_type as 类型,column_id as 序号, a.data_length as 字段长度, a.data_precision as 精度,  a.data_scale as 小数位, a.nullable as 允许空   
27    from all_tab_columns a left join (select d.constraint_type,e.column_name,e.table_name from all_constraints d,all_cons_columns e
28  where  d.owner  =  e.owner  and d.table_name  =  e.table_name and
29   d.constraint_name  =  e.constraint_name and d.constraint_type  = 'P') b on a.COLUMN_NAME = b.column_name and a.TABLE_NAME = b.table_name
30    where  a.owner  = upper('databaseA') order by 表名2 asc) b
31 on a.表名1=b.表名2 and a.字段名=b.字段名      
32 where  a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null  or a.类型<>b.类型 or a.字段长度<>b.字段长度 or a.精度<>b.精度 or a.小数位<>b.小数位 or a.允许空<>b.允许空 or a.主键<>b.主键
33 order by nvl(a.表名1,b.表名2),nvl(a.序号,b.序号)

很久之前,估计在2010年左右在使用Oralce,当时有个需求就是需要对两个表的数据进行匹配,这两个表的数据结构一致,一个是正式表,一个是临时表,这两表数据量还算是比较大几百M。业务需求是用临时表中的数据和正式表的匹配,所有字段都需要一一匹配,而且两表还没有主键,这是一个比较麻烦和糟糕的事情。

执行结果显示的差异信息比较详细。可以依据这个来修改数据库,比较方便。
现在分析下这句sq的语法。
1.将两个数据库oracle叫schema下的所有表结构select出来,即:

场景:

所有表结构select a.table_name as 表名1, a.column_name as 字段名,b.constraint_type as 主键, a.data_type as 类型,column_id as 序号, a.data_length as 字段长度, a.data_precision as 精度,  a.data_scale as 小数位, a.nullable as 允许空   
  from all_tab_columns a left join (select d.constraint_type,e.column_name,e.table_name from all_constraints d,all_cons_columns e
  where  d.owner  =  e.owner  and d.table_name  =  e.table_name and
   d.constraint_name  =  e.constraint_name and d.constraint_type  = 'P') b on a.COLUMN_NAME = b.column_name and a.TABLE_NAME = b.table_name
  where  a.owner  = upper('databaseB') order by 表名1 asc

1、如果两表所有字段值都一致则不处理;2、如果有部分字段不一致则更新;3、如果正式表中数据在临时表中不存在,则需要删除;

通过上述sql就可以将某个数据库下的表结构select出来,通过left join 关联列的主键。

满足上面场景的三个功能即可,可以用程序如(java,C,C#)等各种实现,也可以使用存储过程oracle实现;

  1. 将要比较的两个库的表结构用full join 关联起来。
  2. 同在用case来比较具体不同信息。然后在加上where条件。

考虑之后用程序来做,这肯定是可以实现,但是撇开繁琐的数据读取、操作sql之外,还需要匹配,而且效率也是个问题;就决定用存储过程来实现,先前是使用了if exists来匹配。后面发现效率不高,比较慢;后续改进之后引入了Oracle merge into来实现;

综述,比较oracle中,2个数据库的表结构基本就用上述sql比较了,效率有些慢,朋友们有好的建议可以谈谈。谢谢。

具体事例代码(字段删除了大部分)如下:

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 ) 

注:

编辑:计算机教程 本文来源:vnsc5858威尼斯城官网oracle数据匹配merge into的实例

关键词: