用pl \ sql逐行读取Clob [英] Reading clob line by line with pl\sql

问题描述

在我的项目中，我将oracle用作主数据库，并且在解析clob时遇到了问题.因此，假设我们有一个具有价值的斗篷

In my project i use oracle as primary database and i've faced a problem with parsing clob. So suppose we have a clob with value

   aaaaaa
   cccccc
   bbbbbb

它存储在表测试中……

我需要编写plsql过程来获取该Clob并将其拆分，以便我将具有三个项的数组[aaaaaa，cccccccc，bbbbbbb].

I need to write plsql procedure to get this clob and split it so that i will have array with three items [aaaaaa,cccccccc,bbbbbbb].

有没有可能的解决方案?

Is there any possible solutions?

推荐答案

这是一段有效的代码. 我建议您出于性能目的，使用显式游标而不是隐式游标(FOR i IN(选择...)).

Here is a piece of code that works. I suggest that you use explicit cursors instead of implicit ones (FOR i IN (select...)), for performance purpose.

首先，这是创建测试用例的脚本.

First here is the script to create testcase.

create table test (c clob);

insert into test (c) values (
'azertyuiop
qsdfghjklm
wxcvbn
');

然后这是逐行读取Clob的脚本:

Then here is the script to read line by line Clob :

/* Formatted on 28/08/2012 14:16:52 (QP5 v5.115.810.9015) */
declare
    nStartIndex number := 1;
    nEndIndex number := 1;
    nLineIndex number := 0;
    vLine varchar2(2000);

    cursor c_clob is
    select c from test;

    c clob;
    -------------------------------
    procedure printout
       (p_clob in out nocopy clob) is
      offset number := 1;
      amount number := 32767;
      len    number := dbms_lob.getlength(p_clob);
      lc_buffer varchar2(32767);
      i pls_integer := 1;
    begin
      if ( dbms_lob.isopen(p_clob) != 1 ) then
        dbms_lob.open(p_clob, 0);
      end if;
      amount := instr(p_clob, chr(10), offset);
      while ( offset < len )
      loop
        dbms_lob.read(p_clob, amount, offset, lc_buffer);
        dbms_output.put_line('Line #'||i||':'||lc_buffer);
       offset := offset + amount;
       i := i + 1;
      end loop; 
          if ( dbms_lob.isopen(p_clob) = 1 ) then
        dbms_lob.close(p_clob);
      end if; 
    exception
      when others then
         dbms_output.put_line('Error : '||sqlerrm);
    end printout;
    ---------------------------
begin
    dbms_output.put_line('-----------');
    open c_clob;
    loop
       fetch c_clob into c;
       exit when c_clob%notfound;
       printout(c);
    end loop;
    close c_clob;
end;

'amount'变量用于检测行尾位置.举足轻重，在某些情况下，行尾是CHR(10)|| CHR(13)(CR + LF)，在其他情况下，只有CHR(10).

'amount' variable is used to detect end of line position. Be carfull, in some case the end of line is CHR(10)||CHR(13) (CR + LF), and in some other cases it is only CHR(10).

这篇关于用pl \ sql逐行读取Clob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！