【DB笔试面试626】在Oracle中，如何查看和下载BLOB类型的数据？
- 2019 年 10 月 10 日
- 筆記
题目部分
在Oracle中，如何查看和下载BLOB类型的数据？
♣
答案部分
BLOB类型的数据存储的是二进制文件，例如pdf、jpg或mp4视频格式文件等。对于BLOB类型的数据，可以使用图形化界面软件（例如PLSQL Developer或Oracle SQL Developer）来下载这些二进制数据，也可以使用PL/SQL程序来对这些数据进行读写。
例如PLSQL Developer：
Oracle SQL Developer：
另外，可以使用以下代码插入BLOB类型的文件到Oracle数据库中：
drop table IMAGE_LOB; CREATE TABLE IMAGE_LOB ( T_ID VARCHAR2 (5) NOT NULL, T_IMAGE BLOB NOT NULL ); --插入blob文件 CREATE OR REPLACE DIRECTORY IMAGES AS '/home/oracle/'; CREATE OR REPLACE NONEDITIONABLE PROCEDURE IMG_INSERT(TID VARCHAR2, FILENAME VARCHAR2) AS F_LOB BFILE; B_LOB BLOB; BEGIN INSERT INTO IMAGE_LOB (T_ID, T_IMAGE) VALUES (TID, EMPTY_BLOB()) RETURN T_IMAGE INTO B_LOB; F_LOB := BFILENAME('IMAGES', FILENAME); DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY); DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB, DBMS_LOB.GETLENGTH(F_LOB)); DBMS_LOB.FILECLOSE(F_LOB); COMMIT; END; / BEGIN IMG_INSERT('1','1.jpg'); IMG_INSERT('2','2.jpg'); IMG_INSERT('3','1.pdf'); END; / select length(t_image) from image_lob; --可以查看该字段占用的空间大小 SELECT * FROM image_lob;`
可以使用以下代码导出数据库中的BLOB文件：
DECLARE l_file utl_file.file_type; --l_lob BLOB; l_offset INT := 1; l_amount INT := 32767; l_len INT; l_buffer RAW(32767); BEGIN FOR cur IN (SELECT t_image,t.t_id FROM image_lob t) LOOP l_file := utl_file.fopen('IMAGES', cur.t_id||'.jpg', 'wb', 32767); l_len := dbms_lob.getlength(cur.t_image); l_offset := 1; WHILE l_offset < l_len LOOP dbms_lob.read(cur.t_image, l_amount, l_offset, l_buffer); utl_file.put_raw(l_file, l_buffer, TRUE); l_offset := l_offset + l_amount; END LOOP; utl_file.fclose(l_file); END LOOP; END; /
需要注意的是，这里导出的文件都是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)）给出了查询语句：
ACCEPT SCHEMA PROMPT 'Table Owner: ' ACCEPT TABNAME PROMPT 'Table Name: ' SELECT (SELECT SUM(S.BYTES) -- The Table Segment size FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&TABNAME'))) + (SELECT SUM(S.BYTES) -- The Lob Segment Size FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) + (SELECT SUM(S.BYTES) -- The Lob Index size FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA'))) "TOTAL TABLE SIZE" FROM DUAL;
& 说明：
有关BLOB的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2639269/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
