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

注:仅存储和读取服务器上的数据

客户端可以执行，但也是存取服务器上的数据。

以下操作最好在服务器上执行

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

以下存储过程用于向数据库加载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 2005   Copyright (c) 1982, 2004, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0 .3.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining options   SQL> create user eygle identified by eygle default tablespace users;   User created.   SQL> grant connect ,resource,dba to eygle;   Grant succeeded.   SQL> connect / as sysdba Connected. 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/eygle Connected. 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 a30 SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';   SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024 ------------------------------ ------------------ --------------- SYS_IL 0000050545C 00004$       LOBINDEX                     .0625 SYS_LOB 0000050545C 00004$      LOBSEGMENT                   .0625 EYGLE_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_IL 0000050545C 00004$       LOBINDEX                     .0625 SYS_LOB 0000050545C 00004$      LOBSEGMENT                       4 EYGLE_BLOB                     TABLE                        .0625   SQL> 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_IL 0000050545C 00004$       LOBINDEX                     .0625 SYS_LOB 0000050545C 00004$      LOBSEGMENT                       7 EYGLE_BLOB                     TABLE                        .0625     SQL> col fname for a20 SQL> col fdesc for a30 SQL> select fid,fname,fdesc,dbms_lob.getlength(fpic) siz from eygle_blob;          FID FNAME                FDESC                                 SIZ ---------- -------------------- ------------------------------ ----------          1 ShaoLin.jpg          少林寺-康熙手书                   1768198          2 DaoYing.jpg          倒映                              2131553     D:/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

通过以上方式，我们可以很容易的把大对象存储到数据库中。
二、使用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:/oradata/Pic';   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:/oradata/Pic 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:/oradata/Pic 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:/oradata/Pic 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   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg