對 Clob 欄位的大規模更新 oracle 10g

我需要從 oracle 10g 中的 2000+ clob 欄位中刪除一串字元。我將如何在 PL/SQL 中執行此操作？

例如，在表 t1 中有一個名為 VBase 的 clob 欄位需要將字元串“.magic()”替換為“”。

每個領域都是這樣的。

--Setup.
DROP TABLE t1;
CREATE TABLE t1 (ID Number(4), VBase Clob);
INSERT INTO t1 (SELECT level, '*' || '.magic()' || '*' FROM dual 
  connect by level <=2001);
COMMIT;
SELECT * FROM t1;

--Replace.
DECLARE
  cBatchSize CONSTANT Number(3) := 100;
  Cursor vQuery Is SELECT ID, VBase FROM t1 FOR UPDATE;   
  Type tTable Is Table Of t1%rowtype;
  vData tTable;
  vNew Clob;
  vLocation Number(10);
  vLobLength Number(10);

  Procedure LobReplace(ioLob In Out Clob, iOld In Varchar2, iNew In Varchar2 )
  As
      vPosition number;
  Begin
      vPosition := dbms_lob.instr(ioLob, iOld);
      If (nvl(vPosition,0) > 0) Then
          DBMS_Lob.Copy( ioLob, ioLob, dbms_lob.getlength(ioLob)
             , vPosition+length(iNew), vPosition+length(iOld) );
          DBMS_Lob.Write( ioLob, length(iNew), vPosition, iNew );
          If (Length(iOld) > Length(iNew)) Then
              DBMS_Lob.Trim(ioLob
                 , dbms_lob.getlength(ioLob) - (length(iOld)-length(iNew)));
          End If;
      End If;
  End;


BEGIN
  Open vQuery;
  Loop
     Fetch vQuery Bulk Collect Into vData Limit cBatchSize;
     For i In 1..vData.Count() Loop
        LobReplace(vData(i).VBase,'.magic()',' ');
     End Loop;
     ForAll i In 1..vData.Count
        UPDATE t1 SET vBase = vData(i).VBase WHERE ID = vData(i).Id;
     Exit When vData.Count() < cBatchsize;
  End Loop;
  Close vQuery;

  COMMIT;
END;
/

--Verify.
SELECT * FROM t1;

引用自：https://dba.stackexchange.com/questions/28159