oracle去重用存储过程

存储过程

用于分区表，表数据量较大的场合。仅以key判断，保留rowid小的条

create or replace PROCEDURE  partion_distinct(v_owner IN VARCHAR2,v_table IN VARCHAR2,v_key IN VARCHAR2)
IS
/*
foxytale make
qq 195136130
*/

s_name varchar2(100);
s_str varchar2(1000);
s_num number(20);
s_sum number(20);
--declare and get part name
    CURSOR c_part is select partition_name from dba_tab_partitions where table_name=v_table and table_owner=v_owner order by 1;
    v_part dba_tab_partitions.partition_name%type;
    

--exec distinct
begin
    s_name    :=v_owner||'.'||v_table;
    s_sum    :=0;
    open c_part;
    loop
        fetch c_part into v_part;
        s_str:=    'delete '||s_name||
                ' partition('||v_part||
                ') a where a.rowid <> (select min(rowid) from '||s_name||
                ' partition('||v_part||
                ') b where a.'||v_key||
                '= b.'||v_key||
                ')';
        --dbms_output.put_line(s_str);
        execute immediate s_str;
        s_num:=sql%rowcount;
    commit;

exit when c_part%notfound;
--print log
        dbms_output.put_line(s_name||' distinct '||v_part);
        insert into partion_distinct_log values(v_owner,v_table,v_part,s_num,sysdate);
    commit;
        s_sum:=s_sum+s_num;
end loop;
--print log
    dbms_output.put_line(s_name||' distinct all completed');
    insert into partion_distinct_log values(v_owner,v_table,'all',s_sum,sysdate);
    commit;
close c_part;
end;
/

配套日志表

/*  日志表--预先创建
drop table partion_distinct_log ;
create table partion_distinct_log (owner varchar2(100),tab varchar2(100),part varchar2(100),num number(20),ctime date);



alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set linesize 100
col owner for a10
col tab for a10
col part for a30
col ctime for a30
select * from partion_distinct_log;


truncate table partion_distinct_log;
*/

测试用数据

测试用数据

/*  测试用
create table t_range (dt date,name varchar2(50))
partition by range(dt)(
partition t_range_p2016_01 values less than (to_date('2016-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) ,
partition t_range_p2016_02 values less than (to_date('2016-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) ,
partition t_range_p2016_03 values less than (to_date('2016-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) ,
partition t_range_pmax values less than (maxvalue)  
);

insert into t_range values(sysdate,'1');
insert into t_range values(sysdate,'1');
insert into t_range values(sysdate,'1');
insert into t_range values(sysdate,'1');
insert into t_range values(sysdate,'2');
insert into t_range values(sysdate,'2');
insert into t_range values(sysdate,'3');
insert into t_range values(sysdate,'4');

insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'1');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'1');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'2');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'2');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'1');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'3');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'3');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'3');
insert into t_range values(to_date('2016-01-01 00:00:11','yyyy-mm-dd hh24:mi:ss'),'3');

exec partion_distinct('SYS','T_RANGE','NAME');
exec partion_distinct('TZOS','BS_TB_PAYMENT','PAYMENT_ID');

*/