用存储过程批量更新表

来源：互联网发布：手机淘宝如何退货退款编辑：程序博客网时间：2024/06/07 01:14

下面这个和我的很相似，不用自己写了，就用他的吧！

最近做了一个需求，需要批量更新数据库表，但是因为涉及到的数据较多(千万条)，如果直接用sql更新，估计会把pl/sql弄垮

sql如下：update online_product set online_flag = '0' where status = 'ON'

所以，写了一个存储过程，以备忘：

declare  cursor product_id_list is    select product_id from online_product where status = 'ON';  commit_count number := 0;  total_count number := 0;begin  for pid in product_id_list loop    total_count := total_count + 1;    commit_count := commit_count + 1;    update online_product setonline_flag = '0'     where status = 'ON'     and product_id = pid.product_id;    if commit_count > 1000 then      commit;      commit_count := 0;    end if;  end loop;  commit;  dbms_output.put_line('Total Count:' || total_count);end;

以 mod 判断提交更为简便（每500条提交一次）。

declare    v_count number := 0;    cursor cur_r_meas_basic_info is            select  trade_flow_no            from R_MEAS_BASIC_INFO i            where stake_no='1110102010002418' and trad_time<(select to_date('2016-05-19','yyyy-MM-dd') from dual);    begin    for i in cur_r_meas_basic_info loop       update R_TRADE_REC t set t.batch_no ='111' where t.trade_flow_no =i.trade_flow_no and t.batch_no is null;            v_count:= v_count+1;            if mod (v_count,500) = 0 then              commit;            end if;    end loop;  commit;  end;

0 0