一次数据运维的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;