oracle中游标遍历update更新,如何执行1W条后commit一次

Question

我准备在oracle中使用游标遍历的方式将user1用户下的test1表scycjg字段update为'0' 现在要求我1W条提交一次，不用逐条commit; 请各位大神帮忙改一下脚本来实现，谢谢！！！

DECLARE
CURSOR UPDATE_CURSOR IS
select id from user1.test1
where sc_updated_date < to_date('2016-7-01','yyyy-mm-dd');
T_ROW UPDATE_CURSOR%ROWTYPE;
BEGIN
OPEN UPDATE_CURSOR;
LOOP
FETCH UPDATE_CURSOR INTO T_ROW;
EXIT WHEN UPDATE_CURSOR%NOTFOUND;
update user1.test1
set scycjg='0'
where id=t_row.id;
END LOOP;
CLOSE UPDATE_CURSOR;
END;

Answer 1

修改后的如下 可以参考下

DECLARE
CURSOR UPDATE_CURSOR1 IS
select id from user1.table1;
T_ROW1 UPDATE_CURSOR1%ROWTYPE;
row_num1 int := 0;
BEGIN
OPEN UPDATE_CURSOR1;
LOOP
FETCH UPDATE_CURSOR1 INTO T_ROW1;
EXIT WHEN UPDATE_CURSOR1%NOTFOUND;
update user1.table1
set x='0'
where id=t_row1.id;
row_num1:=row_num1 + 1;
if row_num1=10000 then
commit;
row_num1:= 0;
end if;
commit;
END LOOP;
CLOSE UPDATE_CURSOR1;
END;