更新或插入大量数据（分批提交）

最新推荐文章于 2023-09-27 23:42:09 发布

每日一禅

最新推荐文章于 2023-09-27 23:42:09 发布

阅读量1.3k

点赞数

本文链接：https://blog.csdn.net/qq_41688733/article/details/89399845

版权

–更新大量数据

create or replace procedure large_update_operation as
type rowid_list is table of urowid index by binary_integer;
rowid_infos rowid_list;
i number;
cursor c_rowids is select rowid from t_objects;
begin
open c_rowids;
loop
fetch c_rowids bulk collect into rowid_infos limit 2000;
forall i in 1..rowid_infos.count
update t_objects set owner=to_char(length(owner)+1) where rowid=rowid_infos(i);
commit;
exit when rowid_infos.count<2000;
end loop;
close c_rowids;
end;
/

–插入大量数据

create or replace procedure large_insert_operation as 
type rowid_list is table of urowid index by binary_integer;
rowid_infos rowid_list;
i number;
cursor c_rowids is select rowid from t_objects;

begin
open c_rowids;
loop
fetch c_rowids bulk collect into rowid_infos limit 2000;
forall i in 1..rowid_infos.count
insert into t_test select * from t_objects where rowid=rowid_infos(i);
commit;
exit when rowid_infos.count < 2000;
end loop;
close c_rowids;
end;
/