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

 

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

以下存储过程用于向数据库加载BLOB对象
1.创建directory并授权
关于Directory可以参考: Using Create directory & UTL_FILE in Oracle

C:\>sqlplus "/ as sysdba"SQL*Plus: Release 10.1.0.3.0 - Production on Tue Apr 26 07:11:51 2005Copyright (c) 1982, 2004, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining optionsSQL> create user eygle identified by eygle default tablespace users;User created.SQL> grant connect ,resource,dba to eygle;Grant succeeded.SQL> connect / as sysdbaConnected.SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';Directory created.SQL> grant read on directory BLOBDIR to eygle;Grant succeeded.SQL>

2.创建测试表

SQL> connect eygle/eygleConnected.SQL> CREATE TABLE eygle_blob (  2  fid    number,  3  fname       varchar2(50),  4  fdesc  varchar2(200),  5  fpic        BLOB)  6  /Table created.SQL>SQL> create sequence S_EYGLE_SEQ  2  start with 1  3  increment by 1  4  /Sequence created.SQL>

3.创建存储过程

SQL> CREATE OR REPLACE PROCEDURE eygle_load_blob (pfname VARCHAR2,pdesc varchar2)  2  IS  3  src_file BFILE;  4  dst_file BLOB;  5  lgh_file BINARY_INTEGER;  6  BEGIN  7     src_file := bfilename('BLOBDIR', pfname);  8  9     INSERT INTO eygle_blob (fid,fname,fdesc,fpic) 10     VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB()) 11     RETURNING fpic INTO dst_file; 12 13     SELECT fpic INTO dst_file 14     FROM eygle_blob  WHERE fname = pfname FOR UPDATE; 15 16     dbms_lob.fileopen(src_file, dbms_lob.file_readonly); 17     lgh_file := dbms_lob.getlength(src_file); 18     dbms_lob.loadfromfile(dst_file, src_file, lgh_file); 19 20    UPDATE eygle_blob  SET fpic = dst_file 21    WHERE fname = pfname; 22 23    dbms_lob.fileclose(src_file); 24    commit; 25  END eygle_load_blob; 26  /Procedure created.SQL> col segment_name for a30SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024------------------------------ ------------------ ---------------SYS_IL0000050545C00004$$       LOBINDEX                     .0625SYS_LOB0000050545C00004$$      LOBSEGMENT                   .0625EYGLE_BLOB                     TABLE                        .0625

4.加载Blob对象

SQL> exec eygle_load_blob('ShaoLin.jpg','少林寺-康熙手书');PL/SQL procedure successfully completed.SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024------------------------------ ------------------ ---------------SYS_IL0000050545C00004$$       LOBINDEX                     .0625SYS_LOB0000050545C00004$$      LOBSEGMENT                       4EYGLE_BLOB                     TABLE                        .0625SQL> exec eygle_load_blob('DaoYing.jpg','倒映');PL/SQL procedure successfully completed.SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024------------------------------ ------------------ ---------------SYS_IL0000050545C00004$$       LOBINDEX                     .0625SYS_LOB0000050545C00004$$      LOBSEGMENT                       7EYGLE_BLOB                     TABLE                        .0625SQL> col fname for a20SQL> col fdesc for a30SQL> select fid,fname,fdesc,dbms_lob.getlength(fpic) siz from eygle_blob;       FID FNAME                FDESC                                 SIZ---------- -------------------- ------------------------------ ----------         1 ShaoLin.jpg          少林寺-康熙手书                   1768198         2 DaoYing.jpg          倒映                              2131553D:\oradata\Pic>ls -l-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg

通过以上方式，我们可以很容易的把大对象存储到数据库中。