oracle如何修改Blob类型数据,批量更新数据做字符替换_Oracle

常规替换

函数解析

utl_raw.cast_to_raw (blob字段) : 将blob字段转为字符串
replace(string, str, tgt) : 替换函数, string中的str 替换成tgt
utl_raw.cast_to_raw(string) : 将字符串转为raw类型

update 表 a 
set a.blob字段 = utl_raw.cast_to_raw ( replace ( utl_raw.cast_to_varchar2 ( a.blob字段 ), '目标字段', '要替换成的字段' ) ) 
where 条件

blob大数据替换

用上述方法blob数据过多时会出现以下报错：

ora-22835: 缓冲区对于 clob 到 char 转换或 blob 到 raw 转换而言太小 (实际: 4786, 最大: 2000)

to_char方法将clob类型转换成varchar类型出了问题，oracle中varchar最大的长度是4000。

把clob大文本类型直接转换成varcahr类型时，如果clob的长度小于4000，没有超过varchar的最大值，不会出问题，一旦超过4000就会报错

sql解决方法

1.将blob转换成clob

-- typecasts blob to clob (binary conversion)
-- blob转换为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转成varchar2

-- clob转成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
--当转换出来的字符串乱码时，可尝试用注释掉的函数
--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 = 条件

多数据更新场景

用上述方法一次仅仅可更新一条数据，如果需要批量更新数据，则编写存储过程：

create or replace procedure update_content is
-- 定义游标（需要批量修改的数据）
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;
 
-- 执行存储过程 
begin 
update_content;
end;