LOB字段学习

LOB字段学习[@more@]

--插入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 SettingsAdministratorMy DocumentsMy Picturestu1.jpg'));
insert into blobtest values (1, bfilename('tempdir', 'C:Documents and SettingsAdministratorMy DocumentsMy Picturestu1.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 SettingsAdministratorMy DocumentsMy Picturestu1.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;

SQL> create table IMAGE_LOB (T_ID varchar2(5) not null, T_IMAGE blob not null );

表已创建。

SQL> commit;

提交完成。

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;

过程已创建。

SQL> commit;

提交完成。

SQL> commit;

提交完成。

SQL> $cls

SQL> @e:insertimg

过程已创建。

SQL> commit;

提交完成。

SQL> exec img_insert('1', 'e:tu1.jpg');

PL/SQL 过程已成功完成。

SQL> @e:insertimg

过程已创建。

SQL> exec img_insert('2', 'e:tu2.jpg');

PL/SQL 过程已成功完成。

SQL> select count(*) from image_lob;

COUNT(*)
----------
3

SQL> @e:insertimg

过程已创建。
PL/SQL 过程已成功完成。

SQL> @e:insertimg

过程已创建。

PL/SQL 过程已成功完成。

SQL> @e:insertimg

过程已创建。

PL/SQL 过程已成功完成。

SQL> @e:insertimg

过程已创建。

PL/SQL 过程已成功完成。

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 dbms_output.put_line('Now begin');
8 insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
9 F_LOB := bfilename('IMAGES', filename);
10 dbms_output.put_line('Open success');
11 dbms_output.put_line('Now open :' || filename);
12 dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
13 dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
14 dbms_lob.fileclose(F_LOB);
15 commit;
16 EXCEPTION
17 when others
18 then
19 DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm );
20* end;
21 /

过程已创建。

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 dbms_output.put_line('Now begin');
8 insert into image_lob (t_id, t_image) values (tid, empty_blob()) return t_image into B_LOB;
9 F_LOB := bfilename('IMAGES', filename);
10 dbms_output.put_line('Open success');
11 dbms_output.put_line('Now open :' || filename);
12 dbms_lob.fileopen(F_LOB, dbms_lob.file_readonly);
13 dbms_lob.loadfromfile(B_LOB, F_LOB, dbms_lob.getlength(F_LOB));
14 dbms_lob.fileclose(F_LOB);
15 commit;
16 EXCEPTION
17 when others
18 then
19 DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm );
20* end;

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
ANYDATATAB
BFILETEST
BLOBTEST
BONUS
DEPT
EMP
IMAGE_LOB
LINEITEM_CV
LINEITEM_DP
SALGRADE
TAB2

TABLE_NAME
------------------------------
TEST
TEST2
VIEW_SITES_INFO

已选择14行。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28429/viewspace-842663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28429/viewspace-842663/