Oracle的LOB字段学习

核心提示：--插入bfile create or replace procedure insert_book(filename varchar2) as book_file bfile := NULL;bookExists boolean := false;beginbook_file := bfilename('BOO

--插入bfile create or replace procedure insert_book(filename varchar2) as
book_file bfile := NULL;
bookExists boolean := false;
begin
book_file := bfilename('BOOK_TEXT', filename);
bookExists := dbms_lob.fileexists(book_file) = 1;

if bookExists then
insert into my_book_files values ((select count(*) from my_book_files) + 1 , book_file);
dbms_output.put_line('Insert sucess! file : ' || filename);
else
dbms_output.put_line('Not exists! file : ' || filename);
end if;
exception
when dbms_lob.noexist_directory then
dbms_output.put_line('Error: ' || sqlerrm);
when dbms_lob.invalid_directory then
dbms_output.put_line('Error : ' || sqlerrm);
when others then
dbms_output.put_line('Unkown Error : ' || sqlerrm);
end insert_book;
/

create or replace procedure insertPDF(fileName varchar2) is
fileLoc bfile;
nID number;
nPDFSize integer;
bFileExists boolean := false;
begin
fileLoc := bfilename('PDFDIR',filename);
bFileExists := dbms_lob.fileexists(fileLoc) = 1;
if bFileExists = false then
dbms_output.put_line(fileName || ' not exists');
return;
end if;

nPDFSize := dbms_lob.getlength(fileLoc);
dbms_output.put_line('the length of ' || fileName || ' is ' || nPDFSize);
select count(*) + 1 into nID from PDFTable;
insert into PDFTable(ID,Pdffile)
values (nID, fileLoc);
exception
when dbms_lob.noexist_directory then
dbms_output.put_line('Error: ' || sqlerrm);
when dbms_lob.invalid_directory then
dbms_output.put_line('Error : ' || sqlerrm);
when others then
dbms_output.put_line('Unkown Error : ' || sqlerrm);
end;
/

--插入 blob

CREATE OR REPLACE procedure insertImg(imgName varchar2) is
v_file_loc bfile;
v_image blob;
nID number;
nImgSize integer;
bFileExists boolean := false;
begin
v_file_loc := bfilename('IMAGEDIR', imgName);
bFileExists := dbms_lob.fileExists(v_file_loc) = 1;
if bFileExists = false then
dbms_output.put_line(imgName || ' not exists');
return;
end if;

nImgSize := dbms_lob.getlength(v_file_loc);
dbms_output.put_line(imgName ||' size is ' || nImgSize);
dbms_output.put_line('Now Inserting empty image row');

select count(*) + 1 into nID from imagetable;
insert into imagetable(ID, image)
values (nID, empty_blob)
returning image into v_image;

DBMS_LOB.FILEOPEN (v_file_loc);
dbms_output.put_line('Open file');
dbms_lob.loadfromfile(v_image, v_file_loc, nImgSize);
DBMS_LOB.FILECLOSE(v_file_loc);
commit;
exception
when others then
dbms_output.put_line('Error happen! ' || sqlerrm);
DBMS_LOB.FILECLOSE(v_file_loc);
end insertImg;
/

--=================================================

SQL> create table view_sites_info (
2 site_id number(3),
3 audio blob default empty_blob(),
4 document clob default empty_clob(),
5 video_file bfile default null
6 );

表已创建。

SQL> commit;

提交完成。

SQL> @e:\writelob

PL/SQL 过程已成功完成。

SQL> desc view_sites_info;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SITE_ID NUMBER(3)
AUDIO BLOB
DOCUMENT CLOB
VIDEO_FILE BINARY FILE LOB

SQL> select document from view_sites_info where site_id = 100;

DOCUMENT
--------------------------------------------------------------------------------
This is a writing example
SQL> desc view_sites_info
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SITE_ID NUMBER(3)
AUDIO BLOB
DOCUMENT CLOB
VIDEO_FILE BINARY FILE LOB

SQL> insert into blobtest values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'));
insert into blobtest values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'))
*
ERROR 位于第 1 行:
ORA-00932: 数据类型不一致

SQL> desc BFILETEST
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(3)
FNAME BINARY FILE LOB

SQL> insert into BFILETEST values (1, bfilename('tempdir', 'C:\Documents and Settings\Administrator\My Documents\My Pictures\tu1.jpg'));

已创建 1 行。

SQL> get E:\insertimg
1 create or replace procedure img_insert (
2 tid varchar2,
3 filename varchar2) as
4 F_LOB BFILE;
5 B_LOB BLOB;
6 begin
7 insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
8 F_LOB := bfilename('images', filename);
9 dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
10 dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
11 dbms_lob.fileclose(F_LOB);
12 commit;
13* end;
SQL> r
1 create or replace procedure img_insert (
2 tid varchar2,
3 filename varchar2) as
4 F_LOB BFILE;
5 B_LOB BLOB;
6 begin
7 insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
8 F_LOB := bfilename('images', filename);
9 dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
10 dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
11 dbms_lob.fileclose(F_LOB);
12 commit;
13* end;