BLOB及CLOB字段处理
数据类型,不管blob还是clob,在SQL中,都可以直接当作varchar2来使用的。
比如 下面的表 create table testlob(id number(2), b_l blob);
可以直接 insert into testlob values (1,'123');
当然更专业的是用下面的方式:
这个案例把一个文件写入到blob,最典型的应用。
首先创建一个目录
create or replace directory mydir as 'd:';
declare
dest_loc blob;
--指定要读入的文件,在mydir目录下的
src_loc bfile := bfilename('MYDIR', 'ace_introdue.jpg'); --目录名字一定要大写
amount integer := 4000;
begin
--插入记录,同时返回blob的locator
insert into testlob values(2,empty_blob()) returning b_l into dest_loc;
--打开 bfile
dbms_lob.open(src_loc, dbms_lob.lob_readonly);
--获得文件长度
select dbms_lob.getlength(src_loc) into amount from dual;
--打开要写入记录的blob locator
dbms_lob.open(dest_loc, dbms_lob.lob_readwrite);
--直接把文件load到blob字段
dbms_lob.loadfromfile(dest_loc, src_loc,amount);
--关闭相应的locator
dbms_lob.close(dest_loc);
dbms_lob.close(src_loc);
commit;
end;
/
dbms_lob包的用法
<=====插入图片========>
create directory img as 'c:\img';
保证在服务器端即192.168.0.250的C盘下有名为img的文件夹并在此目录下有"1.jpg"这张图片
并保证当前用户有对此目录的读取权限(如果读取文件用户不是创建目录的用户那么要用创建
目录的用户执行grant read on directory img to XXX)
create or replace procedure insert_image(img_dir varchar2,img_name varchar2)
img_blob blob;
img_bfile bfile;
begin
/*将通过empty_blob()函数将类型为blob的列初始化为空以便以后填充*/
insert into fw.my_image values(empty_blob())
return fw.my_image.img into img_blob;
img_bfile := bfilename(img_dir,img_name); --获得定位器指向的目录和文件
if (dbms_lob.fileexists(img_bfile)!=0) then --如果文件定位器指向的文件存在
dbms_lob.fileopen(img_bfile,dbms_lob.file_readonly); --打开目标文件
/*将文件字节流数据加载到指定的LOB类型变量中*/
dbms_lob.loadfromfile(img_blob,img_bfile,dbms_lob.getlength(img_bfile));
dbms_lob.fileclose(img_bfile);--关闭文件
commit;
dbms_output.put_line('已经从'||img_dir||'目录中读取了图片'||img_name||'向表中插入');
else --如果文件定位器指向的文件不存在
dbms_output.put_line('文件没找到');
end if;
exception when others then
dbms_output.put_line(sqlerrm);
end;
调用过程
declare
begin
fw.insert_image('IMG','1.jpg');
<====将查询结果转化为XML格式并打印======>
create or replace procedure query_to_xml
is
clob_var clob;
xml_content varchar2(32767);
line_content varchar2(4000);
line_id int; --行号
begin
line_id := 0;
clob_var := dbms_xmlquery.getXML('select * from fw.math'); --将查询SQL返回的结果集合转化为XML格式并交给一
个CLOB变量保存
xml_content := dbms_lob.substr(clob_var,32767); --截取此CLOB变量保存的全部数据并交给一个PL/SQL里的varchar2
类型变量保存(最大长度为32767)
dbms_output.put_line('以下是math表的记录转化而成的XML文档内容');
while xml_content is not null --循环
loop
line_id := line_id+1; --行号++
line_content := substr(xml_content,1,instr(xml_content,'>')); --提取第1行
dbms_output.put_line(line_id||':'||line_content); --打印行号和行内容
xml_content := substr(xml_content,instr(xml_content,chr(10))+1,length(xml_content)); --把已经读取的行从
整个XML数据里剔除掉,那么剩下的XML数据的第1行就是整个XML数据的第2行,依此类推,进行遍历(chr(10)返回一个换行
符)
end loop;
/*异常处理*/
exception when others then
dbms_output.put_line(sqlerrm);
end;
length(xml_content)这个参数也可以不写 原因如下:
create or replace directory utllobdir as 'd:\images';
declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','1.bmp');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
在sqlplus中操做blob和clob方法,下面的实现代码。
create directory "utllobdir" as 'd:'
create table blobtest(col1 BLOB);
create table clobtest(col1 CLOB);
--insert BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'RMAN_Piner.pdf\');
begin
insert into blobtest values (empty_blob())
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--update BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'log.txt\');
begin
update blobtest set col1=empty_blob() where rownum=1
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--insert CLOB
create
table clobtest(col1 CLOB);
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'teslob.doc\');
begin
insert into clobtest values (empty_clob())
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
--update CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\'
,\'log.txt\');
begin
update clobtest set col1=empty_clob() where rownum=1
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;
通过查看lob字段的长度判断能否插入成功
select dbms_lob.getlength(col1) from blobtest;
select dbms_lob.getlength(col1) from clobtest;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-689242/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-689242/