用plsql写了个存储过程，用定时任务触发，可是执行好慢，千条数据要执行几分钟，请教高手帮忙！

[万物皆一空]

create or replace procedure DE_KC_STORE(v_createdate in varchar2 :=to_char(sysdate-1105,'yyyymmdd'),
v_validflag1 in number :=0,
v_validflag2 in number :=4) is
--创建游标
cursor m_cur is
select * from s_store_t where validflag in(v_validflag1,v_validflag2) and
createdate < to_date(v_createdate,'yyyy-mm-dd')
order by createdate asc;
m_row m_cur%rowtype;
begin
open m_cur;
loop
fetch m_cur
into m_row;
exit when m_cur%notfound;
insert into s_store_backup_t (ID,MATERIALID,VALIDFLAG,QFLAG,MATCHID,STORECODE,STORENAME,POSTIONID,STOERPOS,
MATERIALCODE,MATERIALNAME,FOLDERCODE,STEELCODE,STEELNAME,MATERIALSPECCODE,MATERIALSPEC,
MATERIALSIZE,ISSTANDARD,SOURCECODE,SOURCENAME,BATCHNO,BATCHNO_XN,LOTNUMBER,MATERIALTOTALCOUNT,
MATERIALTOTALWEIGHT,MATERIALCOUNT,MATERIALWEIGHT,DOSTANDARD,LICENCE,MAINCOMP,AGEING,CREATEMAN,
CREATEDATE,UPDATEMAN,UPDATEDATE,MEMO,RECORDTYPE,TESTMAN,TESTDATE,HEATNO,LZJNO,POSLEVEL,POSDIRECTION,
FIREDCOUNT,ZG,RSFLAG,GROSS,SUTTLE,CHECKMAN,ORDERNUMBER,STEELALIAS,WORKGROUP,WORKGROUPINDEX,PRODUCTATTRIBUTES,
COUNTNUMBER,PRINTTIME,TSMEMO,ORDERNO,TGCJ,ZHIXINGBIAOZHUN,YGR,YGDATE,HYR,HYDATE,ZFR,ZFDATE,YGMEMO,
PFMEMO)
values(m_row.id,m_row.materialid,m_row.validflag,m_row.qflag,m_row.matchid,m_row.storecode,
m_row.storename,m_row.postionid,m_row.stoerpos,m_row.materialcode,m_row.materialname,
m_row.foldercode,m_row.steelcode,m_row.steelname,m_row.materialspeccode,m_row.materialspec,
m_row.materialsize,m_row.isstandard,m_row.sourcecode,m_row.sourcename,m_row.batchno,
m_row.batchno_xn,m_row.lotnumber,m_row.materialtotalcount,
m_row.materialtotalweight,m_row.materialcount,m_row.materialweight,m_row.dostandard,
m_row.licence,m_row.maincomp,m_row.ageing,m_row.createman,m_row.createdate,
m_row.updateman,m_row.updatedate,m_row.memo,m_row.recordtype,m_row.testman,m_row.testdate,
m_row.heatno,m_row.lzjno,m_row.poslevel,m_row.posdirection,m_row.firedcount,m_row.zg,m_row.rsflag,m_row.gross,
m_row.suttle,m_row.checkman,m_row.ordernumber,m_row.steelalias,m_row.workgroup,m_row.workgroupindex,m_row.productattributes,
m_row.countnumber,m_row.printtime,m_row.tsmemo,m_row.orderno,m_row.tgcj,m_row.zhixingbiaozhun,m_row.ygr,m_row.ygdate,
m_row.hyr,m_row.hydate,m_row.zfr,m_row.zfdate,m_row.ygmemo,m_row.pfmemo);
commit;
delete from s_store_t where validflag in(v_validflag1,v_validflag2) and
createdate < to_date(v_createdate,'yyyy-mm-dd');
commit;
--to_date(v_createdate,'yyyy-mm-dd');
end loop;
end DE_KC_STORE;

上面是存储过程，原来没有对insert into和delete后面加commit,使用jobs存储过程基本就不调用,之后加了commit后，虽然调用，但数据量上千条时，执行就好慢？请问高手，commit放置位置是不是有什么学问？还有如果把delete放到end loop之后，会不会好一点？

[卖水果的net]

把游标换成 insert into select 形式。

[jdsnhan]

1、commit放在end loop里面，有可能造成数据不一致，如果中途出错，前面的提交了，后面的不执行，数据不一致了 2、在执行的时候，查查v$session_wait，看看是什么在等待 3、1楼的是个好建议，尝试insert into select，批量处理，效率高很多

[万物皆一空]

谢谢楼上的，问题已经解决，我把commit放到到loop外面！速度快很多！