sql分段处理删除大量数据

最近在QQ群里面，有人问起如何使用分段处理，竟然忘记如何操作，当时只是COPY一本书里的内容，只记得大致的步骤，先重新温习，以便记忆：

实现功能：

1.分段处理

2.拆分操作

3.批量绑定

－－摘自《Oracle 数据库性能优化》

CREATE OR REPLACE PROCEDURE del_hubel_SSF_forall

as

type ridArray is table of rowid index by binary_integer;

type dtArray is table of varchar2(50) index by binary_integer;

v_rowid ridArray;

v_mid_to_delete dtArray;

BEGIN

SELECT mid, rowid bulk_collect into v_mid_to_delete, v_rowid from temp_mid_hubei_bak WHERE ROWNUM<10001;

FORALL i in 1.. v_mid_to_delete.COUNT

DELETE FROM SSF WHERE mid=v_mid_to_delete(i);

--DBMS_OUTPUT_LINE(TO_CHAR(v_mid_to_delete.COUNT) || ' records deleted from hubei_SSF !!!');

FORALL i in i .. v_rowid.COUNT

DELETE FROM temp_mid_hubei_bak WHERE ROWID=v_rowid(i);

--DBMS_OUTPUT_LINE(TO_CHAR(v_mid_to_delete.COUNT) || ' records deleted from hubei_SSF !!!');