LOB大对象处理: 主要是用来存储大量数据的数据库字段,最大可以存储4G字节的非结构化数据。 主要介绍字符类型和二进制文件类型LOB数据的存储,单独介绍二进制类型LOB数据的存储。
一. Oracle中的LOB数据类型分类
1,按存储数据的类型分:
字符类型:
CLOB:存储大量 单字节 字符数据。
NLOB:存储定宽 多字节 字符数据。
二进制类型:
BLOB:存储较大无结构的二进制数据。
二进制文件类型:
BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。
2,按存储方式分:
存储在内部表空间:
CLOB,NLOB和BLOB
指向外部操作系统文件:
BFILE
二,大对象数据的录入
2.1,声明LOB类型列
Create TableSpace JOHN DataFile '/oracle/oradata/AAA/john.dbf' Size 20m;
Create Table LOBTABLE (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace JOHN --指定存储的表空间
Chunk 6k --指定数据块大小
Disable Storage In Row
);
2,插入大对象列
先插入普通列数据
遇到大对象列时,插入空白构造函数。
字符型:empty_clob(),empty_nclob()
二进制型:empty_blob()
二进制文件类型:BFileName函数指向外部文件。
BFileName函数:
BFileName(‘逻辑目录名’,‘文件名’);
逻辑目录名只能大写,因为数据词典是以大写方式存储。Oracle是区分大小写的。
在创建时,无需将BFileName函数逻辑目录指向物理路径,使用时才做检查二者是否关联。
将逻辑目录和物理目录关联。(如果是二进制文件类型)
授予 CREATE ANY DIRECTORY 权限
Grant CREATE ANY DIRECTORY TO 用户名 WITH ADMIN OPTION;
关联逻辑目录和物理目录
本地
Create Directory 逻辑目录名 As ‘文件的物理目录’;
网络:
Create Directory 逻辑目录名 As ‘//主机名(IP)/共享目录’;
例子:
Create Directory DUMP1 As '/export/home/oracle/dbdump1';
插入例子:
INSERT INTO LOBTABLE VALUES (1,'CAI','CLOB大对象列',EMPTY_BLOB (),BFILENAME ('DUMP1', 'Screenshot.png'));
三,大对象数据的读取和操作:DBMS_LOB包
DBMS_LOB包:包含处理大对象的过程和函数
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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 varC2 FROM LOBTABLE WHERE no = 2;
SELECT resume INTO varC3 FROM LOBTABLE WHERE no = 3;
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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 LOBTABLE WHERE no = 1 FOR UPDATE ;
SELECT resume INTO vSRC_LOB FROM LOBTABLE 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;
三, 图片的存储或二进制文件的存储
1,先插入普通数据,遇到大对象列使用empty_blob()构造空的指针。
例子:
Create Table BLOBFILE
(
no number(8) primary key,
fname varchar2(30),
myfile blob
)
Lob (myfile) Store As
(
Tablespace JOHN
Chunk 15K
Disable Storage In Row
);
插入指针
INSERT INTO BLOBFILE VALUES (1, 'IMG_0210.JPG', EMPTY_BLOB ());
2,逻辑目录依然用dump1目录
3,声明一个BLOB类型变量,使用select into 语句让其指向到empty_blob()构造空的指针所指向的存储空间
SELECT BLOBFILE INTO BLOB类型变量 FROM BLOBFILE WHERE no = 1 FOR UPDATE;
4,声明一个BFile类型变量,关联逻辑目录和物理目录文件,使用 BFileName() 将其指向到待存储的文件。
BFile类型变量 := BFileName('MYDIR','IMG_0210.JPG');
5,使用DBMS_LOB.open()方法将BFile类型变量所指向的文件打开
DBMS_LOB.Open(BFile类型变量);
6,使用DBMS_LOB.loadfromfile()方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));
7,使用DBMS_LOB.close()方法将bfile的变量所指向的文件关闭
DBMS_LOB.Close(BFile类型变量);
8,提交事务
Commit;
例子;
Declare
varB blob;
varF Bfile;
Begin
select myfile into varB from BLOBFILE where no = 1 for update;
varF := bfileName('DUMP1', 'Screenshot.png');
DBMS_LOB.open(varF);
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--查看文件大小
Declare
varB blob;
Begin
select myfile into varB from BLOBFILE where no = 1;
DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
End;
例子:创建过程存储图片或二进制文件
Create Or Replace Procedure setBLOB(vFileName varchar2)
As
varF bfile;
varB blob;
vno number(8);
Begin
varF := BFilename('MYDIR',vFileName);
DBMS_LOB.Open(varF);
select max(no) into vno from BLOBFILE;
if vno is null then
vno := 1;
else
vno := vno + 1;
end if;
insert into BLOBFILE values(vno,vFileName,empty_blob());
select myFile into varB from BLOBFILE where no = vno for update;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--执行过程
EXEC setBLOB('Screenshot.png');
--查看文件大小
Declare
varB blob;
Begin
select myfile into varB from BLOBFILE where no = 2;
DBMS_output.put_line('长度为: '||DBMS_LOB.getlength(varB));
一. Oracle中的LOB数据类型分类
1,按存储数据的类型分:
字符类型:
CLOB:存储大量 单字节 字符数据。
NLOB:存储定宽 多字节 字符数据。
二进制类型:
BLOB:存储较大无结构的二进制数据。
二进制文件类型:
BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。
2,按存储方式分:
存储在内部表空间:
CLOB,NLOB和BLOB
指向外部操作系统文件:
BFILE
二,大对象数据的录入
2.1,声明LOB类型列
Create TableSpace JOHN DataFile '/oracle/oradata/AAA/john.dbf' Size 20m;
Create Table LOBTABLE (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace JOHN --指定存储的表空间
Chunk 6k --指定数据块大小
Disable Storage In Row
);
2,插入大对象列
先插入普通列数据
遇到大对象列时,插入空白构造函数。
字符型:empty_clob(),empty_nclob()
二进制型:empty_blob()
二进制文件类型:BFileName函数指向外部文件。
BFileName函数:
BFileName(‘逻辑目录名’,‘文件名’);
逻辑目录名只能大写,因为数据词典是以大写方式存储。Oracle是区分大小写的。
在创建时,无需将BFileName函数逻辑目录指向物理路径,使用时才做检查二者是否关联。
将逻辑目录和物理目录关联。(如果是二进制文件类型)
授予 CREATE ANY DIRECTORY 权限
Grant CREATE ANY DIRECTORY TO 用户名 WITH ADMIN OPTION;
关联逻辑目录和物理目录
本地
Create Directory 逻辑目录名 As ‘文件的物理目录’;
网络:
Create Directory 逻辑目录名 As ‘//主机名(IP)/共享目录’;
例子:
Create Directory DUMP1 As '/export/home/oracle/dbdump1';
插入例子:
INSERT INTO LOBTABLE VALUES (1,'CAI','CLOB大对象列',EMPTY_BLOB (),BFILENAME ('DUMP1', 'Screenshot.png'));
三,大对象数据的读取和操作:DBMS_LOB包
DBMS_LOB包:包含处理大对象的过程和函数
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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 varC2 FROM LOBTABLE WHERE no = 2;
SELECT resume INTO varC3 FROM LOBTABLE WHERE no = 3;
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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 LOBTABLE 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 LOBTABLE WHERE no = 1 FOR UPDATE ;
SELECT resume INTO vSRC_LOB FROM LOBTABLE 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;
三, 图片的存储或二进制文件的存储
1,先插入普通数据,遇到大对象列使用empty_blob()构造空的指针。
例子:
Create Table BLOBFILE
(
no number(8) primary key,
fname varchar2(30),
myfile blob
)
Lob (myfile) Store As
(
Tablespace JOHN
Chunk 15K
Disable Storage In Row
);
插入指针
INSERT INTO BLOBFILE VALUES (1, 'IMG_0210.JPG', EMPTY_BLOB ());
2,逻辑目录依然用dump1目录
3,声明一个BLOB类型变量,使用select into 语句让其指向到empty_blob()构造空的指针所指向的存储空间
SELECT BLOBFILE INTO BLOB类型变量 FROM BLOBFILE WHERE no = 1 FOR UPDATE;
4,声明一个BFile类型变量,关联逻辑目录和物理目录文件,使用 BFileName() 将其指向到待存储的文件。
BFile类型变量 := BFileName('MYDIR','IMG_0210.JPG');
5,使用DBMS_LOB.open()方法将BFile类型变量所指向的文件打开
DBMS_LOB.Open(BFile类型变量);
6,使用DBMS_LOB.loadfromfile()方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));
7,使用DBMS_LOB.close()方法将bfile的变量所指向的文件关闭
DBMS_LOB.Close(BFile类型变量);
8,提交事务
Commit;
例子;
Declare
varB blob;
varF Bfile;
Begin
select myfile into varB from BLOBFILE where no = 1 for update;
varF := bfileName('DUMP1', 'Screenshot.png');
DBMS_LOB.open(varF);
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--查看文件大小
Declare
varB blob;
Begin
select myfile into varB from BLOBFILE where no = 1;
DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
End;
例子:创建过程存储图片或二进制文件
Create Or Replace Procedure setBLOB(vFileName varchar2)
As
varF bfile;
varB blob;
vno number(8);
Begin
varF := BFilename('MYDIR',vFileName);
DBMS_LOB.Open(varF);
select max(no) into vno from BLOBFILE;
if vno is null then
vno := 1;
else
vno := vno + 1;
end if;
insert into BLOBFILE values(vno,vFileName,empty_blob());
select myFile into varB from BLOBFILE where no = vno for update;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;
--执行过程
EXEC setBLOB('Screenshot.png');
--查看文件大小
Declare
varB blob;
Begin
select myfile into varB from BLOBFILE where no = 2;
DBMS_output.put_line('长度为: '||DBMS_LOB.getlength(varB));
End;
###########################
本文由转载自dave大师博客,略做修改
作者:john
转载请注明来自dave大师博客