oracle大批量数据更新

元娅萍

比如现在对一个表增加一个流水字段，非空，唯一。
该表数据量为3000000.
假设表名为test。
1.使用cursor。
declare
cursor c_test is select rowid from test;
v_test c_test%rowtype;
begin
open c_test;
loop
fetch c_test into v_test;
exit when c_test%notfound;
update test set sn = test_seq.nextval;
end loop;
close c_test;
end;
2.使用between ...and。
declare
--总的记录数
v_total number(14,0) := 0;
--当前记录index
v_curr number(14,0) := 0;
--记录上一次更新的位置
v_pri number(14,0) := 0;
begin
--查出总共的记录数。
select count(*) from test into v_total;
for i in 1..v_total loop
v_curr := v_curr + 1;
if v_curr mod 100000 = 0 then
update test set sn = test_seq.nextval where rownum between v_pri and v_curr;
end if;
--下一次更新开始的位置就是本次更新结束的位置
v_pri := v_curr;
end loop;
--需要处理最后一部分数据，因为是100000次一提交，可能最后一部分不足100000，需要单独处理。
update test set sn = test_seq.nextval where rownum between v_pri and v_total;
end;
3.每一次更新都会查目前没有更改的记录数。
declare
--目前表中没有更改的记录数
v_not_updated_count number(14,0) := 0;
begin
loop
select count(*) into v_not_updated_count from test where sn is null;
update test set sn = test_seq.nextval where rownum <= 100000 and sn is null;
exit when v_not_updated = 0;
end loop;
end;

效率比较：
1和2可能效率差不多。大概要1个半小时。
3需要10分钟左右。