oracle数据库blob格式导入、导出照片

一。创建测试表（SCOTT用户）

create table photo(

rdid nvarchar2(30) not null,

rdphoto blob,

xm varchar2(100)

);

select * from photo t;

二。创建文件所在目录，并插入图片

create or replace directory "tmp" as 'd:/tmp';

--

declare

b_file bfile;

b_lob blob;

begin

insert into photo(rdid,xm,rdphoto) values(1,'ABC', empty_blob()) 

return rdphoto into b_lob;

--为了使PL/SQL编译成功，必须先为该列插入一个empty_blob() 值，return rdphoto  into b_lob将该列与一个blog类型的变量绑定在一起，以后只要为b_lob赋值，即等于将该值插入了表中。

b_file:=bfilename('tmp','Default.jpg');

--tmp是建立的文件所在的目录，filename.jpg是文件名;将文件转换为bfile类型

dbms_lob.open(b_file,dbms_lob.file_readonly);--将b_file 以只读的方式打开

dbms_lob.loadfromfile(b_lob,b_file,dbms_lob.getlength(b_file));-- 将b_file中的内容转换到b_lob中

dbms_lob.close(b_file);

commit;

end;

三。查看结果

四。创建导出目录文件夹并授权给scott用户

五。导出图片

--

CREATE OR REPLACE PROCEDURE exphoto (xh VARCHAR2) is

l_file utl_file.file_type;

l_lob blob;

l_offset int := 1;

l_amount int := 32767;

l_len int;

l_buffer RAW (32767);

x VARCHAR2 (100);

begin

select rdphoto INTO l_lob FROM photo t WHERE rdid = xh ; 

SELECT xm INTO x FROM photo WHERE rdid = xh;

l_file := utl_file.fopen ('DIR', x || '.jpg', 'wb', 32767);

l_len := dbms_lob.getlength (l_lob);

while l_offset < l_len loop

dbms_lob.read ( l_lob, l_amount, l_offset, l_buffer );

utl_file.put_raw ( l_file, l_buffer, true );

l_offset := l_offset + l_amount;

END loop;

utl_file.fclose ( l_file );

END exphoto;

--

declare

cursor cur is

select rdid from photo where rownum <= 10 and rdphoto is not null;

begin

for rec in cur loop

exphoto(rec.rdid);

end loop;

end;