在Oracle中存儲Image
?
??? 學習一下如何在Oracle中存儲圖片、影像等大文件。是從Tom那里搬過來的代碼，真的是深入淺出啊，能把復雜的問題弄簡單。好了廢話不多說了，直接上代碼。
注意：后面那段是用于直接輸出html代碼在網頁展現的腳本。
?
?
SQL> create table demo
? 2? ( id??????? int primary key,
? 3??? theBlob?? blob
? 4? )
? 5? /
?
Table created.
?
?
SQL> create or replace directory my_files as 'D:\TEST\Image';
?
Directory created.
?
?
SQL> declare
? 2????? l_blob??? blob;
? 3????? l_bfile?? bfile;
? 4? begin
? 5????? insert into demo values ( 1, empty_blob() )
? 6????? returning theBlob into l_blob;
? 7
? 8????? l_bfile := bfilename( 'MY_FILES', 'af73.jpg' );
? 9????? dbms_lob.fileopen( l_bfile );
10
11????? dbms_lob.loadfromfile( l_blob, l_bfile,
12???????????????????????????? dbms_lob.getlength( l_bfile ) );
13
14????? dbms_lob.fileclose( l_bfile );
15? end;
16? /
?
PL/SQL procedure successfully completed.
?
--done
?
?
?
?
--Now here is the package that can retrieve the pdf (or anything for that matter.? Just
--keep adding procedures that are named after the file type like .doc, .pdf, .xls and so
--on.? Some browsers really want the extension in the URL to be "correct")
SQL> create or replace package image_get
? 2? as
? 3????? procedure gif( p_id in demo.id%type );
? 4? end;
? 5? /
?
Package created.
?
SQL>
SQL> create or replace package body image_get
? 2? as
? 3
? 4? procedure gif( p_id in demo.id%type )
? 5? is
? 6????? l_lob?? blob;
? 7????? l_amt?? number default 30;
? 8????? l_off?? number default 1;
? 9????? l_raw?? raw(4096);
10? begin
11????? select theBlob into l_lob
12??????? from demo
13?????? where id = p_id;
14?????? -- make sure to change this for your type!
15????? owa_util.mime_header( 'image/gif' );
16
17????????? begin
18???????????? loop
19??????????????? dbms_lob.read( l_lob, l_amt, l_off, l_raw );
20
21??????????????? -- it is vital to use htp.PRN to avoid
22??????????????? -- spurious line feeds getting added to your
23??????????????? -- document
24??????????????? htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
25??????????????? l_off := l_off+l_amt;
26??????????????? l_amt := 4096;
27???????????? end loop;
28????????? exception
29???????????? when no_data_found then
30??????????????? NULL;
31????????? end;
32? end;
33
34? end;
35? /
?
Package body created.
?
?
?
??? 注：以上腳本涉及到的系統包有：DBMS_LOB | OWA_UTIL | UTL_RAW | HTP
?
??????? UTL_RAW might not be installed on your database.? It is part of replication.
??????? If you do not have it installed, simply:
??????? o cd $ORACLE_HOME/rdbms/admin
??????? o find the two files with "raw" in their name (eg: ls *raw*)
??????? o using svrmgrl connect as INTERNAL OR SYS -- only these users, no one else can successfully install UTL_RAW
??????? o run the .sql and then the .plb file
?
??? 系統包使用方法可查看《PLSQL Packages and Types Reference》
?
?
?
?