oracle怎么进行批量更新,ORACLE批量更新四种方法比较

技术

/AlexLin 发表于2007-11-28, 17:39

现在我们有2张表 如下：

T1--大表 10000笔 T1_FK_ID

T2--小表 5000笔   T2_PK_ID

T1通过表中字段ID与T2的主键ID关联

模拟数据如下：

--T2

有

5000

笔数据

create table T2

as

select rownum id, a.*

from all_objects a

where 1=0;

-- Create/Recreate primary, unique and foreign key constraints

alter table T2

add constraint T2_PK_ID primary key (ID);

insert /*+ APPEND */ into T2

select rownum id, a.*

from all_objects a where rownum<=5000;

--T1

有

10000

笔数据

create table T1

as

select rownum sid, T2.*

from T2

where 1=0;

-- Create/Recreate primary, unique and foreign key constraints

alter table T1

add constraint T1_FK_ID foreign key (ID)

references t2 (ID);

insert /*+ APPEND */ into T1

select rownum sid, T2.*

from T2;

insert /*+ APPEND */ into T1

select rownum sid, T2.*

from T2;

--

更新

Subobject_Name

字段，之前为

null

update T2 set T2.Subobject_Name='StevenHuang'

我们希望能把

T1

的

Subobject_Name

字段也全部更新成

'StevenHuang'

，也就是说

T1

的

10000

笔数据都会得到更新

方法一

写

PL/SQL,

开

cursor

declare

l_varID varchar2(20);

l_varSubName varchar2(30);

cursor mycur is select T2.Id,T2.Subobject_Name from T2;

begin

open mycur;

loop

fetch mycur into l_varID,l_varSubName;

exit when mycur %notfound;

update T1 set T1.Subobject_Name = l_varSubName where T1.ID = l_varID;

end loop;

close mycur;

end;

---

耗时

39.716s

显然这是最传统的方法，如果数据量巨大的话(

4000

万笔)，还会报

”snapshot too old”

错误退出

方法二.

用

loop

循环，分批操作

declare

i