【DB笔试面试626】在Oracle中，如何查看和下载BLOB类型的数据？

♣

题目部分

在Oracle中，如何查看和下载BLOB类型的数据？

     

♣

答案部分

BLOB类型的数据存储的是二进制文件，例如pdf、jpg或mp4视频格式文件等。对于BLOB类型的数据，可以使用图形化界面软件（例如PLSQL Developer或Oracle SQL Developer）来下载这些二进制数据，也可以使用PL/SQL程序来对这些数据进行读写。

例如PLSQL Developer：

Oracle SQL Developer：

另外，可以使用以下代码插入BLOB类型的文件到Oracle数据库中：

 1drop table IMAGE_LOB;
 2CREATE TABLE IMAGE_LOB (
 3   T_ID VARCHAR2 (5) NOT NULL,
 4   T_IMAGE BLOB NOT NULL
 5   );
 6
 7--插入blob文件  
 8CREATE OR REPLACE DIRECTORY IMAGES AS '/home/oracle/';
 9CREATE OR REPLACE NONEDITIONABLE PROCEDURE IMG_INSERT(TID      VARCHAR2,
10                                                      FILENAME VARCHAR2) AS
11    F_LOB BFILE;
12    B_LOB BLOB;
13BEGIN
14    INSERT INTO IMAGE_LOB
15        (T_ID, T_IMAGE)
16    VALUES
17        (TID, EMPTY_BLOB()) RETURN T_IMAGE INTO B_LOB;
18    F_LOB := BFILENAME('IMAGES', FILENAME);
19    DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
20    DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB));
21    DBMS_LOB.FILECLOSE(F_LOB);
22    COMMIT;
23END;
24 /
25BEGIN
26    IMG_INSERT('1','1.jpg');
27    IMG_INSERT('2','2.jpg');
28    IMG_INSERT('3','1.pdf');
29 END;
30/
31select length(t_image) from image_lob; --可以查看该字段占用的空间大小
32SELECT * FROM image_lob;`

可以使用以下代码导出数据库中的BLOB文件：

 1DECLARE
 2    l_file   utl_file.file_type;
 3    --l_lob    BLOB;
 4    l_offset INT := 1;
 5    l_amount INT := 32767;
 6    l_len    INT;
 7    l_buffer RAW(32767);
 8BEGIN
 9    FOR cur IN (SELECT t_image,t.t_id FROM image_lob t) LOOP
10
11                l_file := utl_file.fopen('IMAGES', cur.t_id||'.jpg', 'wb', 32767);
12        l_len  := dbms_lob.getlength(cur.t_image);
13                l_offset := 1;   
14
15        WHILE l_offset < l_len LOOP
16            dbms_lob.read(cur.t_image, l_amount, l_offset, l_buffer);
17            utl_file.put_raw(l_file, l_buffer, TRUE);
18            l_offset := l_offset + l_amount;
19        END LOOP; 
20
21        utl_file.fclose(l_file);
22
23    END LOOP;
24END;
25/

需要注意的是，这里导出的文件都是jpg格式的，如果存储的是pdf或其它格式的文件，那么在导出完成后只需要将文件的后缀名修改掉即可，并不会损坏文件。

Oracle中的lob字段采用独立的Lob Segment来存储，因此表的大小不能只查看DBA_SEGMENTS视图，还需要和DBA_LOBS视图结合来查看。另外，也可以通过LENGTH函数来查看LOB类型的字段占用的空间大小。Mos（How to Compute the Size of a Table containing Outline CLOBs and BLOBs(文档ID 118531.1)）给出了查询语句：

 1ACCEPT SCHEMA PROMPT 'Table Owner: '
 2ACCEPT TABNAME PROMPT 'Table Name:  '
 3SELECT
 4 (SELECT SUM(S.BYTES)                             -- The Table Segment size
 5  FROM DBA_SEGMENTS S
 6  WHERE S.OWNER = UPPER('&SCHEMA') AND
 7       (S.SEGMENT_NAME = UPPER('&TABNAME'))) +
 8 (SELECT SUM(S.BYTES)                              -- The Lob Segment Size
 9  FROM DBA_SEGMENTS S, DBA_LOBS L
10  WHERE S.OWNER = UPPER('&SCHEMA') AND
11       (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) +
12 (SELECT SUM(S.BYTES)                               -- The Lob Index size
13  FROM DBA_SEGMENTS S, DBA_INDEXES I
14  WHERE S.OWNER = UPPER('&SCHEMA') AND
15       (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA')))
16  "TOTAL TABLE SIZE"
17FROM DUAL;

 

& 说明：

有关BLOB的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2639269/

 

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。

---------------优质麦课------------

详细内容可以添加麦老师微信或QQ私聊。

About Me：小麦苗

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/

● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

● QQ：646634621  QQ群：618766405

● 提供OCP、OCM和高可用部分最实用的技能培训

● 题目解答若有不当之处，还望各位朋友批评指正，共同进步

DBA宝典

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。