一次数据运维的PL/SQL脚本

目标: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;

一次数据运维的PL/SQL脚本

上一篇:PHP数据库配置


下一篇:Kubernetes字段Finalizers