用存储过程批量更新表，存储过程批量更新表

用存储过程批量更新表，存储过程批量更新表

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

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

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

declare
  cursor product_id_list is
    select product_id
      from 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 product
       set online_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;

也可以通过写Java程序进行批量更新：

public void batchUpdate(){
		List<Long> ppList = new ArrayList<Long>(); // 此处应为查询后获取一个product id list
		SqlHelper helper = new SqlHelper(ConfigManager.getStringProperty("DBName"));
		try {
			for(int i = 0; i < ppList.size(); i++){
				Long productId = ppList.get(i);
				String updateSql = "update product set online_flag = '0' " +
						           " where status = 'ON' and product_id = " + productId;
				helper.addBatch(updateSql);
				if((i == ppList.size() - 1) || (i % 1000 == 0)){
					helper.executeBatch();
					helper.commit();
					helper = new SqlHelper(ConfigManager.getStringProperty("DBName"));
				}
			}
		} catch (Exception e) {
			logger.error("catch exception...", e);
		} finally {
			if (helper != null) {
				helper.close();
			}
		}
	}