--在存储过程执行的用户下，新建三个表
--建表代码
create table tab_name
(table_name varchar2(20));

create table remark
(
db_name varchar2(20),
table_name varchar2(20),
num number(5)
);

create table dif_result
(
db_name varchar2(20),
table_name varchar2(20),
id varchar2(100),
name varchar2(500)
);
--tab_name存放代码表的名称，先执行获取表名称的语句，
insert into tab_name
select table_name from dba_tables where wner='汇总库用户名';
--remark存放核对的结果；如果结果为-1，表示仅在汇总库中存在;
--dif_result中存放的是不同的结果

CREATE OR REPLACE PROCEDURE "DATACODE" (MYARG IN varchar2)
IS
  v_num number(5);
  cursor cur_table is select table_name from tab_name;
  cursor cur_result is select db_name,table_name,num from remark;
  v_tablename varchar2(20);
  v_count number(5);
  v_count1 number(5);
  v_sql varchar2(400);
  v_db_name varchar2(20);
  v_table_name varchar2(20);
  v_num1 number(5);
  no_table exception;
  pragma exception_init(no_table,-00942);
begin
  select count(*) into v_count from tab_name;
  open cur_table;
  fetch cur_table into v_tablename;
     for i in 1..v_count loop
         v_sql :='select count(1) from ('||
         '(select id,name from hzcode.'|| v_tablename
         ||' minus '
         ||'select id,name from ' || MYARG ||'.'|| v_tablename
         || ')'
         ||'union'
         ||'(select id,name from ' || MYARG ||'.'||v_tablename
         ||' minus '
         ||'select id,name from hzcode.'||v_tablename
         || ')'
         || ')';
            begin
               execute immediate v_sql into v_num;
               insert into remark values(MYARG,v_tablename,v_num);
               commit;
                 exception
                    when no_table then
                    insert into remark values(MYARG,v_tablename,-1);
                    commit;
             end;
             fetch cur_table into v_tablename;
     end loop;
  close cur_table;
  delete from remark where num=0;
  commit;
  select count(*) into v_count1 from remark;
  open cur_result;
  fetch cur_result into v_db_name,v_table_name,v_num1;
    for i in 1..v_count1 loop
        v_sql :='insert into dif_result(id,name) select id,name from( '||
         '(select id,name from hzcode.'|| v_table_name
         ||' minus '
         ||'select id,name from ' || v_db_name ||'.'|| v_table_name
         || ')'
         ||'union'
         ||'(select id,name from ' || v_db_name ||'.'||v_table_name
         ||' minus '
         ||'select id,name from hzcode.'||v_table_name
         || ')'
         || ')';
         begin
           execute immediate v_sql;
           update dif_result set db_name=v_db_name where db_name is null;
           update dif_result set table_name=v_table_name where table_name is null;
           commit;
             exception
               when no_table then
                 insert into dif_result values(v_db_name,v_table_name,'仅汇总库有','仅汇总库有');
                 commit;
         end;
         fetch cur_result into v_db_name,v_table_name,v_num1;
         end loop;
         close cur_result;           
end;
/