超过千万记录的表，更新时要使用存储过程分段处理

 

针对数据量超过千万的表，更新时要分段处理，防止锁死数据库；此例 是每年12个月分开处理 

CREATE OR REPLACE FUNCTION "public"."jcy_clean"()
  RETURNS "pg_catalog"."void" AS $BODY$
    -- Routine body goes here...
    declare 
    flowmonth text;
     i int;
    
     BEGIN
      i=1;
    while i<=12 loop  
        flowmonth = '2022'||right('0'||cast(i as text),2);
        RAISE NOTICE '正在处理%月份的数据', flowmonth;
        update jcy_yjlx set yjlx_wllb=b.category from goods as b 
   where  yjlx_lxqj=flowmonth and yjlx_wlbh=b.code ;
        i= i+1;
    end loop;
    



END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100