ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 批量插入和删除数据

批量插入和删除数据

原创 Linux操作系统 作者:qinhan1219 时间:2011-12-12 18:34:12 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/14568827/viewspace-713188/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-08-23

  • 博文量
    5
  • 访问量
    12513