闲来无事,写了一个,不知道是不是能达到你的要求:create table t_clob (id number,str clob); insert into t_clob values(1,'aaa'||chr(10)||'bbb'||chr(10)||'ccc'); insert into t_clob values(2,'111'||chr(10)||'222'||chr(10)||'333'||chr(10)||'444'); / declare v_amount BINARY_INTEGER := 4000; v_offset INTEGER := 1; v_buffer varchar2(4000); BEGIN for j in (select id, str from (select id, regexp_substr(str, '[^' || chr(10) || ']+', 1, level) str, level lv from t_clob t CONNECT BY id = PRIOR id AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL AND LEVEL <= regexp_count(str, chr(10)) + 1) where lv = 3) loop IF dbms_lob.getlength(j.str) <= 4000 THEN dbms_lob.READ(j.str, v_amount, v_offset, v_buffer); dbms_output.put_line(j.id || ':' || v_buffer); ELSE dbms_output.put_line('文件的第三行长度超过了4000'); END IF; end loop; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line(sqlerrm); END;
insert into t_clob values(1,'aaa'||chr(10)||'bbb'||chr(10)||'ccc');
insert into t_clob values(2,'111'||chr(10)||'222'||chr(10)||'333'||chr(10)||'444');
/
declare
v_amount BINARY_INTEGER := 4000;
v_offset INTEGER := 1;
v_buffer varchar2(4000);
BEGIN
for j in (select id, str
from (select id,
regexp_substr(str,
'[^' || chr(10) || ']+',
1,
level) str,
level lv
from t_clob t
CONNECT BY id = PRIOR id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
AND LEVEL <= regexp_count(str, chr(10)) + 1)
where lv = 3) loop
IF dbms_lob.getlength(j.str) <= 4000 THEN
dbms_lob.READ(j.str, v_amount, v_offset, v_buffer);
dbms_output.put_line(j.id || ':' || v_buffer);
ELSE
dbms_output.put_line('文件的第三行长度超过了4000');
END IF;
end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(sqlerrm);
END;