Clob字段中字符替换处理

来源：互联网发布：日剧台词知乎编辑：程序博客网时间：2024/05/17 08:58

同事做网站处理，历史数据需要导入到新系统的数据库中，所以问题就来了，

以前下载地址的ip需要修改，但是这些都存在clob中，需要替换。

所以我就查资料，写了两个测试过程，解决了问题。

贴到这里共享给大家也方便自己查找

建表脚本：

CREATE TABLE PRODUCTS( PRODUCTID NUMBER(10) NOT NULL, NAME VARCHAR2(255 BYTE), DESCRIPTION CLOB);数据：insert into products values(1,'aaaaa',to_clob('afdsafds,catan你好http://92.16.16.164'));insert into products values(2,'aaaaa',to_clob('你好啊，服了吧哈哈http://92.16.16.164'));insert into products values(3,'aaaaa',to_clob('你好啊，服了http://92.16.16.164'));insert into products values(4,'aaaaa',to_clob('你好啊http://92.16.16.164'));insert into products values(5,'aaaaa',to_clob('你好啊,你好啊，你好http://92.16.16.164'));insert into products values(6,'aaaaa',to_clob('http://92.16.16.164afdsafds,catan你好http://92.16.16.164'));insert into products values(7,'aaaaa',to_clob('你好啊，http://92.16.16.164服了吧哈哈http://92.16.16.164'));insert into products values(8,'aaaaa',to_clob('你好啊http://92.16.16.164，服了http://92.16.16.164'));insert into products values(9,'aaaaa',to_clob('你好http://92.16.16.164啊http://92.16.16.164'));insert into products values(10,'aaaaa',to_clob('你好啊,你好啊，http://92.16.16.164你好http://92.16.16.164'));

过程脚本：REATE OR REPLACE PROCEDURE JUSTDOIT
IS
LnewClob CLOB:= EMPTY_CLOB;
LsrcClob CLOB := EMPTY_CLOB;

CURSOR CURSOR_TEMP_CATAN IS
-- select DESCRIPTION from PRODUCTS where ;
select DESCRIPTION from products where dbms_lob.instr(products.description,'92.16.16.164',1,1) > 0;

BEGIN
-- initalize the new clob

OPEN CURSOR_TEMP_CATAN;
      LOOP
      FETCH CURSOR_TEMP_CATAN INTO
      LsrcClob;
      EXIT WHEN CURSOR_TEMP_CATAN%NOTFOUND;
-- dbms_output.put_line('srcCloba');
    dbms_output.put_line(LsrcClob);

REPLACECLOB_NEW(LsrcClob,'92.16.16.164','92.16.16.163',LnewClob);
     dbms_output.put_line(LnewClob);

END LOOP;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/

CREATE OR REPLACE PROCEDURE CATAN.replaceClob_new (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2,
newClob OUT CLOB
)
IS

vBuffer    VARCHAR2 (32767);
l_amount   BINARY_INTEGER := 32767;
l_pos      PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;

BEGIN
   newClob := EMPTY_CLOB;
-- initalize the new clob


dbms_lob.createtemporary(newClob,TRUE);
--newClob := EMPTY_CLOB;
--srcClob := EMPTY_CLOB;
l_clob_len := dbms_lob.getlength(srcClob);

WHILE l_pos < l_clob_len
LOOP
dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/

问题：

一开始把整个处理写到一个过程里了，处理总有问题，不是丢字节，就是只能更新几条。

后来拆分后解决了。^_^！

Clob字段中字符替换 处理

Clob字段中字符替换处理