create or replace procedure Unique_Record_In_Zhk(partition_name in varchar2) is
--变量声明
TYPE dynamic_cur IS REF CURSOR;
cur dynamic_cur;--动态游标
select_sql varchar2(200);
V_rowid varchar2(50);
rowid_tmp varchar2(50);
counts number := 0;
id varchar2(190);
id_tmp varchar2(190);
TYPE TAB_ID IS TABLE OF t_zhk.ID%TYPE;
TYPE TAB_ROWID IS TABLE OF rowid;
id_type TAB_ID;
rowid_type TAB_ROWID;
--结束变量声明
begin
select_sql := ('select id, rowid from ' || partition_name || ' order by id');
open cur for select_sql;
LOOP
/* 当用FETCH已经取到游标的末尾时,再对游标进行取值,游标会返回结果集中的最后一条记录,
* 因为,当游标再结果集中取不到值时会返回给用户游标缓冲区的内容(也就是结果集中的最后一条记录)*/
FETCH cur BULK COLLECT INTO id_type, rowid_type limit 100000;
for id_index in 1 .. id_type.COUNT
LOOP
id := id_type(id_index);
V_rowid := rowid_type(id_index);
if (id_tmp is not null) then
begin
if (id = id_tmp) then
execute immediate 'delete from ' || partition_name || ' where rowid = :rowid_tmp' using rowid_tmp;
counts := counts + 1;
end if;
end;
end if;
id_tmp := id;
rowid_tmp := V_rowid;
if (mod(counts,2000) = 0) then
commit;
end if;
END LOOP;
EXIT WHEN cur%NOTFOUND;
end LOOP;
close cur;
commit;
dbms_output.put_line(to_char(counts));
end Unique_Record_In_Zhk;
将分区名作为参数传入存储过程,生成动态游标,以10万为一个批次进行批量提取数据。数据按ID做了排序,删除重复的ID记录,保留一条即可。删除记录的时候使用了变量替换,每删除2000条记录提交一次事务。