--创建目录对象dir1并授权
create or replace directory dir1 as 'D:\';
grant read,write on directory dir1 to scott;
grant read on directory dir1 to public;

1、创建表
create table temp_pic
(pic_no number(6),
 pic_name varchar2(20),
 pic blob,
 constraint pk_pic primary key (pic_no)
 );
/

2、创建过程向表中插入图片
例一：
create or replace procedure insert_blob_files(pic_id number,pic_name varchar2,file_name varchar2)
is
 bfile_name bfile; --存放指向OS文件的指针
 blob_file blob;  
begin
 bfile_name:=bfilename('DIR1',file_name); --初始化bfile_name
 insert into temp_pic(pic_no,pic_name,pic) values(pic_id,pic_name,empty_blob())
  returning pic into blob_file;
 dbms_lob.fileopen(bfile_name,0);
 dbms_lob.loadfromfile(blob_file,bfile_name,dbms_lob.getlength(bfile_name));
 commit;
end;
/
exec insert_blob_files(&pic_id,&pic_name,&file_name);

exec insert_blob_files(100001,'美女','美女.jpg');
exec insert_blob_files(100002,'古典','古典.jpg');
exec insert_blob_files(100003,'展览','展览.jpg');
exec insert_blob_files(100004,'战国','战国.jpg');
exec insert_blob_files(100005,'神泣','神泣.jpg');
exec insert_blob_files(100006,'美白','美白.jpg');
exec insert_blob_files(100007,'时尚','时尚.jpg');
exec insert_blob_files(100008,'焰火','焰火.jpg');

例二：
insert into temp_pic (pic_no,pic_name,pic) values (100005,'美女1',empty_blob());
commit;

create or replace procedure load_photo(no number,filename varchar2)
is
 bfile_name bfile;
 blob_file blob;
 amount int;
 src_offset integer:=1;
 dest_offset integer:=1;
begin
 select pic into blob_file from temp_pic where pic_no=no for update;
 bfile_name:=bfilename('DIR1',filename);
 dbms_lob.fileopen(bfile_name,0);
 amount:=dbms_lob.getlength(bfile_name);
 dbms_lob.loadblobfromfile(blob_file,bfile_name,amount,dest_offset,src_offset);
 dbms_lob.fileclose(bfile_name);
 commit;
end;
/
exec load_photo(100005,'美女1.jpg'); 

3、创建过程从表中导出图片
create or replace procedure download_pic (no number)
is
 files utl_file.file_type;  --存放指向OS文件的指针
 photo blob;
 photo_name varchar2(10);
 photo_length integer;   --文件大小   
 photo_buffer raw(32767);  --存放raw数据的缓冲区
 photo_amount binary_integer := 32767; --用于指定一次要读取的总字节/字符数
 photo_start integer := 1;  --用于指定要读取的其始位置
 
begin
 select pic_name,pic into photo_name,photo from temp_pic where pic_no=no;
 
 photo_length := dbms_lob.getlength(photo);
 files := utl_file.fopen('DIR1',photo_name||'.jpg','wb');

 while photo_start < photo_length loop
  dbms_lob.read(photo,photo_amount,photo_start,photo_buffer);
  utl_file.put_raw(files,photo_buffer,false);--将RAW缓冲区的数据写入到OS文件
  photo_start := photo_start + photo_amount;
 end loop;

 utl_file.fclose(files);
exception
 when others then
  if utl_file.is_open(files) then
   utl_file.fclose(files);
  end if;
 raise;
end;
/
exec download_pic(100001);

4、批量导出图片
create or replace procedure download_all_pic
is
 files utl_file.file_type;  --存放指向OS文件的指针
 type photo_type is table of blob index by binary_integer;
 photo photo_type;
 type photo_name_type is table of varchar2(20) index by binary_integer;
 photo_name photo_name_type;
 photo_buffer raw(32767);
 photo_length integer;   --文件大小 
 photo_amount binary_integer := 32767; --用于指定一次要读取的总字节/字符数 
 photo_start integer;  --用于指定要读取的其始位置
begin
 select pic_name,pic bulk collect into photo_name,photo from temp_pic;
 for i in 1..photo.count loop 
  photo_length := dbms_lob.getlength(photo(i));
  files := utl_file.fopen('DIR1',photo_name(i)||'.jpg','wb');
  photo_start:= 1;
  while photo_start < photo_length loop
   dbms_lob.read(photo(i),photo_amount,photo_start,photo_buffer);
   utl_file.put_raw(files,photo_buffer,false);
   photo_start := photo_start + photo_amount;
  end loop;
  utl_file.fclose(files);
  commit;
 end loop;  
exception
 when others then
  if utl_file.is_open(files) then
   utl_file.fclose(files);
  end if;
 raise;
end;
/
exec download_all_pic;

5、判断BLOB后导出图片

A、判断BLOB属性
declare
  photo blob;
begin
     select pic into photo from temp_pic where pic_no=100005;
     if photo is null then
        dbms_output.put_line('NULL');
     elsif dbms_lob.getlength(photo)=0 then
        dbms_output.put_line('empty');
     else
        dbms_output.put_line('非空');
     end if;
end;

B、判断BLOB后导出图片
create or replace procedure download_a_pic
is
 files utl_file.file_type;  --存放指向OS文件的指针
 type photo_type is table of blob index by binary_integer;
 photo photo_type;
 type photo_name_type is table of varchar2(20) index by binary_integer;
 photo_name photo_name_type;
 photo_buffer raw(32767);
 photo_length integer;   --文件大小 
 photo_amount binary_integer := 32767; --用于指定一次要读取的总字节/字符数 
 photo_start integer;  --用于指定要读取的其始位置
begin
 select pic_name,pic bulk collect into photo_name,photo from temp_pic;
 for i in 1..photo.count loop
  if photo(i) is null then
   photo_start := photo_start + photo_amount;
  elsif dbms_lob.getlength(photo(i))=0 then
   photo_start := photo_start + photo_amount;
  else  
   photo_length := dbms_lob.getlength(photo(i));
   files := utl_file.fopen('DIR1',photo_name(i)||'.jpg','wb');
   photo_start:= 1;
   while photo_start < photo_length loop
    dbms_lob.read(photo(i),photo_amount,photo_start,photo_buffer);
    utl_file.put_raw(files,photo_buffer,false);
    photo_start := photo_start + photo_amount;
   end loop;
   utl_file.fclose(files);
   commit;
  end if;
 end loop;  
exception
 when others then
  if utl_file.is_open(files) then
   utl_file.fclose(files);
  end if;
 raise;
end;
/
exec download_a_pic;

6、在PL/SQL中操作Oracle的BLOB字段时,出现“无效的窗口句柄”错误的解决:
(1) 启动"Print Spooler"服务。
(2) 安装打印机(随便装一个)。

7、PL/SQL DEVELOPER 动态执行表不可访问
grant select on v_$session to scott;
grant select on v_$sesstat to scott;
grant select on v_$statname to scott;