最近在QQ群里面，有人問(wèn)起如何使用分段處理，竟然忘記如何操作，當(dāng)時(shí)只是COPY一本書(shū)里的內(nèi)容，只記得大致的步驟，先重新溫習(xí)，以便記憶：

實(shí)現(xiàn)功能：

1.分段處理

2.拆分操作

3.批量綁定

－－摘自《Oracle 數(shù)據(jù)庫(kù)性能優(yōu)化》

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 !!!');
END;

CREATE OR REPLACE PROCEDURE exec_forall
(
 p_RowCount in number,   --Total need to delete rows count
 p_ExeCount in number
)
AS
 n_RowCount number:=0;
 n_ExeTimes number:=0;
 n_Delete number:=0;
BEGIN
 n_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;