当前位置：首页 > 数据库 > 正文内容

oracle怎么修改Blob类型数据,批量更新数据做字符替换

2024-11-30数据库2

常规替换

函数解析

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;

Navicat for MySQL管理软件安装使用教程图解2024-11-08

PyMySQL数据库的使用2024-11-08

mysql管理工具有哪些?哪个好用？2024-11-09

linux数据库管理软件推荐2024-11-09

SQL中的INNER JOIN用法详解2024-11-29

SQL Server 数据太多怎么优化2024-11-29

CentOS 9 Stream 安装部署 SQL Server 20192024-11-29

SQL server 中 CROSS APPLY的操作2024-11-29

SQL Duplicate entry for key ‘PRIMARY‘主键重复报错怎么解决2024-11-29

广为博客