mysql更新clob 语句_修改clob字段并插入新表 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 1342964878...

最近开发有个需求，需要替换一张表的clob字段中的某些字符串，然后插入的一张新表中，我查询了一些资料，利用function结合匿名块模拟实现

0、数据库版本

SQL> select * from v$version;

BANNER

-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

1、创建替换clob中字符串函数

create or replace function replaceClob_new (

srcClob IN CLOB,

replaceStr IN VARCHAR2,

replaceWith IN VARCHAR2

--newClob OUT CLOB

) RETURN CLOB

IS

vBuffer VARCHAR2 (32767);

l_amount BINARY_INTEGER := 32767;

l_pos PLS_INTEGER := 1;

l_clob_len PLS_INTEGER;

newClob CLOB;

BEGIN

newClob := EMPTY_CLOB;

-- initalize the new clob

dbms_lob.createtemporary(newClob,TRUE);

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;

RETURN newclob;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END;

2、创建原表，并测试该函数

SQL> CREATE TABLE t_clob(ID NUMBER,clob_xff CLOB);

Table created

--手工通过plsql dev插入一些数据(大于4000)

SQL> select dbms_lob.getlength(clob_xff) from t_clob;

DBMS_LOB.GETLENGTH(CLOB_XFF)

----------------------------

4856

SQL> select dbms_lob.getlength(replaceClob_new(clob_xff,'function','1')) from t_clob;

DBMS_LOB.GETLENGTH(REPLACECLOB

------------------------------

4814

SQL> set long 50

SQL> select clob_xff from t_clob where id=1;

CLOB_XFF

--------------------------------------------------

create or replace function replaceClob_new (

srcCl

SQL> select replaceClob_new(clob_xff,'function','1')

2 from t_clob where id=1;

REPLACECLOB_NEW(CLOB_XFF,'FUNCTION','1')

--------------------------------------------------

create or replace 1 replaceClob_new (

srcClob IN C

3、编写匿名块实现插入功能

DECLARE

A_CLOB CLOB;

R_CLOB CLOB;

VSTRT NUMBER(4);

BEGIN

--否则会在新表的clob字段头部会有空格

VSTRT := 1;

SELECT REPLACECLOB_NEW(CLOB_XFF, 'function', 'xifenfei')

INTO R_CLOB

FROM T_CLOB

WHERE ID = 1;

INSERT INTO T_CLOB_NEW

(ID, CLOB_XFF)

VALUES

(1, EMPTY_CLOB())

RETURNING CLOB_XFF INTO A_CLOB;

DBMS_LOB.WRITE(A_CLOB, DBMS_LOB.GETLENGTH(R_CLOB), VSTRT, R_CLOB);

COMMIT;

END;

4、测试匿名块功能

SQL> set long 50

SQL> select clob_xff from t_clob where id=1;

CLOB_XFF

--------------------------------------------------

create or replace function replaceClob_new (

srcCl

SQL> select clob_xff from t_clob_new where id=1;

CLOB_XFF

--------------------------------------------------

create or replace xifenfei replaceClob_new (

srcCl

参考:http://space.itpub.net/111631/viewspace-605827