oracle 分批插入 分批删除

create or replace procedure insbigtab
(   p_TableName       in    varchar2,
  p_Condition       in    varchar2,
  p_Count        in    number,
  p_insettab in    varchar2
) 
is
 Type v_rowid is table of varchar2(100) index by binary_integer;
 type type_cursor  is ref cursor; 
 v_cur type_cursor ;
 var_rowid v_rowid;
 sql1 varchar2(1000);

 tb varchar2(1000);
 co varchar2(1000);
 pc number;
 pinst varchar2(1000);
begin 
 tb:=p_TableName;
 co:=p_Condition;
 pc:=p_Count;
 pinst:=p_insettab;
 sql1:='select  ROWID from '||tb||' where  '||co;
  open v_cur for sql1 ;
  loop
   fetch v_cur bulk collect
     into var_rowid limit pc;
    forall i in 1 .. var_rowid.count
     execute  immediate 'insert into '||pinst||' select * from '||tb||' where ROWID = :rn '
   USING  var_rowid(i);
     commit;
     dbms_lock.sleep(3);
   exit when v_cur%NOTFOUND or v_cur%NOTFOUND is null;
   END LOOP ;
  close v_cur;
end;

exec yz.insbigtab('yz.t1','object_id>5000',10000,'yz.t2');

create or replace procedure delbigtab
(
p_TableName in varchar2,
p_Condition in varchar2,
p_Count in varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
dbms_lock.sleep(3);
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

 exec yz.delbigtab('yz.t1','object_id>5000','10000');

不使用过程批量删除

DECLARE
CNT NUMBER(10):=0;
I NUMBER(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM  tt WHERE TO_CHAR(DF_DATE,'MM')='01';

FOR I IN 1..TRUNC(CNT/5000)+1 LOOP
DELETE FROMt WHERE TO_CHAR(DF_DATE,'MM')='01' AND ROWNUM<=5000;
COMMIT;
END LOOP;
END;