快捷導(dǎo)航

oracle如何修改Blob類型數(shù)據(jù),批量更新數(shù)據(jù)做字符替換

更新時(shí)間：2024年07月02日 15:55:44 作者：Tine.

這篇文章主要介紹了oracle如何修改Blob類型數(shù)據(jù),批量更新數(shù)據(jù)做字符替換問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

常規(guī)替換

函數(shù)解析

Utl_Raw.CAST_TO_RAW (blob字段) : 將blob字段轉(zhuǎn)為字符串
REPLACE(string, str, tgt) : 替換函數(shù), string中的str 替換成tgt
Utl_Raw.CAST_TO_RAW(string) : 將字符串轉(zhuǎn)為raw類型

UPDATE 表 a 
SET a.blob字段 = Utl_Raw.CAST_TO_RAW ( REPLACE ( Utl_Raw.Cast_To_Varchar2 ( a.blob字段 ), '目標(biāo)字段', '要替換成的字段' ) ) 
WHERE 條件

Blob大數(shù)據(jù)替換

用上述方法BLOB數(shù)據(jù)過多時(shí)會出現(xiàn)以下報(bào)錯：

ORA-22835: 緩沖區(qū)對于 CLOB 到 CHAR 轉(zhuǎn)換或 BLOB 到 RAW 轉(zhuǎn)換而言太小 (實(shí)際: 4786, 最大: 2000)

to_char方法將clob類型轉(zhuǎn)換成varchar類型出了問題，oracle中varchar最大的長度是4000。

把clob大文本類型直接轉(zhuǎn)換成varcahr類型時(shí)，如果clob的長度小于4000，沒有超過varchar的最大值，不會出問題，一旦超過4000就會報(bào)錯

sql解決方法

1.將BLOB轉(zhuǎn)換成CLOB

-- typecasts BLOB to CLOB (binary conversion)
-- blob轉(zhuǎn)換為clob的方法
CREATE OR REPLACE FUNCTION C2B ( b IN CLOB DEFAULT empty_clob ( ) ) RETURN BLOB IS res BLOB;
b_len NUMBER := dbms_lob.getlength ( b );
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;
warning INTEGER;
BEGIN
    IF b_len > 0 THEN
        DBMS_LOB.createtemporary ( res, TRUE );
        DBMS_LOB.OPEN ( res, DBMS_LOB.lob_readwrite );
        DBMS_LOB.convertToBlob ( res, b, amount_c, dest_offset1, src_offset1, blob_csid, lang_ctx, warning );
    ELSE 
    SELECT empty_blob ( ) INTO res FROM dual;
    END IF;
    RETURN res;-- res is OPEN here
END C2B;

2.從clob轉(zhuǎn)成varchar2

-- clob轉(zhuǎn)成varchar2的方法
CREATE OR REPLACE FUNCTION Blob_To_Varchar ( Blob_In IN Blob ) Return clob IS V_Varchar VARCHAR2 ( 32767 );
V_Varchar1 VARCHAR2 ( 32767 );
V_Start Pls_Integer := 1;
V_Buffer Pls_Integer := 4000;
BEGIN
    IF Dbms_Lob.Getlength ( Blob_In ) IS NULL 
    THEN Return '';
    END IF;
    V_Varchar1 := '';
--return to_char(Ceil(Dbms_Lob.Getlength(Blob_In) / V_Buffer));
    FOR I IN 1..Ceil ( Dbms_Lob.Getlength ( Blob_In ) / V_Buffer )
    Loop
--當(dāng)轉(zhuǎn)換出來的字符串亂碼時(shí)，可嘗試用注釋掉的函數(shù)
--V_Varchar := Utl_Raw.Cast_To_Varchar2(Utl_Raw.Convert(Dbms_Lob.Substr(Blob_In, V_Buffer, V_Start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));
    V_Varchar := Utl_Raw.Cast_To_Varchar2 ( Dbms_Lob.Substr( Blob_In, V_Buffer, V_Start ) );
    V_Varchar1 := V_Varchar1 || V_Varchar;
    V_Start := V_Start + V_Buffer;
END Loop;
Return V_Varchar1;
END Blob_To_Varchar;

3.編寫sql

UPDATE 表名 
SET blob字段 = C2B ( to_clob( ( SELECT REPLACE ( Blob_To_Varchar ( blob字段 ), '要被替換的值', '替換值' ) FROM 表名 WHERE 條件 ) ) ) 
WHERE RP_CODE = 條件

多數(shù)據(jù)更新場景

用上述方法一次僅僅可更新一條數(shù)據(jù)，如果需要批量更新數(shù)據(jù)，則編寫存儲過程：

CREATE OR REPLACE PROCEDURE update_Content is
-- 定義游標(biāo)（需要批量修改的數(shù)據(jù)）
cursor cur IS SELECT * FROM T_PRESCRIPTION_INFO;
BEGIN
    FOR temp IN cur
    LOOP
    -- dbms_Output.put_line ( temp.RP_CODE );
    UPDATE T_PRESCRIPTION_INFO 
    SET RP_CONTENT = c2b ( to_clob( ( SELECT REPLACE ( Blob_To_Varchar ( blob字段 ), '要被替換的值', '替換值'  ) FROM T_PRESCRIPTION_INFO WHERE RP_CODE = temp.RP_CODE ) ) ) 
    WHERE RP_CODE = temp.RP_CODE;        
END LOOP;
END update_Content;
 
-- 執(zhí)行存儲過程 
BEGIN 
update_Content;
end;