fetch bulk collect into 更新存储过程

SQL快速更新存储过程，效率非常快

declare 

         TYPE   t_id   IS   TABLE   OF   chncrmtpp.tp_card_no%TYPE; 
         v_t_id   t_id; 
              
         CURSOR   c   IS 
         SELECT   tp_card_no  FROM  chncrmtpp   where  trans_no like '3206%'; 
         --    
         cnt   NUMBER   :=   0; 
           BEGIN 
         OPEN   c; 
         LOOP 
                      cnt   :=   cnt   +   1; 
                      --   10000 
                   fetch   c   bulk   collect   into   v_t_id   LIMIT   10000; 
                      --   forall 
                      FORALL   i   IN   1   ..   v_t_id.COUNT 
                   UPDATE  tp_01   SET   db_type=chr(1)   WHERE   tp_01.id_no =v_t_id(i); 
                     --    
                     COMMIT; 
                      --    
                     exit   when   c%NOTFOUND; 
              END   LOOP; 
              dbms_output.put_line( ' ' ||c%rownum );      
              CLOSE   c; 
              COMMIT; 
end;  

 

转载于:https://www.cnblogs.com/yrash/p/11319837.html