最近做项目需要用到一些大对象数据,在网上淘了一阵子,发现了一些比较好的资料,记录了下来:
主要是用来存储大量数据的数据库字段,最大可以存储4G字节的非结构化数据。
主要介绍字符类型和二进制文件类型LOB数据的存储,单独介绍二进制类型LOB数据的存储。
一,Oracle中的LOB数据类型分类
1,按存储数据的类型分:
①字符类型:
CLOB:存储大量 单字节 字符数据。
NLOB:存储定宽 多字节 字符数据。
②二进制类型:
BLOB:存储较大无结构的二进制数据。
③二进制文件类型:
BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。
2,按存储方式分:
①存储在内部表空间:
CLOB,NLOB和BLOB
②指向外部操作系统文件:
BFILE
二,大对象数据的录入
1,声明LOB类型列
/*
conn scott/tiger;
Create TableSpace ts5_21
DataFile 'E:\Oracle\ts5_21.dbf'
Size 5m;
*/
Create Table tLob (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace ts5_21 --指定存储的表空间
Chunk 6k --指定数据块大小
Disable Storage In Row
);
2,插入大对象列
①先插入普通列数据
②遇到大对象列时,插入空白构造函数。
字符型:empty_clob(),empty_nclob()
二进制型:empty_blob()
二进制文件类型:BFileName函数指向外部文件。
BFileName函数:
BFileName(‘逻辑目录名’,‘文件名’);
逻辑目录名只能大写,因为数据词典是以大写方式存储。Oracle是区分大小写的。
在创建时,无需将BFileName函数逻辑目录指向物理路径,使用时才做检查二者是否关联。
例子:
Insert Into tLob Values(1,'Gene',empty_clob(),empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
③将逻辑目录和物理目录关联。(如果是二进制文件类型)
授予 CREATE ANY DIRECTORY 权限
Grant CREATE ANY DIRECTORY TO 用户名 WITH ADMIN OPTION;
关联逻辑目录和物理目录
本地
Create Directory 逻辑目录名 As ‘文件的物理目录’;
网络:
Create Directory 逻辑目录名 As ‘\\主机名(IP)\共享目录’;
例子:
Create Directory MYDIR As 'E:\Oracle';
插入例子:
insert into tlob values(1,'Gene','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
三,大对象数据的读取和操作:DBMS_LOB包 DBMS_LOB包:包含处理大对象的过程和函数 /* insert into tlob values(1,'Gene','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG')); insert into tlob values(2,'Jack','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG')); insert into tlob values(3,'Mary','大对象列CLOB',empty_blob(),bfilename('MYDIR','IMG_0210.JPG')); */ 1,读取大对象数据的过程和函数 ①:DBMS_LOB.Read():从LOB数据中读取指定长度数据到缓冲区的过程。 DBMS_LOB.Read(LOB数据,指定长度,起始位置,存储返回LOB类型值变量); 例子: Declare varC clob; vRStr varchar2(1000); ln number(4); Strt number(4); Begin select resume into varC from tlob where no = 1; ln := DBMS_LOB.GetLength(varC); Strt := 1; DBMS_LOB.Read(varC, ln, Strt, vRStr); DBMS_output.put_line('Return: '||vRStr); End; ②:DBMS_LOB.SubStr():从LOB数据中提取子字符串的函数。 DBMS_LOB.SubStr(LOB数据,指定提取长度,提取起始位置): 例子: Declare varC clob; vRStr varchar2(1000); ln number(4); Strt number(4); Begin select resume into varC from tlob where no = 1; ln := 4; Strt := 1; vRStr := DBMS_LOB.SubStr(varC, ln, Strt); DBMS_output.put_line('结果为: '||vRStr); End; ③:DBMS_LOB.InStr():从LOB数据中查找子字符串位置的函数。 DBMS_LOB.InStr(LOB数据, 子字符串); 例子: Declare varC clob; vSubStr varchar2(1000); vRStr varchar2(1000); ln number(4); Begin select resume into varC from tlob where no = 1; vSubStr := '大对象'; ln := DBMS_LOB.InStr(varC,vSubStr); DBMS_output.put_line('位置为: '||ln); vRStr := DBMS_LOB.SubStr(varC, Length(vSubStr), ln); DBMS_output.put_line('位置为'||ln||'长度为'||Length(vSubStr)||'的子字符串为:'||vRStr); End; ④:DBMS_LOB.GetLength():返回指定LOB数据的长度的函数。 DBMS_LOB.GetLength(LOB数据); ⑤:DBMS_LOB.Compare():比较二个大对象是否相等。返回数值0为相等,-1为不相等。 DBMS_LOB.Compare(LOB数据,LOB数据); 例子: Declare varC1 clob; varC2 clob; varC3 clob; ln number(4); Begin select resume into varC1 from tlob where no = 1; select resume into varC2 from tlob where no = 2; select resume into varC3 from tlob where no = 3; ln := DBMS_LOB.Compare(varC1,varC1); DBMS_output.put_line('比较的结果为: '||ln); ln := DBMS_LOB.Compare(varC2,varC3); DBMS_output.put_line('比较的结果为: '||ln); End; 2,操作大对象数据的过程 操作会改变数据库中原有数据,需要加上Updata锁锁上指定数据列,修改完后提交事务。 ①:DBMS_LOB.Write():将指定数量的数据写入LOB的过程。 DBMS_LOB.Write(被写入LOB, 写入长度(指写入LOB数据),写入起始位置(指被写入LOB),写入LOB数据); 例子: Declare varC clob; vWStr varchar2(1000); vStrt number(4); ln number(4); Begin vWStr := 'CLOB'; ln := Length(vWStr); vStrt := 5; select resume into varC from tlob where no = 1 FOR UPDATE; DBMS_LOB.Write(varC, ln, vStrt, vWStr); DBMS_output.put_line('改写结果为: '||varC); Commit; End; ②:DBMS_LOB.Append():将指定的LOB数据追加到指定的LOB数据后的过程。 DBMS_LOB.Append(LOB数据,LOB数据); 例子: Declare varC clob; vAStr varchar2(1000); Begin vAStr := ',这是大对象列'; select resume into varC from tlob where no = 1 FOR UPDATE; DBMS_LOB.Append(varC, vAStr); commit; DBMS_output.put_line('追加结果为: '||varC); End; ③:DBMS_LOB.Erase():删除LOB数据中指定位置的部分数据的过程; DBMS_LOB.Erase(LOB数据,指定删除长度, 开始删除位置); 例子: Declare varC clob; ln number(4); strt number(4); Begin ln := 1; strt := 5; select resume into varC from tlob where no = 1 FOR UPDATE; DBMS_LOB.Erase(varC, ln, strt); commit; DBMS_output.put_line('擦除结果为: '||varC); End; ④:DBMS_LOB.Trim():截断LOB数据中从第一位置开始指定长度的部分数据的过程; DBMS_LOB.Trim(LOB数据,截断长度); 例子: Declare varC clob; ln number(4); Begin ln := 4; select resume into varC from tlob where no = 1 FOR UPDATE; DBMS_LOB.Trim(varC, ln); COMMIT; DBMS_output.put_line('截断结果为: '||varC); End; ⑤:DBMS_LOB.Copy():从指定位置开始将源LOB复制到目标LOB; DBMS_LOB.Copy(目标LOB,源LOB,复制源LOB长度,复制到目标LOB开始位置,复制源LOB开始位置) 例子: Declare vDEST_LOB clob; vSRC_LOB clob; AMOUNT number; DEST_OFFSET number; SRC_OFFSET number; Begin select resume into vDEST_LOB from tlob where no = 1 FOR UPDATE; select resume into vSRC_LOB from tlob where no = 2 ; AMOUNT := DBMS_LOB.GetLength(vSRC_LOB); DEST_OFFSET := DBMS_LOB.GetLength(vDEST_LOB)+1; SRC_OFFSET := 1; DBMS_LOB.Copy(vDEST_LOB, vSRC_LOB, AMOUNT, DEST_OFFSET, SRC_OFFSET); DBMS_output.put_line('拷贝结果为: '||vDEST_LOB); End;