oracle 大字段mysql_Oracle批量更新表的大字段值

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

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语句一定要先执行。要不再做批量更新的时候会提示错误的。

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

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！