用預存程序批次更新表，預存程序批次更新表

用預存程序批次更新表，預存程序批次更新表

最近做了一個需求，需要批次更新資料庫表，但是因為涉及到的資料較多(千萬條)，如果直接用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 listSqlHelper 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();}}}