CLOB and BFILE

create table clob_table
(clob_no number(4) primary key,
 clob_name varchar2(20),
 clob_descr clob
 )lob(clob_descr) store as segname (disable storage in row);
 

insert into clob_table(clob_no,clob_name) values (1001,'王明');

insert into clob_table(clob_no,clob_name,clob_descr) values (1002,'马丽','出生于1978年');

insert into clob_table(clob_no,clob_name,clob_descr) values (1003,'马丽',empty_clob());

update clob_table set clob_descr=empty_clob() where clob_no=1001;

2、为CLOB列追加数据
create or replace procedure add_content(no number,text varchar2)
is
  clob_loc clob;
  amount int;
  offset int;
begin
     select clob_descr into clob_loc from clob_table where clob_no=no for update;
     offset := dbms_lob.getlength(clob_loc)+1;
     amount := length(text);
     dbms_lob.write(clob_loc,amount,offset,text);
     commit;
end;
/
exec add_content(1001,'出生于1978年，毕业于哈尔滨工业大学');
exec add_content(1001,',2000年参加工作');

3、读取CLOB列内容
create or replace procedure get_content(no number)
is
  type content_table_type is table of varchar2(100) index by binary_integer;
  content_table content_table_type;
  clob_loc clob;
  len int;
  amount int;
  offset int:=1;
  temp int;
begin
     select clob_descr into clob_loc from clob_table where clob_no=no;
     len:=dbms_lob.getlength(clob_loc);
     temp:=trunc((len-1)/10);
     for i in 1..(temp+1) loop
         if trunc((len-offset+1)/10)>0 then
            amount:=10;
            dbms_lob.read(clob_loc,amount,offset,content_table(i));
            offset:=10*i+1;
            dbms_output.put_line(content_table(i));
         else
              amount:=len-offset+1;
              dbms_lob.read(clob_loc,amount,offset,content_table(i));
              dbms_output.put_line(content_table(i));
         end if;
     end loop;
end;
/
exec get_content(1001);

4、将CLOB列写入OS文件
create or replace procedure get_content(no number)
is
  type content_table_type is table of varchar2(100) index by binary_integer;
  content_table content_table_type;
  clob_loc clob;
  len int;
  amount int;
  offset int:=1;
  temp int;
  bfile_txt utl_file.file_type;
begin
     select clob_descr into clob_loc from clob_table where clob_no=no;
     bfile_txt:=utl_file.fopen('DIR1',no||'.txt','a');
     len:=dbms_lob.getlength(clob_loc);
     temp:=trunc((len-1)/10);
     for i in 1..(temp+1) loop
         if trunc((len-offset+1)/10)>0 then
            amount:=10;
            dbms_lob.read(clob_loc,amount,offset,content_table(i));
            offset:=10*i+1;
            utl_file.put_line(bfile_txt,content_table(i));
         else
              amount:=len-offset+1;
              dbms_lob.read(clob_loc,amount,offset,content_table(i));
              utl_file.put_line(bfile_txt,content_table(i));
         end if;              
     end loop;
     commit;
     utl_file.fclose(bfile_txt);
end;
/
exec get_content(1001);

5、BFILE的使用

create table bfile_table
(bfile_no number(4) primary key,
 bfile_name varchar2(20),
 resume bfile
 );
 
insert into bfile_table values(1001,'张三',bfilename('DIR1','张三.txt'));

将BFIL_TABLE表的RESUME列的内容导入CLOB_TABLE表的clob_descr列中
declare
  clob_loc clob;
  bfile_loc bfile;
  amount int;
  src_offset int:=1;
  dest_offset int:=1;
  csid int:=0;
  lc int:=0;
  warning int;
  no int:=&id;
begin
     select resume into bfile_loc from bfile_table where bfile_no=no;
     dbms_lob.fileopen(bfile_loc,0);
     amount:=dbms_lob.getlength(bfile_loc);
     select clob_descr into clob_loc from clob_table where clob_no=no for update;
     dbms_lob.loadclobfromfile(clob_loc,bfile_loc,amount,dest_offset,src_offset,csid,lc,warning);
     dbms_lob.fileclose(bfile_loc);
     commit;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17012874/viewspace-693814/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17012874/viewspace-693814/