服务热线:13616026886

技术文档 欢迎使用技术文档,我们为你提供从新手到专业开发者的所有资源,你也可以通过它日益精进

位置:首页 > 技术文档 > 数据库技术 > Oracle技术 > Oracle开发 > 查看文档

如何使用pl/sql读取数据库中的blob对象

【赛迪网-it技术报道】使用pl/sql从数据库中读取blob对象:

1.首先,确认现有对象

sql> col fdesc for a30
sql> select fid,fname,fdesc from eygle_blob;

    fid fname                        fdesc
------------------------ ------------------------------
   1 shaolin.jpg                少林寺-康熙手书
   2 daoying.jpg                     倒映

2.创建存储directory

sql> connect / as sysdba
connected.
sql> create or replace directory blobdir as 'd:oradatapic';

directory created.

sql>
sql> grant read,write on directory blobdir to eygle;

grant succeeded.

sql>

3.创建存储过程

sql> connect eygle/eygle
connected.
sql>
sql> create or replace procedure eygle_dump_blob (piname varchar2,poname varchar2) is
  2    l_file      utl_file.file_type;
  3    l_buffer    raw(32767);
  4    l_amount    binary_integer := 32767;
  5    l_pos       integer := 1;
  6    l_blob      blob;
  7    l_blob_len  integer;
  8  begin
  9    select fpic
 10    into      l_blob
 11    from      eygle_blob
 12    where  fname = piname;
 13
 14    l_blob_len := dbms_lob.getlength(l_blob);
 15    l_file := utl_file.fopen('blobdir',poname,'wb', 32767);
 16
 17    while l_pos < l_blob_len loop
 18      dbms_lob.read (l_blob, l_amount, l_pos, l_buffer);
 19      utl_file.put_raw(l_file, l_buffer, true);
 20      l_pos := l_pos + l_amount;
 21    end loop;
 22
 23    utl_file.fclose(l_file);
 24
 25  exception
 26    when others then
 27      if utl_file.is_open(l_file) then
 28        utl_file.fclose(l_file);
 29      end if;
 30      raise;
 31  end;
 32  /

procedure created.

4.最后取出数据

sql> host ls -l d:oradatapic
total 7618
-rwxrwxrwa   1 gqgai           none            2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa   1 gqgai           none            1768198 apr 19 10:12 shaolin.jpg

sql> exec eygle_dump_blob('shaolin.jpg','01.jpg')

pl/sql procedure successfully completed.

sql> host ls -l d:oradatapic
total 11072
-rwxrwxrwa   1 administrators  system          1768198 apr 26 07:16 01.jpg
-rwxrwxrwa   1 gqgai           none            2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa   1 gqgai           none            1768198 apr 19 10:12 shaolin.jpg

sql>
sql> exec eygle_dump_blob('daoying.jpg','02.jpg')

pl/sql procedure successfully completed.

sql> host ls -l d:oradatapic
total 15236
-rwxrwxrwa   1 administrators  system          1768198 apr 26 07:16 01.jpg
-rwxrwxrwa   1 administrators  system          2131553 apr 26 07:19 02.jpg
-rwxrwxrwa   1 gqgai           none            2131553 apr 19 10:12 daoying.jpg
-rwxrwxrwa

扫描关注微信公众号