Oracle进阶 LOB字段学习
--插入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;
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行。
