Oracle批量更新表的大字段值

Oracle批量更新表的大字段值

blob类型字段
1.前提 id=316083730656 的content字段有值
update docs set content=Empty_Blob()
where id<>316083730656

Declare
src_Blob Blob;
v_Blob Blob;
Amount Binary_Integer;
Offset Integer;
cursor testCursor is
select content
from docs
where  id<>316083730656 and format='.pdf'
for update ;
Begin
Select content Into src_Blob From docs Where Id = 316083730656 for update;
open testCursor;
loop
fetch testCursor into v_Blob ;
DBMS_LOB.OPEN(src_Blob, DBMS_LOB.LOB_READWRITE);
Dbms_Lob.Open(v_Blob, Dbms_Lob.Lob_Readwrite);
Amount := Lengthb(src_Blob);
Offset := 1;
Dbms_Lob.copy(v_Blob,src_Blob, Amount, Offset,Offset);
Dbms_Lob.Close(v_Blob);
Dbms_lob.close(src_Blob);
exit when testCursor%notfound;
end loop;
close testCursor;
End;

clob类型字段
1.前提 id=316688448842 的clob类型的Abstract字段字段有值
update docs set Abstract=Empty_clob()
where id <> 316688448842

declare
src_Clob Clob;
v_Clob Clob;
Amount Integer;
Offset Integer;
cursor testCursor is
select Abstract
from docs
where  id<>316688448842
for update ;
Begin
Select Abstract Into src_Clob From docs Where Id = 316688448842 for update;
open testCursor;
loop
fetch testCursor into v_Clob ;
DBMS_LOB.OPEN(src_Clob, DBMS_LOB.LOB_READWRITE);
Dbms_Lob.Open(v_Clob, Dbms_Lob.Lob_Readwrite);

Dbms_Lob.append(v_Clob,src_Clob);
Dbms_Lob.Close(v_Clob);
Dbms_lob.close(src_Clob);
exit when testCursor%notfound;
end loop;
close testCursor;
End;

注意。前面的update语句一定要先执行。要不再做批量更新的时候会提示错误的。

这里更新的值为同一个值可以在循环的时候获取不同的值来进行更新。