问题描述
在我的项目中,我将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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!