存储过程存取图片

来源：互联网发布：台湾娱乐圈没落知乎编辑：程序博客网时间：2024/04/25 00:19

使用存储过程(PL/SQL)向数据库中存取BLOB对象——图片新一篇: Add a custom tool to the web mapping application注:仅存储和读取服务器上的数据
客户端可以执行，但也是存取服务器上的数据。
以下操作最好在服务器上执行
一、使用存储过程(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_IL0000050545C00004$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$      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_IL0000050545C00004$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$      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_IL0000050545C00004$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$      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