最近在QQ群里面，有人问起如何使用分段处理，竟然忘记如何操作，当时只是COPY一本书里的内容，只记得大致的步骤，先重新温习，以便记忆：
实现功能：
1.分段处理
2.拆分操作
3.批量绑定
－－摘自《Oracle 数据库性能优化》
CREATE OR REPLACE PROCEDURE del_hubel_SSF_forallastype 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;BEGINSELECT 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 !!!');END;
CREATE OR REPLACE PROCEDURE exec_forall(p_RowCount in number,--Total need to delete rows countp_ExeCount in number)ASn_RowCount number:=0;n_ExeTimes number:=0;n_Delete number:=0;BEGINn_RowCount :=p_RowCount;WHILE n_RowCount>0 LOOP EXECUTE IMMEDIATE 'begin del_hubei_SSF_forall; end;'; COMMIT; IF n_RowCount>p_ExeCount THEN n_RowCount:=n_RowCount-p_ExeCount; n_ExeTime:=n_ExeTimes+1; ELSE n_ExeTimes :=n_ExeTimes+1; n_Delete:=RowCount; n_RowCount:=n_RowCount-p_ExeCount; END IF; --DBMS_OUTPUT_LINE('----------------' || TO_CHAR(n_ExeTimes) || '--------------'); n_delete :=n_delete+p_ExeCount*(n_ExeTimes-1); END LOOP; --DBMS_OUTPUT_LINE('Full Finished!!!'); --DBMS_OUTPUT_LINE('Totally' || TO_CHAR(n_delete) || 'records deleted. !!!');END;
