写入和读取LOB类型的对象

====写入数据============

create or replace procedure addWaterFall
is
directions clob;
amount binary_integer;
offset integer;
first_direction varchar2(1000);
more_direction varchar2(1000);
begin
--删除已经存在的同名记录
delete from waterfalls t where t.falls_name='Munising Falls';

--插入新行，并创建文件定位符
insert into waterfalls(falls_name,falls_direction)
values ('Munising Falls',empty_clob());

--取出文件定位符
select falls_direction into directions from waterfalls t where t.falls_name='Munising Falls';

--打开lob
dbms_lob.open(directions,dbms_lob.lob_readwrite);

--写入数据
first_direction :='direction ttttttttttt';
amount :=length(first_direction);
offset :=1;
dbms_lob.write(directions,amount,offset,first_direction);

--追加数据
more_direction :='more fffffffffff';
amount :=length(more_direction);
dbms_lob.writeappend(directions,amount,more_direction);

--关闭lob
dbms_lob.close(directions);
end;

 

========读取数据================

create or replace procedure readLobTest
is
amountEveryTime constant binary_integer := 2;
readSize binary_integer := amountEveryTime;
directions clob;
resultStr varchar2(1000);
perStr varchar2(299);
offset integer;

begin
--提取定位符
select t.falls_direction into directions from waterfalls t where t.falls_name='Munising Falls';

--设置偏移量
offset :=1;

--读取clob
while(readSize = amountEveryTime)
loop
dbms_lob.read(directions,readSize,offset,perStr);
offset := offset+readSize;
resultStr :=concat(resultStr,perStr);
end loop;
dbms_output.put_line(resultStr);
end;