oracle修改Blob类型数据，批量更新数据做字符替换 |
您所在的位置：网站首页 › oracle怎么修改字段类型 › oracle修改Blob类型数据，批量更新数据做字符替换 |
oracle修改Blob类型数据，批量更新数据做字符替换
|
常规替换
函数解析： 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解决方法（参考(36条消息) ORA-22835 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小_YannSea的博客-CSDN博客_ora-22835:缓冲区对于 clob 到 char 转换或 blob 到 raw 转换而言太小） 将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;从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;编写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; |
【本文地址】
今日新闻 |
推荐新闻 |