Fetch Bulk Collect Into

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条记录提交一次事务。