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

2016-01-06 10:43:15 411 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle数据库下载安装与PLSQL Developer配置实操指南关系型数据库在金融、电信等企业级系统中占据核心地位，Oracle凭借其高并发处理与安全机制成为主流选择。而PLSQL Developer作为Oracle开发的高效工具，其与数据库的连接依赖OCI与TNS配置。理解服务名、监听端口、环境变量等基础原理，是解决安装报错与连接失败的关键。从环境规划、版本选择到服务端安装、客户端配置，再到连接验证与卸载清理，系统掌握全流程能显著提升工程效率。本文结合常见问题，如TNS无法解析、登录卡死等，给出排查思路，帮助开发者快速搭建可用的本地开发环境，并延伸至远程连接及轻量客户阅读详情

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之后，会不会好一点？

...全文