Oracle批量更新表的大字段值

blob類(lèi)型字段
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類(lèi)型字段
1.前提 id=316688448842 的clob類(lèi)型的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語(yǔ)句一定要先執(zhí)行。要不再做批量更新的時(shí)候會(huì)提示錯(cuò)誤的。

這里更新的值為同一個(gè)值可以在循環(huán)的時(shí)候獲取不同的值來(lái)進(jìn)行更新

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]