使用PL/SQL从数据库中读取BLOB对象

最新推荐文章于 2022-08-02 11:26:09 发布

原创最新推荐文章于 2022-08-02 11:26:09 发布 · 835 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL

oracle 专栏收录该内容

19 篇文章

订阅专栏

本文介绍如何使用PL/SQL从Oracle数据库中读取BLOB对象，并将其导出到文件系统的过程。首先确认数据库中已存在的BLOB对象，接着创建存储目录并授权，然后创建用于读取BLOB对象的存储过程，最后通过执行存储过程完成数据的导出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用PL/SQL从数据库中读取BLOB对象

1.确认现有对象
SQL> col fdesc for a30
SQL> select fid,fname,fdesc from bobo_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 bobo;

Grant succeeded.

SQL>

3.创建存储过程
SQL> connect bobo/bobo
Connected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE bobo_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 bobo_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 bobo_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 bobo_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