当前位置: 代码迷 >> Oracle开发 >> oracle中比较两表表结构差别和数据差异的方法
  详细解决方案

oracle中比较两表表结构差别和数据差异的方法

热度:73   发布时间:2016-04-24 06:29:07.0
oracle中比较两表表结构差异和数据差异的方法

  在工作中需要完成这么一个需求:比较两个表的表结构是否形相同,并找出差异.比较两个表中的数据是否相同,并找出差异数据?
    分析:由于表结构中字段比较多,手工比较很浪费时间,而且不能保证不出错误.对于表中的数据那就能多了,更不能靠这种方式比较.

    为了思考问题简单和方便测试,首先先建立两个测试表,并插入一些测试数据吧,sql如下:

 

  1. create table t_A 
  2.   id   VARCHAR2(36) not null, 
  3.   name VARCHAR2(100), 
  4.   age  NUMBER, 
  5.   sex  VARCHAR2(2) 
  6. ); 
  7.  
  8. insert into t_A (id, name, age, sex) 
  9. values ('1', '1', 1, '1'); 
  10. insert into t_A (id, name, age, sex) 
  11. values ('2', '2', 2, '2'); 
  12. commit; 
  13.  
  14.  
  15. create table t_B 
  16.   id    VARCHAR2(36) not null, 
  17.   name  VARCHAR2(100), 
  18.   age   NUMBER, 
  19.   clazz VARCHAR2(36) 
  20. ); 
  21.  
  22. insert into t_B (id, name, age, clazz) 
  23. values ('1', '1', 1, '1'); 
  24. insert into t_B (id, name, age, clazz) 
  25. values ('2', '2', 1, '3'); 
  26. insert into t_B (id, name, age, clazz) 
  27. values ('3', '3', 3, '3'); 
  28. commit; 
create table t_A(  id   VARCHAR2(36) not null,  name VARCHAR2(100),  age  NUMBER,  sex  VARCHAR2(2));insert into t_A (id, name, age, sex)values ('1', '1', 1, '1');insert into t_A (id, name, age, sex)values ('2', '2', 2, '2');commit;create table t_B(  id    VARCHAR2(36) not null,  name  VARCHAR2(100),  age   NUMBER,  clazz VARCHAR2(36));insert into t_B (id, name, age, clazz)values ('1', '1', 1, '1');insert into t_B (id, name, age, clazz)values ('2', '2', 1, '3');insert into t_B (id, name, age, clazz)values ('3', '3', 3, '3');commit;

      解决过程:刚开始考虑使用存储过程,用循环比较的方式处理,首先需要找出能得到表结构的sql,查阅资料得知,在Oracle中所有表结构信息都存储在user_tab_columns中,那么查询单个表的表结构信息很简单: select column_name from user_tab_columns where table_name = 't_A'; 运行后发现查不到结果,为什么呢?去掉查询条件后能查询出结果,核对后发现原来在user_tab_columns中存储的内容都是大写的,原来如此,sql改为如下就可以查询出结果了: select column_name from user_tab_columns where table_name = 'T_A'; 写这样一个存储过程发现还是有点复杂的,网上找找有没有现成的,自己写了一会发现很复杂.网上找的时候找到了一个minus关键字.科普一下:在oracle中union 并集 intersect 交集  minus 差集;我可以用差集来实现那个需求吗? 很快就写出了sql:

 

 

  1. /*1.比较表结构 */ 
  2. (select column_name 
  3.           from user_tab_columns 
  4.          where table_name = 'T_A' 
  5.         minus 
  6.         select column_name 
  7.           from user_tab_columns 
  8.          where table_name = 'T_B') 
  9. union  
  10. (select column_name 
  11.          from user_tab_columns 
  12.         where table_name = 'T_B' 
  13.        minus 
  14.        select column_name 
  15.          from user_tab_columns 
  16.         where table_name = 'T_A'); 
  17.          
  18.  
  19. /* 2.比较表数据 */ 
  20. (select * 
  21.           from t_A 
  22.         minus 
  23.         select * from t_B) 
  24. union  
  25. (select * 
  26.          from t_B 
  27.        minus 
  28.        select * from t_A) 
/*1.比较表结构 */(select column_name          from user_tab_columns         where table_name = 'T_A'        minus        select column_name          from user_tab_columns         where table_name = 'T_B')union (select column_name         from user_tab_columns        where table_name = 'T_B'       minus       select column_name         from user_tab_columns        where table_name = 'T_A');        /* 2.比较表数据 */(select *          from t_A        minus        select * from t_B)union (select *         from t_B       minus       select * from t_A)

 

看看sql的运行效果吧:

表t_A结构及数据:

表t_B结构及数据:

表结构差异:

数据差异:

 

    反思:为什么我之前没想到用差集呢? 1.数学没有学好,没有数学的思维.并集交集和差集的概念早就在中学学过,但数学思维没有建立,所以....得补补数学啦~ 2.oracle函数不熟,看来我需要找一本oracle函数手册,没事的时候就翻翻.

 

1楼雷厉*风行
这东西平时用的就少,想不起来很正常。感谢lz分享。
  相关解决方案