Oracle LOB数据类型专题
一、概述
LOB数据类型专门用于存储大对象的数据,不超过4G字节,包括大文本、图像图像、视频剪切等。
Oracle8之前使用LONG、LOBG RAW来实现,现慢慢淘汰,8开始使用LOB实现。
二、LOB的分类
分为内部LOB和外部LOB。内部LOB包括CLOB、BLOB、NCOLB;外部LOB包括BFILE
内部LOB的数据存储在数据库中,并且支持事务操作(提交、回退、保持点);外部LOB的数据存储在OS文件中,并且不支持事务操作
CLOB(存放大字符数据)用于存储大批量字符数据,
BLOB(存放大二进制数据)用于存放音频、图像、图像,
NCLOB(存放大字符数据)用于存储大批量字符数据,
BFILE(存储指向OS文件的指针)用于存储电影文件。
三、LOB和LONG的比较
两者都可以大批量字符数据和二进制数据。
1、LONG\LONG ROW
1)、一个表只能有一个LONG或LONG RAW列
2)、数据最大长度2G字节
3)、select返回数据
4)、数据存储在行内
5)、不支持对象类型
6)、数据顺序访问
2、LOB
1)、一个表可以有多个LOB列
2)、数据最大长度4G字节
3)、select返回LOB定位符
4)、数据存储在行内或行外
5)、支持对象类型
6)、数据随机访问
3、注意
LONG\LONG ROW列的数据域其它列的数据相邻存放;
而用LOB列时,如果数据小于4000字节,则与其它列相邻存放(行内),大于则数据存放到专门的LOB段中(行内)。
4、临时LOB
说明:临时LOB相当于局部变量,与数据库表无关,并且只能由当前应用程序建立和使用。
当SQL语句中使用临时LOB时,它们只能作为输入宿主变量使用,可在where子句,values子句和set子句中使用临时LOB,而不能在into子句中使用。
四、DBMS_LOB包
DBMS_LOB是Oracle提供的、专门用于处理LOB类型数据的PL/SQL包,该包定义了一些变量、过程、函数,下面逐一讲述。
1.常量
说明:包中定义了6个常量,可以在PL/SQL中直接引用,方法为包名.常量名
file_readonly constant binary_integer:=0;
lob_readonly contant binary_integer:=0;
lob_readwrite constant binaty_integer:=1;
lobmaxsize constant integer:=4294967295;
call constant pls_integer:=12;
session constant pls_integer:=10;
2、过程APPEND
说明:用于将源LOB变量的内容添加到目标LOB变量的尾部。只适用于内部LOB类型的blob和clob。
语法:
DBMS_LOB.APPEND(dest_lob in out nocopy blob,src_lob in blob);
DBMS_LOB.APPEND(dest_lob in out nocopy blob character set any_cs,src_lob in clob character set dest_lob%charset);
其中,dest_lob指定目标LOB变量,src_lob指定源LOB变量。
例子:
declare
dest_lob clob;
sec_lob clob;
begin
src_lob:='中国';
desc_lob:='你好';
dbms_lob.append(dest_lob,src_lob);
dbms_output.put_line(dest_lob);
end;
3、过程close
说明:用于关闭已经打开的LOB,全适用。
语法:DBMS_LOB.CLOSE(lob_loc in out nocopy blob/clob/bfile);
4、函数compare
说明:用于比较2个LOB的全部内容或者部分内容,但只能用于比较同类型的LOB变量。全适用。
语法:
DBMS_LOB.COMPARE(
lob_1 in blob/clob/bfile,
lob_2 in blob/clob/bfile,
amount in integer:=4294967295,
offset_1 in integer:=1,
offset_1 in integer:=1);
其中,lob_1指定第一个lob变量;lob_2指定第二个lob变量;amount指定字符个数(clob)或字节数(blob);
offser_1指定第一个lob的初始位置;offser_2指定第二个lob的初始位置;比较结果返回0则结果相同;反之则不相同。
例子:
declare
dest_lob clob;
src_lob clob;
begin
sec_lob:='中国';
dest_lob:='中国';
if dbms_lob.compare(sec_lob,dest_lob)=0 then
dbms_output.put_line('内容相同');
else
dbms_output.put_line('内容不同');
end if;
end;
5、过程copy
说明:将源lob变量的部分或者全部内容复制到目标lob变量中,只使用内部LOB类型。
语法:
DBMS_LOB.copy(dest_lob in out bocopy blob/clob/nclob,src_lob in blob/clob/nclob,
amount in integer,dest_offset in integer:=1,src_offset in integer:=1);
其中,将src_lob复制到dest_lob,dest_offset用于指定要复制到目标lob变量的起始位置,src_offset用于指定源lob变量中开始复制的起始位置。
例子:
declare
dest_lob clob;
src_lob clob;
amount int;
begin
sec_lob:='中国';
dest_lob:='你好,';
amount:=dbms_lob.getlength(sec_lob);
dbms_lob.copy(dest_lob,src_lob,amount,3);
dbms_output.put_line(dest_lob);
end;
6、过程createtemporary
说明:用于建立临时lob,Oracle会将临时lob建立在用户的临时表空间,只使用与内部LOB类型
语法:
DBMS_LOB.createtemporary(
lob_loc in out nocopy blob/clob/nclob,
cache in boolean,
dur in pls_integer:=10
);
其中,lob_loc指向lob定位符;cache指向是否要将lob读取到缓冲区;dur指向何时清除临时lob(10:会话结束时清除;12:调用结束时清除)
例如:
declare
src_loh clob;
begin
DBMS_LOB.createtemporary(src_lob,true);
end;
7、过程erase
说明:用于删除lob变量的全部或部分内容,只适用内部lob。
语法:
dbms_lob.erase(lob_Loc in out nocopy blob/clob/nclob,amount in out nocopy integer,offset in integer:=1);
其中,offset指定开始删除内容的起始位置,length指定个数。
例子:
declare
src_Lob clob;
offset int;
amount int;
begin
src_lob:='欢迎使用Oracle';
amount:=10;
offset:=5;
dbms_lob.erase(src_lob,amount,offset);
dbms_output.put_line(src_lob);
end;
8、过程fileclose
说明:用于关闭已经打开的bfile定位符所指向的os文件。
语法:
dbms_lob.fileclose(file_Loc in out ncopy bfile);
其中,file_loc指定bfile定位符。
9、过程filecloseall
说明:用于关闭当前会话已经打开的所有bfile文件。
语法:dbms_lob.filecloseall
10.函数fileexists
说明:用于确定bfile定位符所指向的os文件是否存在。
语法:dbms_lob.fileexists(file_Loc in bfile) return integer;
其中,存在则返回1,不存在返回0。
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileexists(file1)=0 then
dbms_output.put_line('文件不存在');
else
dbms_output.put_line('文件存在');
end if;
end;
11、过程filegetname
说明:用于取得bfile定位符所对应的目录别名和文件名。
语法:
dbms_Lob.filegetname(file_loc in bfile,dir_alias out varchar2,filename out varchar2);
其中,dir_alias用于取得bfile定位符所对应的目录别名;filename用于取得bfile定位符所对应的文件名。
例子:
declare
dir_alias varchar2(20);
filename varchar2(50);
file_Loc bfile;
begin
select filename into file_loc from bfile_table where fno=1;
dbms_lob.filegetname(file_Loc,fir_alias,filename);
dbms_output.put_line('目录'||dir_alias||'文件名'||filename);
end;
12、函数fileisopen
说明:用于确定bfile所对应的os文件是否已经打开。
语法:dbms_lob.fileisopen(file_loc in bfile) return integer;
其中文件被打开了返回1,否则返回0.
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileisopen(file1)=0 then
dbms_output.put_line('文件未打开');
else
dbms_output.put_line('文件已打开');
end if;
end;
13、过程fileopen
说明:打开bfile所对应的os文件。
语法:dbms_Lob in out nocopy bfile,open_mode in binary_integer:=file_readonly);
其中,open_mode指定文件的打开模式。注意os文件只能以只读方式打开。
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileexists(file1)=1 then
dbms_Lob.fileopen(file1);
dbms_output.put_line('文件已经打开');
end if;
dbms_lob.fileclose(file1);
end;
14、过程freetemporary
说明:用于释放在魔人临时表空间中的临时lob。
语法:dbsm_lob.freetemporary(lob_loc in out nocopy blob/clob/nclob);
其中lob_loc用于指定lob定位符。
declare
src_lob clob;
begin
dbms_lob.createtemporary(src_lob,true);
sec_lob:='中华人民共和国';
dbms_lob.freetemporary(src_lob);
end;
15、函数getchunksize
说明:当建立包含clob列或blob列的表时,通过指定chunk参数可以指定操作lob需要分配的字节数(该值是数据块尺寸的整数倍),
如果不指定该参数,其魔人值为数据块的尺寸。通过使用函数getchunksize,可以取得chunk参数所对应的值。
语法:dbms_lob.getchunksize(lob_loc in blob/clob/nclob) return integer;
例子:
declare
src_lob clob;
chunksize int;
begin
src_lob:='中华人民共和国';
chunksize:=dbms_lob.getchunksize(src_lob);
dbms_output.put_line('chunk'||chunksize);
end;
16、函数getlength
说明:用于取得lob数据的实际长度,适用于clob\blob\bfile。
语法:dbms_lob.getlength(lob_loc in blob/clob/nclob) return integer;
例子:
declare
file1 bfile;
length int;
begin
file1:=bfilename('G','readme.doc');
length:=dbms_lob.getlength(file1);
dbms_output.put_line(length);
end;
17、函数instr
说明:返回特定样式数据在lob中从某偏移位置开始第n次出现是的具体位置,适用于blob/clob/bfile。
语法:dbms_lob.instr(lob_loc in blob/clob/nclob/bfile,pattern in raw/varchar2,offset in integer:=1,nth in integer:=1) return integer;
其中pattern指定要搜索的字符串或者二进制数据,offset指定搜索的起始位置,nth指定第n次的出现次数。
例子:
declare
src_lob clob;
location int;
offset int;
occurence int;
begin
src_lob:='祖国、祖国,伟大的祖国';
offset:=2;
occurence:=2;
location:=dbms_lob.instr(src_lob,'祖国',offset,occurence);
dbms_output.put_line('从第'||offset||'字符开始,祖国第'||occurence||'次出现位置为'||location);
end;
18、函数isopen
说明:用于确定lob是否已经被打开,如果打开了则返回1,反之0,适用于blob/clob/bfile。
语法:dbms_lob.isopen(lob_loc in blob/clob/bfile) return integer;
例子:
declare
src_lob clob;
begin
src_lob:='祖国、祖国,伟大的祖国';
if dbms_lob.isopen(src_lob)=0 then
dbms_Lob.open(src_lob,1);
end if;
dbms_lob.colse(src_lob);
end;
19、函数istemporary
说明:用于确定lob定位符是否为临时lob,是则返回1,反之0。
语法:dbms_lob.istemporary(lob_loc in blob/clob/nclob) return integer;
例子:
declare
src_lob clob;
begin
if dbms_lob.istemporary(src_lob)=1 then
dbms_output.put_line('已经是临时lob');
else
dbms_output.put_line('临时LOB需要建立');
dbms_lob.createtemporary(src_lob,true);
end if;
dbms_lob.freetemporary(src_lob);
end;
20、过程loadfromfile
说明:用于将bfile的部分或者全部内容复制到目标lob变量(clob或者blob)中。
语法:dbms_lob.loadfromfile(
dest_lob in nocopy blob/clob,
src_file in bfile,amount in integer,
dest_offset in integer:=1,
src_offset in integer:=1);
其中,src_file指定bfile定位符,当bfile数据装载到clob中时,不会进行字符集转换,因此要确保bfile数据与数据库具有相同字符集,否则装载后的数据为乱码。
例子:
declare
src_lob bfile;
dest_lob clob;
amount int;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amount);
dbms_lob.fileclose(src_lob);
dbms_lob.freetemporary(dest_lob);
end;
21、过程loadblobfromfile
说明:将bfile数据装载到blob中,并且在装载后可以取得新的偏移位置
语法:
dbms_lob.loadblobfromfile(
dest_lob in out nocopy blob,src_bfile in bfile,
amount in integer,dest_offset in out integer,
src_offset in out integer);
其中,src_bfile指定bfile定位符,dest_offset(in)指定目标lob的起始位置,dest_offset(out)用于取得装载完成后的偏移位置,
src_offset(in)指定bfile定位符的起始位置,src_offset(out)用于取得bfile读取完成后的偏移位置。
例子:
declare
src_lob bfile;
dest_lob blob;
amount int;
src_offset int:=1;
dest_offset int:=1;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadblobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset);
dbms_lob.fileclose(src_lob);
dbms_lob.freetemporary(dest_lob);
dbms_output.put_line(dest_offset);
end;
22、过程loadclobfromfile
说明:用于将bfile数据装载到clob中,可以指定字符集id号,并进行字符集装换。
语法:
dbms_lob.loadclobfromfile(dest_lob in out nocopy clob,
src_bfile in bfile,amount in integer,
dest_offset in out integer,src_offset in out integer,
src_csid in number,lang_context in out integer,warning out integer);
其中,src_csid指定源文件的字符集id号,lang_context(in)指定语言上下文,
lang_context(out)用于取得先前装载的语言上下文,warning用于取得警告消息。
例子:
declare
src_lob bfile;
dest_lob clob;
amount int;
src_offset int:=1;
dest_offset int:=1;
csid int:=0;
lc int:=0;
warning int;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadclobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset,csid,lc,waring);
dbms_lob.fileclose(src_lob);
dbms_output.put_line(dest_lob);
dbms_lob.freetemporary(dest_lob);
end;
23、过程open
说明:该过程用于打开lob时指定lob的读写模式:只读(dbms_lob.lob_readonly)、读写(dbms_lob.lob_readwrite),适用于blob\clob\bfile。
语法:
dbms_lob.open(lob_loc in out nocopy blob/clob/bfile,open_mode in binary_integer);
其中,open_mode指定lob的读写模式。
例子:
declare
src_lob clob;
v1 varchar2(100):='中华人民共和国';
amount in;
begin
amount:=length(v1);
dbms_lob.createtemporary(src_lob,true);
dbms_lob.open(src_lob,dbms_lob,lob_readwrite);
dbms_lob.write(src_lob,amount,1,v1);
dbms_lob.close(src_lob);
dbms_output.put_line(src_lob);
dbms_lob.freetemporary(src_lob);
end;
24、过程read
说明:用于将lob数据读取到缓冲区中,适用于blob\clob\bfile。
语法:
dbms_lob.read(lob_loc in blob/clob/bfile,amount in out nocopy binary_integer,offset in integer,buffer out raw/varchar2);
其中,从lob_loc中开始偏移offset,读取amount个;
amount(in)指定要读取的字节个数(blob)或字符个数(clob),amount(out)用于取得实际读取的字节个数或字符个数。
例子:
declare
src_lob clob:='伟大的祖国';
amount int;
buffer varchar2(200);
offset int:=1;
begin
amount:=dbms_lob.getlength(src_lob);
dbms_lob.open(src_lob,dbms_lob.lob_readonly);
dbms_lob.read(src_lob,amount,offset,buffer);
dbms_output.put_line(buffer);
dbms_lob.close(src_lob);
end;
25、函数substr
说明:用于返回lob中从指定位置开始的部分内容,适用于blob\clob\bfile.
语法:dbms_lob.substr(lob_loc in blob/clob/bfile,amount in integer :=32767,offset in integer:=1);
其中始于offset,长度为amount。
例子:
declare
src_lob clob:='中国,中国,伟大的中国';
amount int;
v1 varchar2(200);
offset int;
begin
amount:=10;
offset:=4;
v1:=dbms_lob.substr(src_lob,amount,offset);
dbms_output.put_line(v1);
end;
26、过程trim
说明:用于截断lob内容到指定长度,适用于blob\clob/nclob。
语法:dbms_lob.trim(lob_loc in out nocopy blob/clob/nclob,newlen in integer);
例子:
declare
src_lob clob:='中国,中国,伟大的中国';
amount int;
begin
amount:=5;
dbms_lob.trim(src_lob,amount);
dbms_output.put_line(src_lob);
end;
27、过程write
说明:用于将缓冲区数据写入到lob中的特定位置,适用于blob和clob。
语法:dbms_lob.write(lob_loc in out nocopy blob/clob,amount in binary_integer,offset in integer,buffer in raw/varchar2);
其中,buffer指定要写入的内容,读取amount到lob_loc,开始位置为offset.
例子:
declare
src_lob clob:='我的祖国';
amount int;
offset int;
buffer varchar2(100):='伟大的中国';
begin
offset:=dbms_lob.getlength(src_lob)+1;
amount:=length(buffer);
dbms_lob.write(src_lob,amount,offset,buffer);
dbms_output.put_line(src_lob);
end;
28、过程writeappend
说明:用于将缓冲区数据写入到lob尾部,适用于blob\clob\nclob。
语法:
dbms_lob.writeappend(
lob loc in out nocopy blob/clob/nclob,
amount in binary_integer,buffer in raw);
例子:
declare
src_lob clob:='我的祖国';
amount int;
buffer varchar2(100):=',伟大的祖国';
begin
amount:=length(buffer);
dbms_lob.writeappend(src_lob,amount,buffer);
dbms_output.put_line(src_lob);
end;
五、内部LOB(CLOB/BLOB)的使用
说明:clob存放不超过4g的文本数据;blob存放不超过4g的二进制数据(例如图片)。
1、建表
create table lob_example1(id number(6) primary key,name varchar2(10),resume clob);
create table lob_example2(id number(6) primary key,name varchar2(10),photo blob);
2、插入
说明:使用函数empty_clob()初始化clob列,函数empty_blob()初始化blob列
注意:函数empty_clob()/empty_blob()与null不同,尽管该函数没有提供任何数据,但却分配了lob定位符。
insert into lob_example1 values(1,'王鸣',empty_clob());
insert into lob_example1 values(2,'马丽',empty_clob());
insert into lob_example2 values(1,'王鸣',empty_blob());
insert into lob_example2 values(2,'马丽',empty_blob());
3、更新
说明:为clob列追加数据使用dbms_lob中的write或writeappend来完成。
declare
lob_loc clob;
text varchar2(200);
amount int;
offset int;
begin
select resume into lob_loc from lob_example1 where id=1 for update;
offset:=dbms_lob.getlength(lob_loc)+1;
text:='哈工大';
amount:=length(text);
dbms_lob.write(lob_loc,amount,offset,text);
commit;
end;
4、查询
说明:这里查询针对数据不大于2000字节的情况,使用dbms_lob.read读取数据。如果大于2000字节使用循环方式读取。
例子:
declare
lob_loc clob;
lobloc blob;
buffer varchar2(200);
buffer2 raw(2000);
amount int;
offset int:=1;
begin
--clob
select resume into lob_loc from lob_example1 where id=1;
amount:=dbms_lob.getlength(lob_loc);
dbms_lob.read(lob_loc,amount,offset,buffer);--从第1个字符开始的所有数据
dbms_output.put_line(buffer);
--blob
select photo into lobloc from lob_example2 where id=1;
amount:=dbms_lob.gerlength(lobloc);
dbms_lob.read(lob_loc,amount,offset,buffer);
end;
5、将文本文件内容写入到clob列/将二进制文件内容写入到blob列
说明:将文本文件内容写入到clob中需要使用过程dbms_lob.loadfromfile或dbms_lob.loadclobfromfile,为了避免字符集问题建议使用loadclobfromfile。
将二进制文件内容写入到blob中使用dbms_lob.loadblobfromfile。
例子:
declare
lobloc clob;
lobloc2 blob;
fileloc bfile;
amount int;
src_offset int:=1;
dest_offset int:=1;
csid int:=0;
lc int:=0;
waring int;
begin
--clob
fileloc:=bfilename('G','马丽.txt');
dbms_lob.fileopen(fileloc,0);
amount:=dbms_lob.getlength(fileloc);
select sesume into lobloc from lob_example1 where id=2 for update;
dbms_lob.loadclobfromfile(lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,waring);
dbms_lob.fileclose(fileloc);
commit;
--bloc
select photo into lobloc2 from lob_example2 where id=1 for update;
fileloc:=bfilename('G','马丽.bmp');
dbms_lob.fileopen(fileloc,0);
amount:=dbms_lob.getlength(fileloc);
dbms_lob.loadblobfromfile(lobloc2,fileloc,amount,dest_offset,src_offset);
dbms_lob.fileclose(fileloc);
commit;
end;
6、将clob列内容写入到文本文件/将blob列内容写入到二进制文件
说明:不仅需要使用dbms_lob包读取clob列的内容,而且需要使用utl_file包建立文本文件并写入内容。
例子:
declare
lobloc clob;
lobloc2 blob;
amount int;
offset int:=1
buffer varchar2(2000);
buffer2 raw(1000);
handle utl_file.file_type;
begin
--clob
select resume into lobloc from lob_example1 where id=1;
amount:=dbms_lob.getlength(lobloc);
dbms_lob.read(lobloc,amount,offset,buffer);
handle:=utl_file.fopen('user_dir','a.txt','w',2000);
utl_file.put_line(bandle,buffer);
utl_file.fclose(handle);
--blob
select photo into lobloc2 from lob_example2 where id=1;
amount:=dbms_lob.getlength(lobloc2);
dbms_lob.read(lobloc2,amount,offset,buffer);
handle:=utl_file.fopen('user_dir','a.bmp','w',1000);
utl_file.put_rwa(bandle,buffer);
utl_file.fclose(handle);
end;
六、外部LOB(BFILE)的使用
说明:为了在数据库中访问OS文件的数据,需要使用bfile类型。需要注意,bfile所对应的OS文件内容只能读取,不能修改。
使用bfile类型访问OS文件是,必须首先建立directory对象,而建立此对象要求用户有create any directory权限。
例子:
conn system/manager
grant create any directory to scott;
conn scott/tiger
create directory bfile_dir as 'G:\BFILE_EXAMPLE';
当建立directory对象时,一定要确保OS目录已经存在。如果目录不存在建立时不会显示错误,当依据该directory对象访问OS文件时才会报错误。
建立了directory对象后,就可以使用bfile类型访问OS文件了。
1、建表
说明:在表中访问OS文件需要使用bfile。
create table lob_example3(id number(6) primary key,name varchar2(10),resume bfile);
2、插入
说明:使用bfilename()来初始化bfile列。当使用此函数时,directory对象必须使用大写。
insert into lob_example3 values(1,'王鸣',bfilename('BFILE_DIR','王鸣.TXT'));
insert into lob_example3 values(2,'马丽',bfilename('BFILE_DIR','马丽.TXT'));
3、查询
说明:使用dbms_lob.read读取,用raw变量接收。
declare
buffer raw(2000);
amount int;
offset int;
lobloc bfile;
begin
select resume into lobloc from lob_example3 where id=2;
dbms_lob.fileopen(lobloc,0);
amount:=dbms_lob.getlength(lobloc);
offset:=1;
dbms_lob.read(lobloc,amount,offset,buffer);
dbms_lob.fileclose(lobloc);
end;
一、概述
LOB数据类型专门用于存储大对象的数据,不超过4G字节,包括大文本、图像图像、视频剪切等。
Oracle8之前使用LONG、LOBG RAW来实现,现慢慢淘汰,8开始使用LOB实现。
二、LOB的分类
分为内部LOB和外部LOB。内部LOB包括CLOB、BLOB、NCOLB;外部LOB包括BFILE
内部LOB的数据存储在数据库中,并且支持事务操作(提交、回退、保持点);外部LOB的数据存储在OS文件中,并且不支持事务操作
CLOB(存放大字符数据)用于存储大批量字符数据,
BLOB(存放大二进制数据)用于存放音频、图像、图像,
NCLOB(存放大字符数据)用于存储大批量字符数据,
BFILE(存储指向OS文件的指针)用于存储电影文件。
三、LOB和LONG的比较
两者都可以大批量字符数据和二进制数据。
1、LONG\LONG ROW
1)、一个表只能有一个LONG或LONG RAW列
2)、数据最大长度2G字节
3)、select返回数据
4)、数据存储在行内
5)、不支持对象类型
6)、数据顺序访问
2、LOB
1)、一个表可以有多个LOB列
2)、数据最大长度4G字节
3)、select返回LOB定位符
4)、数据存储在行内或行外
5)、支持对象类型
6)、数据随机访问
3、注意
LONG\LONG ROW列的数据域其它列的数据相邻存放;
而用LOB列时,如果数据小于4000字节,则与其它列相邻存放(行内),大于则数据存放到专门的LOB段中(行内)。
4、临时LOB
说明:临时LOB相当于局部变量,与数据库表无关,并且只能由当前应用程序建立和使用。
当SQL语句中使用临时LOB时,它们只能作为输入宿主变量使用,可在where子句,values子句和set子句中使用临时LOB,而不能在into子句中使用。
四、DBMS_LOB包
DBMS_LOB是Oracle提供的、专门用于处理LOB类型数据的PL/SQL包,该包定义了一些变量、过程、函数,下面逐一讲述。
1.常量
说明:包中定义了6个常量,可以在PL/SQL中直接引用,方法为包名.常量名
file_readonly constant binary_integer:=0;
lob_readonly contant binary_integer:=0;
lob_readwrite constant binaty_integer:=1;
lobmaxsize constant integer:=4294967295;
call constant pls_integer:=12;
session constant pls_integer:=10;
2、过程APPEND
说明:用于将源LOB变量的内容添加到目标LOB变量的尾部。只适用于内部LOB类型的blob和clob。
语法:
DBMS_LOB.APPEND(dest_lob in out nocopy blob,src_lob in blob);
DBMS_LOB.APPEND(dest_lob in out nocopy blob character set any_cs,src_lob in clob character set dest_lob%charset);
其中,dest_lob指定目标LOB变量,src_lob指定源LOB变量。
例子:
declare
dest_lob clob;
sec_lob clob;
begin
src_lob:='中国';
desc_lob:='你好';
dbms_lob.append(dest_lob,src_lob);
dbms_output.put_line(dest_lob);
end;
3、过程close
说明:用于关闭已经打开的LOB,全适用。
语法:DBMS_LOB.CLOSE(lob_loc in out nocopy blob/clob/bfile);
4、函数compare
说明:用于比较2个LOB的全部内容或者部分内容,但只能用于比较同类型的LOB变量。全适用。
语法:
DBMS_LOB.COMPARE(
lob_1 in blob/clob/bfile,
lob_2 in blob/clob/bfile,
amount in integer:=4294967295,
offset_1 in integer:=1,
offset_1 in integer:=1);
其中,lob_1指定第一个lob变量;lob_2指定第二个lob变量;amount指定字符个数(clob)或字节数(blob);
offser_1指定第一个lob的初始位置;offser_2指定第二个lob的初始位置;比较结果返回0则结果相同;反之则不相同。
例子:
declare
dest_lob clob;
src_lob clob;
begin
sec_lob:='中国';
dest_lob:='中国';
if dbms_lob.compare(sec_lob,dest_lob)=0 then
dbms_output.put_line('内容相同');
else
dbms_output.put_line('内容不同');
end if;
end;
5、过程copy
说明:将源lob变量的部分或者全部内容复制到目标lob变量中,只使用内部LOB类型。
语法:
DBMS_LOB.copy(dest_lob in out bocopy blob/clob/nclob,src_lob in blob/clob/nclob,
amount in integer,dest_offset in integer:=1,src_offset in integer:=1);
其中,将src_lob复制到dest_lob,dest_offset用于指定要复制到目标lob变量的起始位置,src_offset用于指定源lob变量中开始复制的起始位置。
例子:
declare
dest_lob clob;
src_lob clob;
amount int;
begin
sec_lob:='中国';
dest_lob:='你好,';
amount:=dbms_lob.getlength(sec_lob);
dbms_lob.copy(dest_lob,src_lob,amount,3);
dbms_output.put_line(dest_lob);
end;
6、过程createtemporary
说明:用于建立临时lob,Oracle会将临时lob建立在用户的临时表空间,只使用与内部LOB类型
语法:
DBMS_LOB.createtemporary(
lob_loc in out nocopy blob/clob/nclob,
cache in boolean,
dur in pls_integer:=10
);
其中,lob_loc指向lob定位符;cache指向是否要将lob读取到缓冲区;dur指向何时清除临时lob(10:会话结束时清除;12:调用结束时清除)
例如:
declare
src_loh clob;
begin
DBMS_LOB.createtemporary(src_lob,true);
end;
7、过程erase
说明:用于删除lob变量的全部或部分内容,只适用内部lob。
语法:
dbms_lob.erase(lob_Loc in out nocopy blob/clob/nclob,amount in out nocopy integer,offset in integer:=1);
其中,offset指定开始删除内容的起始位置,length指定个数。
例子:
declare
src_Lob clob;
offset int;
amount int;
begin
src_lob:='欢迎使用Oracle';
amount:=10;
offset:=5;
dbms_lob.erase(src_lob,amount,offset);
dbms_output.put_line(src_lob);
end;
8、过程fileclose
说明:用于关闭已经打开的bfile定位符所指向的os文件。
语法:
dbms_lob.fileclose(file_Loc in out ncopy bfile);
其中,file_loc指定bfile定位符。
9、过程filecloseall
说明:用于关闭当前会话已经打开的所有bfile文件。
语法:dbms_lob.filecloseall
10.函数fileexists
说明:用于确定bfile定位符所指向的os文件是否存在。
语法:dbms_lob.fileexists(file_Loc in bfile) return integer;
其中,存在则返回1,不存在返回0。
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileexists(file1)=0 then
dbms_output.put_line('文件不存在');
else
dbms_output.put_line('文件存在');
end if;
end;
11、过程filegetname
说明:用于取得bfile定位符所对应的目录别名和文件名。
语法:
dbms_Lob.filegetname(file_loc in bfile,dir_alias out varchar2,filename out varchar2);
其中,dir_alias用于取得bfile定位符所对应的目录别名;filename用于取得bfile定位符所对应的文件名。
例子:
declare
dir_alias varchar2(20);
filename varchar2(50);
file_Loc bfile;
begin
select filename into file_loc from bfile_table where fno=1;
dbms_lob.filegetname(file_Loc,fir_alias,filename);
dbms_output.put_line('目录'||dir_alias||'文件名'||filename);
end;
12、函数fileisopen
说明:用于确定bfile所对应的os文件是否已经打开。
语法:dbms_lob.fileisopen(file_loc in bfile) return integer;
其中文件被打开了返回1,否则返回0.
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileisopen(file1)=0 then
dbms_output.put_line('文件未打开');
else
dbms_output.put_line('文件已打开');
end if;
end;
13、过程fileopen
说明:打开bfile所对应的os文件。
语法:dbms_Lob in out nocopy bfile,open_mode in binary_integer:=file_readonly);
其中,open_mode指定文件的打开模式。注意os文件只能以只读方式打开。
例子:
declare
file1 bfile;
begin
file1:=bfilename('G','readme.doc');
if dbms_lob.fileexists(file1)=1 then
dbms_Lob.fileopen(file1);
dbms_output.put_line('文件已经打开');
end if;
dbms_lob.fileclose(file1);
end;
14、过程freetemporary
说明:用于释放在魔人临时表空间中的临时lob。
语法:dbsm_lob.freetemporary(lob_loc in out nocopy blob/clob/nclob);
其中lob_loc用于指定lob定位符。
declare
src_lob clob;
begin
dbms_lob.createtemporary(src_lob,true);
sec_lob:='中华人民共和国';
dbms_lob.freetemporary(src_lob);
end;
15、函数getchunksize
说明:当建立包含clob列或blob列的表时,通过指定chunk参数可以指定操作lob需要分配的字节数(该值是数据块尺寸的整数倍),
如果不指定该参数,其魔人值为数据块的尺寸。通过使用函数getchunksize,可以取得chunk参数所对应的值。
语法:dbms_lob.getchunksize(lob_loc in blob/clob/nclob) return integer;
例子:
declare
src_lob clob;
chunksize int;
begin
src_lob:='中华人民共和国';
chunksize:=dbms_lob.getchunksize(src_lob);
dbms_output.put_line('chunk'||chunksize);
end;
16、函数getlength
说明:用于取得lob数据的实际长度,适用于clob\blob\bfile。
语法:dbms_lob.getlength(lob_loc in blob/clob/nclob) return integer;
例子:
declare
file1 bfile;
length int;
begin
file1:=bfilename('G','readme.doc');
length:=dbms_lob.getlength(file1);
dbms_output.put_line(length);
end;
17、函数instr
说明:返回特定样式数据在lob中从某偏移位置开始第n次出现是的具体位置,适用于blob/clob/bfile。
语法:dbms_lob.instr(lob_loc in blob/clob/nclob/bfile,pattern in raw/varchar2,offset in integer:=1,nth in integer:=1) return integer;
其中pattern指定要搜索的字符串或者二进制数据,offset指定搜索的起始位置,nth指定第n次的出现次数。
例子:
declare
src_lob clob;
location int;
offset int;
occurence int;
begin
src_lob:='祖国、祖国,伟大的祖国';
offset:=2;
occurence:=2;
location:=dbms_lob.instr(src_lob,'祖国',offset,occurence);
dbms_output.put_line('从第'||offset||'字符开始,祖国第'||occurence||'次出现位置为'||location);
end;
18、函数isopen
说明:用于确定lob是否已经被打开,如果打开了则返回1,反之0,适用于blob/clob/bfile。
语法:dbms_lob.isopen(lob_loc in blob/clob/bfile) return integer;
例子:
declare
src_lob clob;
begin
src_lob:='祖国、祖国,伟大的祖国';
if dbms_lob.isopen(src_lob)=0 then
dbms_Lob.open(src_lob,1);
end if;
dbms_lob.colse(src_lob);
end;
19、函数istemporary
说明:用于确定lob定位符是否为临时lob,是则返回1,反之0。
语法:dbms_lob.istemporary(lob_loc in blob/clob/nclob) return integer;
例子:
declare
src_lob clob;
begin
if dbms_lob.istemporary(src_lob)=1 then
dbms_output.put_line('已经是临时lob');
else
dbms_output.put_line('临时LOB需要建立');
dbms_lob.createtemporary(src_lob,true);
end if;
dbms_lob.freetemporary(src_lob);
end;
20、过程loadfromfile
说明:用于将bfile的部分或者全部内容复制到目标lob变量(clob或者blob)中。
语法:dbms_lob.loadfromfile(
dest_lob in nocopy blob/clob,
src_file in bfile,amount in integer,
dest_offset in integer:=1,
src_offset in integer:=1);
其中,src_file指定bfile定位符,当bfile数据装载到clob中时,不会进行字符集转换,因此要确保bfile数据与数据库具有相同字符集,否则装载后的数据为乱码。
例子:
declare
src_lob bfile;
dest_lob clob;
amount int;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadfromfile(dest_lob,src_lob,amount);
dbms_lob.fileclose(src_lob);
dbms_lob.freetemporary(dest_lob);
end;
21、过程loadblobfromfile
说明:将bfile数据装载到blob中,并且在装载后可以取得新的偏移位置
语法:
dbms_lob.loadblobfromfile(
dest_lob in out nocopy blob,src_bfile in bfile,
amount in integer,dest_offset in out integer,
src_offset in out integer);
其中,src_bfile指定bfile定位符,dest_offset(in)指定目标lob的起始位置,dest_offset(out)用于取得装载完成后的偏移位置,
src_offset(in)指定bfile定位符的起始位置,src_offset(out)用于取得bfile读取完成后的偏移位置。
例子:
declare
src_lob bfile;
dest_lob blob;
amount int;
src_offset int:=1;
dest_offset int:=1;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadblobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset);
dbms_lob.fileclose(src_lob);
dbms_lob.freetemporary(dest_lob);
dbms_output.put_line(dest_offset);
end;
22、过程loadclobfromfile
说明:用于将bfile数据装载到clob中,可以指定字符集id号,并进行字符集装换。
语法:
dbms_lob.loadclobfromfile(dest_lob in out nocopy clob,
src_bfile in bfile,amount in integer,
dest_offset in out integer,src_offset in out integer,
src_csid in number,lang_context in out integer,warning out integer);
其中,src_csid指定源文件的字符集id号,lang_context(in)指定语言上下文,
lang_context(out)用于取得先前装载的语言上下文,warning用于取得警告消息。
例子:
declare
src_lob bfile;
dest_lob clob;
amount int;
src_offset int:=1;
dest_offset int:=1;
csid int:=0;
lc int:=0;
warning int;
begin
src_lob:=bfilename('G','a.txt');
dbms_lob.createtemporary(dest_lob,true);
dbms_lob.fileopen(src_lob,0);
amount:=dbms_lob.getlength(src_lob);
dbms_lob.loadclobfromfile(dest_lob,src_lob,amount,dest_offset,src_offset,csid,lc,waring);
dbms_lob.fileclose(src_lob);
dbms_output.put_line(dest_lob);
dbms_lob.freetemporary(dest_lob);
end;
23、过程open
说明:该过程用于打开lob时指定lob的读写模式:只读(dbms_lob.lob_readonly)、读写(dbms_lob.lob_readwrite),适用于blob\clob\bfile。
语法:
dbms_lob.open(lob_loc in out nocopy blob/clob/bfile,open_mode in binary_integer);
其中,open_mode指定lob的读写模式。
例子:
declare
src_lob clob;
v1 varchar2(100):='中华人民共和国';
amount in;
begin
amount:=length(v1);
dbms_lob.createtemporary(src_lob,true);
dbms_lob.open(src_lob,dbms_lob,lob_readwrite);
dbms_lob.write(src_lob,amount,1,v1);
dbms_lob.close(src_lob);
dbms_output.put_line(src_lob);
dbms_lob.freetemporary(src_lob);
end;
24、过程read
说明:用于将lob数据读取到缓冲区中,适用于blob\clob\bfile。
语法:
dbms_lob.read(lob_loc in blob/clob/bfile,amount in out nocopy binary_integer,offset in integer,buffer out raw/varchar2);
其中,从lob_loc中开始偏移offset,读取amount个;
amount(in)指定要读取的字节个数(blob)或字符个数(clob),amount(out)用于取得实际读取的字节个数或字符个数。
例子:
declare
src_lob clob:='伟大的祖国';
amount int;
buffer varchar2(200);
offset int:=1;
begin
amount:=dbms_lob.getlength(src_lob);
dbms_lob.open(src_lob,dbms_lob.lob_readonly);
dbms_lob.read(src_lob,amount,offset,buffer);
dbms_output.put_line(buffer);
dbms_lob.close(src_lob);
end;
25、函数substr
说明:用于返回lob中从指定位置开始的部分内容,适用于blob\clob\bfile.
语法:dbms_lob.substr(lob_loc in blob/clob/bfile,amount in integer :=32767,offset in integer:=1);
其中始于offset,长度为amount。
例子:
declare
src_lob clob:='中国,中国,伟大的中国';
amount int;
v1 varchar2(200);
offset int;
begin
amount:=10;
offset:=4;
v1:=dbms_lob.substr(src_lob,amount,offset);
dbms_output.put_line(v1);
end;
26、过程trim
说明:用于截断lob内容到指定长度,适用于blob\clob/nclob。
语法:dbms_lob.trim(lob_loc in out nocopy blob/clob/nclob,newlen in integer);
例子:
declare
src_lob clob:='中国,中国,伟大的中国';
amount int;
begin
amount:=5;
dbms_lob.trim(src_lob,amount);
dbms_output.put_line(src_lob);
end;
27、过程write
说明:用于将缓冲区数据写入到lob中的特定位置,适用于blob和clob。
语法:dbms_lob.write(lob_loc in out nocopy blob/clob,amount in binary_integer,offset in integer,buffer in raw/varchar2);
其中,buffer指定要写入的内容,读取amount到lob_loc,开始位置为offset.
例子:
declare
src_lob clob:='我的祖国';
amount int;
offset int;
buffer varchar2(100):='伟大的中国';
begin
offset:=dbms_lob.getlength(src_lob)+1;
amount:=length(buffer);
dbms_lob.write(src_lob,amount,offset,buffer);
dbms_output.put_line(src_lob);
end;
28、过程writeappend
说明:用于将缓冲区数据写入到lob尾部,适用于blob\clob\nclob。
语法:
dbms_lob.writeappend(
lob loc in out nocopy blob/clob/nclob,
amount in binary_integer,buffer in raw);
例子:
declare
src_lob clob:='我的祖国';
amount int;
buffer varchar2(100):=',伟大的祖国';
begin
amount:=length(buffer);
dbms_lob.writeappend(src_lob,amount,buffer);
dbms_output.put_line(src_lob);
end;
五、内部LOB(CLOB/BLOB)的使用
说明:clob存放不超过4g的文本数据;blob存放不超过4g的二进制数据(例如图片)。
1、建表
create table lob_example1(id number(6) primary key,name varchar2(10),resume clob);
create table lob_example2(id number(6) primary key,name varchar2(10),photo blob);
2、插入
说明:使用函数empty_clob()初始化clob列,函数empty_blob()初始化blob列
注意:函数empty_clob()/empty_blob()与null不同,尽管该函数没有提供任何数据,但却分配了lob定位符。
insert into lob_example1 values(1,'王鸣',empty_clob());
insert into lob_example1 values(2,'马丽',empty_clob());
insert into lob_example2 values(1,'王鸣',empty_blob());
insert into lob_example2 values(2,'马丽',empty_blob());
3、更新
说明:为clob列追加数据使用dbms_lob中的write或writeappend来完成。
declare
lob_loc clob;
text varchar2(200);
amount int;
offset int;
begin
select resume into lob_loc from lob_example1 where id=1 for update;
offset:=dbms_lob.getlength(lob_loc)+1;
text:='哈工大';
amount:=length(text);
dbms_lob.write(lob_loc,amount,offset,text);
commit;
end;
4、查询
说明:这里查询针对数据不大于2000字节的情况,使用dbms_lob.read读取数据。如果大于2000字节使用循环方式读取。
例子:
declare
lob_loc clob;
lobloc blob;
buffer varchar2(200);
buffer2 raw(2000);
amount int;
offset int:=1;
begin
--clob
select resume into lob_loc from lob_example1 where id=1;
amount:=dbms_lob.getlength(lob_loc);
dbms_lob.read(lob_loc,amount,offset,buffer);--从第1个字符开始的所有数据
dbms_output.put_line(buffer);
--blob
select photo into lobloc from lob_example2 where id=1;
amount:=dbms_lob.gerlength(lobloc);
dbms_lob.read(lob_loc,amount,offset,buffer);
end;
5、将文本文件内容写入到clob列/将二进制文件内容写入到blob列
说明:将文本文件内容写入到clob中需要使用过程dbms_lob.loadfromfile或dbms_lob.loadclobfromfile,为了避免字符集问题建议使用loadclobfromfile。
将二进制文件内容写入到blob中使用dbms_lob.loadblobfromfile。
例子:
declare
lobloc clob;
lobloc2 blob;
fileloc bfile;
amount int;
src_offset int:=1;
dest_offset int:=1;
csid int:=0;
lc int:=0;
waring int;
begin
--clob
fileloc:=bfilename('G','马丽.txt');
dbms_lob.fileopen(fileloc,0);
amount:=dbms_lob.getlength(fileloc);
select sesume into lobloc from lob_example1 where id=2 for update;
dbms_lob.loadclobfromfile(lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,waring);
dbms_lob.fileclose(fileloc);
commit;
--bloc
select photo into lobloc2 from lob_example2 where id=1 for update;
fileloc:=bfilename('G','马丽.bmp');
dbms_lob.fileopen(fileloc,0);
amount:=dbms_lob.getlength(fileloc);
dbms_lob.loadblobfromfile(lobloc2,fileloc,amount,dest_offset,src_offset);
dbms_lob.fileclose(fileloc);
commit;
end;
6、将clob列内容写入到文本文件/将blob列内容写入到二进制文件
说明:不仅需要使用dbms_lob包读取clob列的内容,而且需要使用utl_file包建立文本文件并写入内容。
例子:
declare
lobloc clob;
lobloc2 blob;
amount int;
offset int:=1
buffer varchar2(2000);
buffer2 raw(1000);
handle utl_file.file_type;
begin
--clob
select resume into lobloc from lob_example1 where id=1;
amount:=dbms_lob.getlength(lobloc);
dbms_lob.read(lobloc,amount,offset,buffer);
handle:=utl_file.fopen('user_dir','a.txt','w',2000);
utl_file.put_line(bandle,buffer);
utl_file.fclose(handle);
--blob
select photo into lobloc2 from lob_example2 where id=1;
amount:=dbms_lob.getlength(lobloc2);
dbms_lob.read(lobloc2,amount,offset,buffer);
handle:=utl_file.fopen('user_dir','a.bmp','w',1000);
utl_file.put_rwa(bandle,buffer);
utl_file.fclose(handle);
end;
六、外部LOB(BFILE)的使用
说明:为了在数据库中访问OS文件的数据,需要使用bfile类型。需要注意,bfile所对应的OS文件内容只能读取,不能修改。
使用bfile类型访问OS文件是,必须首先建立directory对象,而建立此对象要求用户有create any directory权限。
例子:
conn system/manager
grant create any directory to scott;
conn scott/tiger
create directory bfile_dir as 'G:\BFILE_EXAMPLE';
当建立directory对象时,一定要确保OS目录已经存在。如果目录不存在建立时不会显示错误,当依据该directory对象访问OS文件时才会报错误。
建立了directory对象后,就可以使用bfile类型访问OS文件了。
1、建表
说明:在表中访问OS文件需要使用bfile。
create table lob_example3(id number(6) primary key,name varchar2(10),resume bfile);
2、插入
说明:使用bfilename()来初始化bfile列。当使用此函数时,directory对象必须使用大写。
insert into lob_example3 values(1,'王鸣',bfilename('BFILE_DIR','王鸣.TXT'));
insert into lob_example3 values(2,'马丽',bfilename('BFILE_DIR','马丽.TXT'));
3、查询
说明:使用dbms_lob.read读取,用raw变量接收。
declare
buffer raw(2000);
amount int;
offset int;
lobloc bfile;
begin
select resume into lobloc from lob_example3 where id=2;
dbms_lob.fileopen(lobloc,0);
amount:=dbms_lob.getlength(lobloc);
offset:=1;
dbms_lob.read(lobloc,amount,offset,buffer);
dbms_lob.fileclose(lobloc);
end;