批量提交（plsql，游标）

 需求：批量提交修改数据，其中需要修改的数据有 23万多条。

declare
cursor l_c1 is select u.id from 系统名（可不写）.表名 u where 条件;    //定义游标
type t1 is table of pls_integer;
  l_t1 t1;
begin
open  l_c1;
loop 
  fetch l_c1 bulk collect into l_t1 limit 1000;   //限制一次提交的数量
  forall i in 1..l_t1.count
update 系统名.表名t set t.字段="值" where t.id = l_t1(i);
commit;
exit when l_t1.count=0;
end loop;
close l_c1;
commit;
end;

实例：

declare
cursor l_c1 is select u.id from ttt.tbl_base u where u.name='7';
type t1 is table of pls_integer;
  l_t1 t1;
begin
open  l_c1;
loop 
  fetch l_c1 bulk collect into l_t1 limit 1000;
  forall i in 1..l_t1.count
update ttt.tbl t set t.value= 'SPE01' where t.id = l_t1(i);
commit;
exit when l_t1.count=0;
end loop;
close l_c1;
commit;
end;

备注：

DBA在生产库之后，会报错：ora-01722。

DBA以上实例进行了修改：

declare
cursor l_c1 is select u.id from ttt.tbl_base u where u.name='7';
type t1 is table of ttt.tbl_base .id%type;   //字段类型问题（修改了这里）。
                                              //数据库的字段id的类型是 varchar2
  l_t1 t1;
begin
open  l_c1;
loop 
  fetch l_c1 bulk collect into l_t1 limit 1000;
  forall i in 1..l_t1.count
update ttt.tbl t set t.value= 'SPE01' where t.id = l_t1(i);
commit;
exit when l_t1.count=0;
end loop;
close l_c1;
commit;
end;