Oracle BLOB字段更新方法

---

更新blob字段，在目标字符串后追加新的字符串

--CREATE TABLE blob_t AS SELECT * from tzq.act_ge_bytearray t where id_ = '34';
DECLARE
  v_clob        CLOB;
  v_varchar     VARCHAR2(32767);
  v_start       PLS_INTEGER := 1;
  v_buffer      PLS_INTEGER := 32767;
  v_blob_in     BLOB;
  v_insteadof   VARCHAR2(1000) := 'tzq_test'; --追加的字符串
  v_target_text VARCHAR(1000) := '<?xml version="1.0" encoding="UTF-8"?>'; --目标的字符串
  v_warning     INTEGER;
  dest_offset1 NUMBER := 1;
  src_offset1  NUMBER := 1;
  amount_c  INTEGER := dbms_lob.lobmaxsize;
  blob_csid NUMBER := dbms_lob.default_csid;
  lang_ctx  INTEGER := dbms_lob.default_lang_ctx;
BEGIN
  --blob转成clob
  FOR cur IN (SELECT t.id_ FROM blob_t t) LOOP
    dbms_lob.createtemporary(v_clob
                            ,TRUE);
    SELECT t.bytes_ INTO v_blob_in FROM blob_t t WHERE t.id_ = cur.id_;
    FOR i IN 1 .. ceil(dbms_lob.getlength(v_blob_in) / v_buffer) LOOP
      v_varchar := utl_raw.cast_to_varchar2(dbms_lob.substr(v_blob_in
                                                           ,v_buffer
                                                           ,v_start));
      dbms_lob.writeappend(v_clob
                          ,length(v_varchar)
                          ,v_varchar);
      dbms_output.put_line(v_varchar);
      v_start := v_start + v_buffer;
    END LOOP;
    --追加字符串
    IF dbms_lob.instr(v_clob
                     ,v_target_text) > 0 THEN
      SELECT REPLACE(v_clob
                    ,v_target_text
                    ,v_target_text || v_insteadof)
        INTO v_clob
        FROM dual;
    END IF;
    --clob转换blob
    IF dbms_lob.getlength(v_clob) > 0 THEN
      dbms_lob.createtemporary(v_blob_in
                              ,TRUE);
      dbms_lob.open(v_blob_in
                   ,dbms_lob.lob_readwrite);
      dbms_lob.converttoblob(v_blob_in
                            ,v_clob
                            ,amount_c
                            ,dest_offset1
                            ,src_offset1
                            ,blob_csid
                            ,lang_ctx
                            ,v_warning);
    ELSE
      SELECT empty_blob() INTO v_blob_in FROM dual;
    END IF;
    --转换后更新
    UPDATE blob_t t SET t.bytes_ = v_blob_in WHERE t.id_ = cur.id_;
    COMMIT;
  END LOOP;
END;