一。創(chuàng)建測試表（SCOTT用戶）

create table photo(

rdid nvarchar2(30) not null,

rdphoto blob,

xm varchar2(100)

);

select * from photo t;

二。創(chuàng)建文件所在目錄，并插入圖片

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是文件名;將文件轉(zhuǎn)換為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中的內(nèi)容轉(zhuǎn)換到b_lob中

dbms_lob.close(b_file);

commit;

end;

三。查看結(jié)果

四。創(chuàng)建導(dǎo)出目錄文件夾并授權(quán)給scott用戶

五。導(dǎo)出圖片

--

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;