数据库多表去重存储过程例子

场景说明：

数据库中几十个表，剔重复记录。

业务表相关说明：

1.ird_main表存放表信息，用来循环获取哪些表要处理。

2.ird_table存放某个表的所有字段，其中is_primary='1'是主键字段，某个表可能有1个以上主键字段。

3.删除操作过程中实时的日志直接写入表deltablelog。

存储过程如下：

create or replace procedure logTask as
col_condition varchar2(2000);

begindate date;
tabExist int;
cursor curIrdMain is select * from bcp.ird_main where parent_id in ('1','2');
begin
   for irdMain in curIrdMain loop
     --表不存在则跳过
     select count(1) into tabExist from user_tables a where table_name=UPPER(irdMain.main_code);
     if tabExist=0 then
       continue;
     end if;
     
     begindate:=sysdate;
     --获取主键信息
     col_condition:=' 1=1 ';
     for pks in (select * from bcp.ird_table where main_id=irdMain.Main_Id and is_primary='1') loop
       col_condition := col_condition||' and a.'||pks.field_code||'=b.'||pks.field_code;
     end loop;
     --调用本表删除
     deltab(irdMain.main_code,col_condition);
     --dbms_output.put_line(irdMain.name||'耗时'||ROUND(TO_NUMBER(sysdate-begindate) * 24 * 60 * 60)||'s');
     insert into deltablelog(content) values(irdMain.name||'耗时'||ROUND(TO_NUMBER(sysdate-begindate) * 24 * 60 * 60)||'s');
     commit;
   end loop;
end;

create or replace procedure delTab(tablename in varchar2,
                                      col_condition in varchar2) as
--tablename varchar2(200):='jhjcqzj.mz03';
--col_condition varchar2(200):=' a.id=b.id ';
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
type refcursor1 is ref cursor;
cur1 refcursor1;
cnt number;
begin
open cur1 for 'select a.rowid
  from '||tablename||' a
 where a.batchid != (select max(batchid)
                          from '||tablename||' b
                         where '||col_condition||')';
loop
exit when cur1%notfound;
fetch cur1 bulk collect
into row_id_table limit maxrows;
cnt:= row_id_table.count;
forall i in 1 .. row_id_table.count
execute immediate 'delete from '||tablename||' where rowid = :1' using row_id_table(i);
commit;
--dbms_output.put_line('删除'||tablename||'重复数据'||cnt||'...');

insert into deltablelog(content) values('删除'||tablename||'重复数据'||cnt||'...');
commit;

end loop;
close cur1;
end;

执行办法,然后观察日志输出情况：

call logTask();

oracle分批更新，请移步这里：http://www.yuanjing.org.cn/article/13.html