判斷彙總庫與地市庫程式碼是否一致

--在儲存過程執行的使用者下，新建三個表
--建表程式碼
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;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28803801/viewspace-768149/，如需轉載，請註明出處，否則將追究法律責任。