1.该表很大，8t，由三列，其中create_time,现在要求修改成非空值，由于数据量比较大，因此采用分批来增加。
脚本如下 create or replace procedure PRC_UPDATE_CREATE_TIME is start_num integer; start_date date; total number; update_count integer; per_loop_count integer; begin dbms_output.put_line('Start to batch update');
start_num := 1; -- start value of cycle per_loop_count := 100; -- per cycle handle count start_date := to_date('20100101', 'YYYYMMDD'); -- start date of create_time
/* get total number of the update records */ select count(1) into total from tmp_loan_file_data where CREATE_TIME is null;
dbms_output.put_line('total number:'); dbms_output.put_line(total);
update_count := total/per_loop_count; if (mod(total, per_loop_count) > 0) then update_count := update_count + 1; end if;
dbms_output.put_line('loop times:'); dbms_output.put_line(update_count);
/* Loop to update records */ while start_num <= update_count loop
dbms_output.put_line('loop seq :'); dbms_output.put_line(start_num);
merge into tmp_loan_file_data src_tab using (select t.rowid as rid from tmp_loan_file_data t where t.CREATE_TIME is null and rownum >=1 and rownum <= per_loop_count) sel_tmp on (src_tab.rowid = sel_tmp.rid) when matched then update set CREATE_TIME = start_date;
start_num := start_num + 1; start_date := start_date + 1; commit; end loop;
dbms_output.put_line('End batch update'); end PRC_UPDATE_CREATE_TIME;
