在进行大批量数据更新的时候,往往采用批量更新的方式,以下俺写了个例子,请大家参考一下:
--创建表
create table test1
(
a varchar2(100),
b varchar2(100)
);
create table test2
(
a varchar2(100),
b varchar2(100)
);
--插入语句
declare i number;
begin
for i in 1..10 loop
insert into test1(a,b)values(i,i);
end loop;
for i in 1..5 loop
insert into test2(a,b)values(i,i||i);
end loop;
end;
--查询一下:
select t.*,t.rowid from test1 t
select t.*,t.rowid from test2 t
--全都更新的方式:
update test1 t1
set t1.b = (select t2.b from test2 t2 where t1.a = t2.a)
--如果没有以下语句结果就不一样了,请注意
where exists
(select '' from test2 t2 where t1.a = t2.a);
--开始批量更新
declare
countpoint number := 0;
begin
for row_id in (select t1.rowid,t2.b from test1 t1,test2 t2 where t1.a = t2.a) loop
update test1 t1
set t1.b = row_id.b
where t1.rowid = row_id.rowid;
countpoint := countpoint + 1;
if mod(countpoint, 5) = 0 then
dbms_output.put_line('countpoint:' || countpoint);
commit;
end if;
end loop;
commit;
end;
--删除数据
delete test1;
delete test2;
--删除表
drop table test1;
drop table test2;