create or replace procedure del_ppphqrun_Tab
(
p_Count in varchar2

)
as
pragma autonomous_transaction;
p_num number(15) :=0;
n_delete number:=0;
start_time varchar2(100);
end_time varchar2(100);
begin
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') into start_time from dual;
DBMS_OUTPUT.PUT_LINE('start_time ' ||start_time);
select count(*) into p_num from estrnlog where ETSDATE is null and SETTLEDATE is null and termdate <> to_char(sysdate,'mmdd');
DBMS_OUTPUT.PUT_LINE(p_num || ' records need to be deleted');
while true loop
EXECUTE IMMEDIATE
'delete from estrnlog where substr(termid,9,1)=''a'' and termdate <> to_char(sysdate,''mmdd'') and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') into end_time from dual;
DBMS_OUTPUT.PUT_LINE('end_time ' || end_time);
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

exec del_ppphqrun_Tab(10000);

declare
n_count integer := 0;
cursor cur_1 is select * from estrnlog_temp;
begin
for icur_1 in cur_1 loop
insert into estrnlog values (icur_1.MSGTYPE,icur_1.PROCODE,icur_1.ACCOUNT,icur_1.ETSTRANCODE,icur_1.AMOUNT,
icur_1.AMOUNT_FEE,icur_1.MERNO,icur_1.DEVICENO,icur_1.ETSTERMID,icur_1.ETSTIME,icur_1.ETSDATE,icur_1.TERMID,
icur_1.TERMTIME,'1212',icur_1.SETTLEDATE,icur_1.MERTYPE,icur_1.TERMTRACE,icur_1.ETSTRACE,icur_1.HOSTTRACE,icur_1.RESPCODE,
icur_1.ETSRESPCODE,icur_1.AUTHID,icur_1.CURECODE,icur_1.TRFOUTACCNO,icur_1.TRFINACCNO,icur_1.HOSTTIME,icur_1.PERIOD,icur_1.CASHFLAG,
icur_1.TRNPROFLAG,icur_1.REVERSAL,icur_1.FWDIID,icur_1.ACQUIID,icur_1.SNDIID,icur_1.CARDBANKID,icur_1.POS_COND_CODE,icur_1.ORGDATA,
icur_1.ACQADDR,icur_1.BATCHFLAG,icur_1.BATCHDATE,icur_1.TRANTYPE,icur_1.ROUTEPORT,icur_1.RESV1,icur_1.RESV2);
    n_count := n_count + 1;
    if n_count >= 5000 then
      commit;
      n_count := 0;
    end if;
end loop;
commit;
exception
when others then
rollback;
end;
/

ITPub博客

批量插入和删除数据

最新文章