【IT168 服务器学院】--插入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行。
