Oracle PL/SQL处理CLOB字段的经验

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

这段时间在客户现场处理SP处理模版内容替换的技术问题，如果是单点的处理，当然非常简单，使用replace就可以解决问题，但是需要将关键TAG替换成动态行数呢？这就比较麻烦了，下面是处理过程的记录

首先需要说明一下，其实这样的功能使用程序来处理会非常简单，但是处于对技术的追求，还是需要大胆的尝试一下的，哈哈

先想到的就是使用funcation来进行处理，将内容输入，替换后输出多简单，函数如下：
create or replace function Fun_GetTaFundnav_zj
   ---------------------------------------------------------------------------------------
    -- 版    权： 上海XXXXXXXXXXX有限公司
    -- 版    本：  XXX
    -- 开发人员： yeliang.wang
    -- 创建日期： 20090609
    -- 模块编号： 私有函数
    -- 模块名称： XXXXXXX动态数据(邮件)
    -- 描    述：
    -- 输入参数： 过滤前模板内容
    -- 输出参数： 过滤后模板内容
    ---------------------------------------------------------------------------------------
    -- 修改作者：
    -- 修改日期：
    -- 修改内容：
    ---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
(
  --i_content   clob, --过滤前模板内容
  i_fundcode  varchar2, --产品编号
  i_tradedate varchar2  ---服务类型
)
return clob
as
  newClob    clob;
  srcClob    clob;
  v_content  VARCHAR2 (32767);

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

  --对XXXXXX进行定位
  cursor r_cust is select * from XX_XX  t1 where t1.XX = i_XXX and t1.XXX in ('0','9') and t1.date = i_date;
begin
    select DESCRIPTION into srcClob from products where productid = 1;

    v_content := '<TR>';
  --生成HTML代码--
    for r in r_cust loop
      v_content := v_content||'<TD class=thtop2 vAlign=center align=middle>'||r.fundcode||'</TD>'
                            ||'<TD class=thtop2 vAlign=center align=middle>'||r.fundname||';</TD>'
                            ||'<TD class=thtop2 vAlign=center align=middle>'||r.updatedate||';</TD>'
                            ||'<TD class=thtop2 vAlign=center align=middle>'||r.fundnav||';</TD>'
                            ||'<TD class=thtop2 vAlign=center align=middle>'||r.totfundnav||';</TD>'
                            ||'<TD class=thtop2_right vAlign=center align=middle>'||r.fundrate||';</TD>';

    end loop;
    v_content := v_content||'</TR>';

    --模板转换
    --v_result := replace(i_content,'${fund_replace}',v_content);
  return newClob;
end Fun_GetTaFundnav_zj;

执行结果：ORA-03127: 在活动操作结束之前不允许进行操作

晕。。难道是不能使用自定义函数对CLOB进行替换吗？查了相关资料，也没有最后的头绪。。。突发奇想的把问题定位于FUNCTION本身，那么就使用Procedure来处理吧，并且结合dbms_lob来对clob进行处理

存储过程如下：

replaceClob_new --处理CLOB替换过程：
CREATE OR REPLACE PROCEDURE 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;

PR_INSERT --先将自己造的测试数据插入：
create or replace procedure pr_insert
 is
 
begin

        insert into tmp_XXX_info1
         (
               ..相关字段
           )
          select      seq_XXXX.nextval as id   ,
                      c.subject                      ,
                      1,
                      '[e-mail]'                        ,
                      c.CONTENT,
                      1,
                      1,
                      1      ,
                      1,
                      to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ,
                      -1                     ,
                      '',
                      ''                     ,
                      1             ,
                      1              ,
                      '',
                      '',
                      '',
                      '',
                      ''
          from XXX c;
commit;
          PR_CLOB_TEST('373010','20090121');

end pr_insert;

PR_CLOB_TEST --将模版内容进行替换：
CREATE OR REPLACE PROCEDURE PR_CLOB_TEST
(
  i_fundcode  varchar2, --产品编号
  i_tradedate varchar2  ---服务类型
)
IS
LnewClob  CLOB := EMPTY_CLOB;
LsrcClob  CLOB := EMPTY_CLOB;
varC clob;
ln number(4);
vStrt number(4);
v_content clob;

CURSOR CURSOR_TEMP_CATAN IS
 select content from tmp_email_info1;
BEGIN
    vStrt := 5;
    -- initalize the new clob
    FOR r IN (select * from XXX t1 where t1.code = i_code and t1.status in ('0','9') and t1.date = i_date and rownum < 10)
    loop
        v_content := v_content||'<TR><TD class=thtop2 vAlign=center align=middle>'||r.fundcode||'</TD>'
                              ||'<TD class=thtop2 vAlign=center align=middle>'||r.fundname||'</TD>'
                              ||'<TD class=thtop2 vAlign=center align=middle>'||r.updatedate||'</TD>'
                              ||'<TD class=thtop2 vAlign=center align=middle>'||r.fundnav||'</TD>'
                              ||'<TD class=thtop2 vAlign=center align=middle>'||r.totfundnav||'</TD>'
                              ||'<TD class=thtop2_right vAlign=center align=middle>'||r.fundrate||'</TD>'
                              ||'</TR>';
      end loop;
  
    OPEN CURSOR_TEMP_CATAN;
        LOOP
        FETCH CURSOR_TEMP_CATAN INTO
        LsrcClob;
        EXIT WHEN CURSOR_TEMP_CATAN%NOTFOUND;

        REPLACECLOB_NEW(LsrcClob,'&{fund_replace}',v_content,LnewClob);

        select content into varC from tmp_email_info1 for update;
      
        ln := Length(LnewClob);
        dbms_lob.write(varC, ln, vStrt, LnewClob);
      
        commit;
      
    END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

最终结果：测试成功

总结：整个过程就是将一个HTML模版进行关键字的HTML片段替换，是用来给数据网关发送邮件用的，在开发的过程中间遇到了很多问题（在这里不一一例举了），由于PL/SQL在处理超长字符的时候会比较多的限制，用PL/SQL来对这样的功能进行处理真的有点得不偿失，嗨。。。但是最终还是完成了，哈哈。。记录下相关操作和部分代码大家参考