Oracle：图片数据的存放与读取（大对象）
本文简单呈现关于Oracle中大对象数据的创建与基本维护。
作为样例，本文创建的大对象中将会存放一张图片数据。
首先，通过SQL*Plus连入数据库实例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@rac1 software]$ ps -ef | grep pmon oracle 7414 1 0 20:15 ? 00:00:00 asm_pmon_+ASM1 oracle 7592 1 0 20:15 ? 00:00:00 ora_pmon_orcl1 oracle 29037 10486 0 21:08 pts/1 00:00:00 grep pmon [oracle@rac1 software]$ export ORACLE_SID=orcl1 [oracle@rac1 software]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sun Sep 28 21:08:57 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl1 OPEN SQL> |
本样例中，大对象创建在SCOTT中。
当前，SCOTT中的对象分布情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SQL> show user USER is "SYS" SQL> conn scott Enter password: Connected. SQL> show user USER is "SCOTT" SQL> SQL> select table_name from user_tables; TABLE_NAME ------------------------------ DEPT EMP BONUS SALGRADE SQL> SQL> select object_name from user_objects; OBJECT_NAME -------------------------------------------------------------------------------- PK_DEPT DEPT EMP PK_EMP BONUS SALGRADE 6 rows selected. SQL> |
创建数据表：pic_data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> create table pic_data 2 ( 3 p_id number, 4 p_name varchar2(30), 5 p_data blob, 6 p_loc bfile 7 ); Table created. SQL> desc pic_data Name Null? Type ----------------------------------------- -------- ---------------------------- P_ID NUMBER P_NAME VARCHAR2(30) P_DATA BLOB P_LOC BINARY FILE LOB SQL> |
创建目录对象：pic_location，并分配适当的权限
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[oracle@rac1 software]$ cd [oracle@rac1 ~]$ pwd /home/oracle [oracle@rac1 ~]$ ll total 0 [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Sun Sep 28 21:17:54 2014 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> create directory pic_location as '/home/oracle'; Directory created. SQL> SQL> grant read,write on directory pic_location to scott; Grant succeeded. SQL> |
插入数据前，先查看一下准备插入的数据。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[oracle@rac1 ~]$ pwd /home/oracle [oracle@rac1 ~]$ ll total 132 -rw-r--r-- 1 root root 64209 Sep 28 22:59 13.jpg -rw-r--r-- 1 oracle oinstall 1323 Sep 28 21:58 import_pictural-data.sql -rw-r--r-- 1 root root 53330 Sep 28 21:26 lwt.jpg [oracle@rac1 ~]$ du -sh * 68K 13.jpg 4.0K import_pictural-data.sql 60K lwt.jpg [oracle@rac1 ~]$ [oracle@rac1 ~]$ export DISPLAY=192.168.56.1:0.0 [oracle@rac1 ~]$ xhost + access control disabled, clients can connect from any host [oracle@rac1 ~]$ [oracle@rac1 ~]$ gthumb 13.jpg & [1] 10816 [oracle@rac1 ~]$ |
向：pic_data，插入一条数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> insert into pic_data values (0,'Example',empty_blob(),bfilename('pic_location','lwt.jpg')); 1 row created. SQL> SQL> select count(*) from pic_data; COUNT(*) ---------- 1 SQL> |
（需要使用empty_blog()函数将blob大对象初始化）
将pic_location目录对象中的图像文件导入到：pic_data。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> declare 2 l_bfile bfile; 3 l_blob blob; 4 begin 5 insert into scott.pic_data values (1,'Wen Ting',empty_blob(),bfilename('pic_location','lwt.jpg')) 6 return p_data into l_blob; 7 l_bfile := bfilename('PIC_LOCATION','lwt.jpg'); 8 dbms_lob.open(l_bfile,dbms_lob.file_readonly); 9 dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile)); 10 dbms_lob.close(l_bfile); 11 commit; 12 end; 13 / PL/SQL procedure successfully completed. SQL> |
再插入一条数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
SQL> select count(*) from scott.pic_data; COUNT(*) ---------- 2 SQL> select p_id,p_name from scott.pic_data; P_ID P_NAME ---------- ------------------------------ 1 Wen Ting 0 Example SQL> SQL> declare 2 l_bfile bfile; 3 l_blob blob; 4 begin 5 insert into scott.pic_data values (2,'Guilty Crown',empty_blob(),bfilename('pic_location','13.jpg')) 6 return p_data into l_blob; 7 l_bfile := bfilename('PIC_LOCATION','13.jpg'); 8 dbms_lob.open(l_bfile,dbms_lob.file_readonly); 9 dbms_output.put_line('--------------------------------------'); 10 dbms_output.put_line('File Status: Opened'); 11 dbms_output.put_line('--------------------------------------'); 12 dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile)); 13 dbms_output.put_line('--------------------------------------'); 14 dbms_output.put_line('File Status: Loaded'); 15 dbms_output.put_line('--------------------------------------'); 16 dbms_lob.close(l_bfile); 17 dbms_output.put_line('--------------------------------------'); 18 dbms_output.put_line('File Status: Closed'); 19 dbms_output.put_line('--------------------------------------'); 20 commit; 21 end; 22 / PL/SQL procedure successfully completed. SQL> select count(*) from scott.pic_data; COUNT(*) ---------- 3 SQL> select p_id,p_name from scott.pic_data; P_ID P_NAME ---------- ------------------------------ 1 Wen Ting 2 Guilty Crown 0 Example SQL> |
这里，BLOB的数据是无法通过SQL*Plus查看的：
|
1 2 3 |
SQL> select * from scott.pic_data; SP2-0678: Column or attribute type can not be displayed by SQL*Plus SQL> |
可以通过Oracle的SQL Developer工具查看：
————————————————————
Ending。
