联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近开发有个需求,需要替换一张表的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