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語句一定要先執行。要不再做批量更新的時候會提示錯誤的。

這里更新的值為同一個值可以在循環的時候獲取不同的值來進行更新

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]