使用存储过程(PL/SQL)向数据库中存储BLOB对象

使用存储过程(PL/SQL)向数据库中存储BLOB对象

参考：http://www.eygle.com/archives/2005/04/eoaiplsqlieoeao.html

create or replace directory BLOBDIR as 'D:\opt';
 select * from dba_directories;
 grant read on directory BLOBDIR to tstuser;
 
 create table TST_BLOB
(
  FID   NUMBER,
  FNAME VARCHAR2(50),
  FDESC VARCHAR2(200),
  FPIC  BLOB
)
  
   select * from tst_blob;
   create sequence S_tstuser_SEQ
   start with 1
  increment by 1

CREATE OR REPLACE PROCEDURE tst_load_blob (pfname VARCHAR2,pdesc varchar2)
  IS
  src_file BFILE;
  dst_file BLOB;
  lgh_file BINARY_INTEGER;
  BEGIN
     src_file := bfilename('BLOBDIR', pfname);

     INSERT INTO tst_blob (fid,fname,fdesc,fpic)
     VALUES (S_tstuser_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
     RETURNING fpic INTO dst_file;

     SELECT fpic INTO dst_file
     FROM tst_blob  WHERE fname = pfname FOR UPDATE;

     dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
     lgh_file := dbms_lob.getlength(src_file);
     dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

    UPDATE tst_blob  SET fpic = dst_file
    WHERE fname = pfname;

    dbms_lob.fileclose(src_file);
    commit;
  END tst_load_blob;

 
  select segment_name, segment_type, bytes / 1024
    from dba_segments
   where segment_name = upper('tst_blob');
  
   call tst_load_blob('Sunset.jpg','太阳');
   call tst_load_blob('Water lilies.jpg','Water');

   call tst_load_blob('11.doc','11.doc');
   call tst_load_blob('AIX.doc','AIX.doc');
  
   select * from tst_blob;