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

DB宝 2019-08-28

4916

♣题目部分在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程序员面试笔试宝典》，作者：李华荣。