访问LOB

CLOB用于存放大批量的文本数据，所允许的最大数据长度为4G字节。

1-建立包含CLOB列的表

SQL> create table lob_example1(
  2  id number(6) primary key,
  3  name varchar2(10),
  4  resume clob);
 
Table created

2-初始化CLOB列

SQL> insert into lob_example1 values(1,'王鸣',empty_clob());
 
1 row inserted
 
SQL> insert into lob_example1 values(2,'玛丽',empty_clob());
 
1 row inserted
 
SQL> commit;
 
Commit complete

3-更新CLOB列的数据

SQL> declare
  2  lob_loc clob;
  3  text varchar2(200);
  4  amount int;
  5  offset int;
  6  begin
  7  select resume into lob_loc from lob_example1
  8  where id=&id for update;
  9  offset:=dbms_lob.getlength(lob_loc)+1;
 10  text:='&resume';
 11  amount:=length(text);
 12  dbms_lob.write(lob_loc,amount,offset,text);
 13  commit;
 14  end;
 15  /

4读取CLOB列的内容

SQL> declare 
  2  lob_loc clob;
  3  buffer varchar2(200);
  4  amount int;
  5  offset int;
  6  begin
  7   select resume into lob_loc from lob_example1
  8  where id=&id;
  9  offset:=6;
 10  amount:=dbms_lob.getlength(lob_loc);
 11  dbms_lob.read(lob_loc,amount,offset,buffer);
 12  dbms_output.put_line(buffer);
 13  end;
 14  /

5-将文本文件内容写入到CLOB列

SQL> declare
  2  lobloc clob;
  3  fileloc bfile;
  4  amount int;
  5  src_offset int:=1;
  6  dest_offset int:=1;
  7  csid int:=0;
  8  lc int:=0;
  9  warning int;
 10  begin
 11   fileloc:=bfilename('G','玛丽.txt');
 12  dbms_lob.fileopen(fileloc,0);
 13  amount:=dbms_lob.getlength(fileloc);
 14  select resume into lobloc from lob_example1
 15  where id=2 for update;
 16  dbms_lob.loadbclobfromfile(lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,warning);
 17  dbms_lob.fileclose(fileloc);
 18  commit;
 19  end;
 20  /