Use a stored procedure to update Tables in batches and a stored procedure to update Tables in batches

Use a stored procedure to update Tables in batches and a stored procedure to update Tables in batches

Recently, we have made a demand to update database tables in batches. However, because there are a large amount of data involved (tens of millions of records), if we use SQL update directly, it is estimated that pl/SQL will be broken down.

The SQL statement is as follows: update product set online_flag = '0' where status = 'on'

Therefore, I wrote a stored procedure to forget:

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;

You can also perform batch update by writing a Java program:

Public void batchUpdate () {List <Long> ppList = new ArrayList <Long> (); // The product id listSqlHelper helper = new SqlHelper (ConfigManager) should be obtained after the query. 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 = pp List. size ()-1) | (I % 1000 = 0))helhelper.exe cuteBatch (); helper. commit (); helper = new SqlHelper (ConfigManager. getStringProperty ("DBName") ;}} catch (Exception e) {logger. error ("catch exception... ", e) ;}finally {if (helper! = Null) {helper. close ();}}}