oracle利用存储过程更新100000条数据;怎么让他更快
CREATE OR REPLACE PROCEDURE PROC_UPDATE_BDC_SECOND
as
slsj date;
bjsj date;
BEGIN
--当是否更新字段为1时
for cur_entry in(select
a.ywh,a.entry_id,a.qllx,a.djlx,a.class_code,a.CATALOGUE_CODE
,a.bdcqzh,a.form_year
from am_bdc_entry a
where a.isbdcupdate =1 and a.state =100 and a.file_code =-1
)
loop
begin
--查询并赋值到临时变量中受理时间(slsj)和办结时间(bjsj)
select to_date(to_char(g.Start_Time_, 'YYYY-MM-DD'),
'yyyy-mm-dd') slsj,
to_date(to_char(g.end_time_, 'YYYY-MM-DD'),
'yyyy-mm-dd') bjsj
into slsj, bjsj
from gisqbpm.ACT_HI_PROCINST g
where g.business_key_ = cur_entry.ywh;
--更新语句
update wz_gisqam.am_bdc_entry t
set t.start_date = slsj, t.end_date = bjsj
where t.ywh = cur_entry.ywh;
commit;
exception
when others then
declare
out_res varchar2(300);
BEGIN
out_res:='捕获错误 错误代码:'||SQLCODE||'错误信息:'||SQLERRM;
insert into
JOBS_LOG_TJ(LAST_DATE,SUCCESS_RECORD,PRO_NAME,FAILURE_RECORD)
values(sysdate,cur_entry.ywh,'PROC_UPDATE_BDC_INFO',out_res);
commit;
end;
end;
end loop;
END ;