ORACLE LOB 大对象处理

于 2011-09-28 13:40:00 发布
阅读量105 收藏
点赞数
文章标签： 数据库 操作系统 人工智能
本文深入探讨了数据库中LOB大对象处理的相关知识，包括不同类型的LOB数据存储方式、录入方法及读取操作，并通过示例展示了如何在Oracle环境中实现大对象数据的存储与管理。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

LOB大对象处理：主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。主要介绍字符类型和二进制文件类型LOB数据的存储，单独介绍二进制类型LOB数据的存储。

一.Oracle中的LOB数据类型分类
1，按存储数据的类型分：
①字符类型：
CLOB:存储大量单字节字符数据。
NLOB:存储定宽多字节字符数据。
②二进制类型：
BLOB:存储较大无结构的二进制数据。
③二进制文件类型：
BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

2，按存储方式分：
①存储在内部表空间：
CLOB，NLOB和BLOB
②指向外部操作系统文件：
BFILE

二，大对象数据的录入
2.1，声明LOB类型列

Create TableSpace DaveDataFile 'D:/Dave/dave.dbf' Size 20m;

Create Table tLob (
no Number(4),
name VarChar2(10),
resume CLob,
photo BLob,
record BFile
)
Lob (resume,photo)Store As (
Tablespace dave --指定存储的表空间
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 MYDIR As 'D:/Dave';

插入例子：
/* Formatted on 2009-12-24 17:27:00 (QP5 v5.115.810.9015) */

INSERTINTOtlob

VALUES(1,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

INSERTINTOtlob

VALUES(2,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

INSERTINTOtlob

VALUES(3,

'Dave',

'CLOB大对象列',

EMPTY_BLOB(),

BFILENAME('MYDIR','Dave.jpg'));

三，大对象数据的读取和操作：DBMS_LOB包
DBMS_LOB包：包含处理大对象的过程和函数


1，读取大对象数据的过程和函数
①：DBMS_LOB.Read()：从LOB数据中读取指定长度数据到缓冲区的过程。
DBMS_LOB.Read(LOB数据，指定长度，起始位置，

存储返回LOB类型值变量)；
例子：

/* Formatted on 2009-12-24 17:25:01 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

vRStrVARCHAR2(1000);

LNNUMBER(4);

StrtNUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=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数据，指定提取长度，提取起始位置):


例子：
/* Formatted on 2009-12-24 17:24:35 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

vRStrVARCHAR2(1000);

LNNUMBER(4);

StrtNUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=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数据,子字符串);


例子：
/* Formatted on 2009-12-24 17:25:39 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

vSubStrVARCHAR2(1000);

vRStrVARCHAR2(1000);

LNNUMBER(4);

BEGIN

SELECTresume

INTOvarC

FROMtlob

WHEREno=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数据)；


例子：
/* Formatted on 2009-12-24 17:26:19 (QP5 v5.115.810.9015) */

DECLARE

varC1CLOB;

varC2CLOB;

varC3CLOB;

LNNUMBER(4);

BEGIN

SELECTresume

INTOvarC1

FROMtlob

WHEREno=1;

SELECTresume

INTOvarC2

FROMtlob

WHEREno=2;

SELECTresume

INTOvarC3

FROMtlob

WHEREno=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数据);

例子：
/* Formatted on 2009-12-24 17:43:38 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

vWStrVARCHAR2(1000);

vStrtNUMBER(4);

LNNUMBER(4);

BEGIN

vWStr:='CLOB';

LN:=LENGTH(vWStr);

vStrt:=5;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.Write(varC,

LN,

vStrt,

vWStr);

DBMS_OUTPUT.put_line('改写结果为:'|| varC);

COMMIT;

END;


②：DBMS_LOB.Append()：将指定的LOB数据追加到指定的LOB数据后的过程。
DBMS_LOB.Append(LOB数据，LOB数据)；
例子：
/* Formatted on 2009-12-24 17:44:17 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

vAStrVARCHAR2(1000);

BEGIN

vAStr:='，这是大对象列';

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.Append(varC,vAStr);

COMMIT;

DBMS_OUTPUT.put_line('追加结果为:'|| varC);

END;



③：DBMS_LOB.Erase()：删除LOB数据中指定位置的部分数据的过程；
DBMS_LOB.Erase(LOB数据，指定删除长度,开始删除位置)；

例子：
/* Formatted on 2009-12-24 17:44:45 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

LNNUMBER(4);

strtNUMBER(4);

BEGIN

LN:=1;

strt:=5;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

DBMS_LOB.ERASE(varC,LN,strt);

COMMIT;

DBMS_OUTPUT.put_line('擦除结果为:'|| varC);

END;

④：DBMS_LOB.Trim()：截断LOB数据中从第一位置开始指定长度的部分数据的过程；
DBMS_LOB.Trim(LOB数据，截断长度)；

例子：
/* Formatted on 2009-12-24 17:45:09 (QP5 v5.115.810.9015) */

DECLARE

varCCLOB;

LNNUMBER(4);

BEGIN

LN:=4;

SELECTresume

INTOvarC

FROMtlob

WHEREno=1

FORUPDATE;

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开始位置)


例子：
/* Formatted on 2009-12-24 17:45:30 (QP5 v5.115.810.9015) */

DECLARE

vDEST_LOBCLOB;

vSRC_LOBCLOB;

AMOUNTNUMBER;

DEST_OFFSETNUMBER;

SRC_OFFSETNUMBER;

BEGIN

SELECTresume

INTOvDEST_LOB

FROMtlob

WHEREno=1

FORUPDATE;

SELECTresume

INTOvSRC_LOB

FROMtlob

WHEREno=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()构造空的指针。
例子：


/* Formatted on 2009-12-24 18:02:34 (QP5 v5.115.810.9015) */

CREATETABLESPACEDaveDATAFILE'D:/Dave/dave.dbf'SIZE20M;

CreateTableMyLob

(

nonumber(8)primarykey,

fnamevarchar2(30),

myfileblob

)

Lob(myfile)StoreAs

(

TablespaceDave

Chunk15K

DisableStorageInRow

);

INSERTINTOMyLob

VALUES(1,'IMG_0210.JPG',EMPTY_BLOB());

2，创建逻辑目录MYDIR
Create Directory MYDIR As 'D:/Dave';

3，声明一个BLOB类型变量，使用select into语句让其指向到empty_blob()构造空的指针所指向的存储空间
/* Formatted on 2009-12-24 18:07:25 (QP5 v5.115.810.9015) */

SELECTmyfile

INTOBLOB类型变量

FROMmyLob

WHEREno=1

FORUPDATE;

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 myLob where no = 1 for update;
varF := bfileName('MYDIR','Dave.JPG');
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 myLob 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 myLob;
if vno is null then
vno := 1;
else
vno := vno + 1;
end if;
insert into myLob values(vno,vFileName,empty_blob());
select myFile into varB from myLob where no = vno for update;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
commit;
End;

--执行过程
EXEC setBLOB('Dave.JPG');

--查看文件大小
Declare
varB blob;
Begin
select myfile into varB from myLob where no = 2;
DBMS_output.put_line('长度为: '||DBMS_LOB.getlength(varB));
End;

iteye_3158
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值