目标:MCHTKC表中所有客票的AC联记录,mtcfab及mtccar的取值逻辑变更
运维脚本:
declare
type typ_tkc_result is
record
(
mtcprf mchtkc.mtcprf%type,
mtcfrm mchtkc.mtcfrm%type,
mtctkt
mchtkc.mtctkt%type,
mtccpn mchtkc.mtccpn%type,
mtccar mchtkc.mtccar%type,
mtcfab mchtkc.mtcfab%type,
RID urowid
);
type typ_tkc_results is table of
typ_tkc_result;
tkc_results
typ_tkc_results; --定义数据集合
--查询数据范围
vc_mchtkc_sql varchar2(4000) := ‘select
mtcprf,mtcfrm,mtctkt,mtccpn,mtccar,mtcfab,rowid from mchtkc where mtctkp =
‘‘PAX‘‘ and mtctyp = ‘‘AC‘‘‘;
cur_mchtkc
sys_refcursor;
begin
open cur_mchtkc for
vc_mchtkc_sql;
loop
--外层循环,每次处理2000条
fetch cur_mchtkc
bulk collect into tkc_results limit 2000;
for
i in 1..tkc_results.count loop
begin
execute
immediate ‘select SDCFAB,SDCOCC from saldct where sdcprf = :1 and sdcfrm = :2
and sdctkt = :3 and sdccpn = :4‘
into tkc_results(i).mtcfab, tkc_results(i).mtccar
using
tkc_results(i).mtcprf,tkc_results(i).mtcfrm,tkc_results(i).mtctkt,tkc_results(i).mtccpn;
exception when others then
tkc_results(i).mtcfab := ‘‘;
tkc_results(i).mtccar := ‘‘;
end;
end loop;
forall j in
1..tkc_results.count
execute
immediate (‘UPDATE mchtkc SET mtcfab = :1, mtccar = :2 WHERE rowid = :3 ‘)
using
tkc_results(j).mtcfab,tkc_results(j).mtccar, tkc_results(j).RID;
commit;
exit when tkc_results.count < 2000;
end
loop;
end;