ORACLE LOB 大对象处理

来源：互联网发布：美工组长岗位说明书编辑：程序博客网时间：2024/05/07 00:04

LOB大对象处理：主要是用来存储大量数据的数据库字段，最大可以存储 128TB 的数据，这取决于数据库的配置。

主要介绍字符类型和二进制文件类型 LOB 数据的存储，单独介绍二进制类型 LOB 数据的存储。

一、 Oracle中的LOB数据类型分类

1.1 按存储数据的类型分：

a. 字符类型：

CLOB：存储大量单字节字符数据
NLOB：存储定宽多字节字符数据

b. 二进制类型：

BLOB：存储较大无结构的二进制数据

c. 二进制文件类型：

BFILE：将二进制文件存储在数据库外部的操作系统文件中，存放文件路径 ( 指向文件的指针 )

2.1 按存储方式分：

a. 存储在内部表空间：

CLOB，NLOB和BLOB

b. 指向外部操作系统文件：

BFILE

二、大对象数据的录入

2.1 声明 LOB 类型列

create tablespace test_lob datafile '/u01/oracle/oradata/orcl/test_lob.dbf' size 20m;

create table t_lob (
id integer,
name varchar2(10),
resume clob,
photo blob,
record bfile
)

lob (resume,photo) store as (
tablespace test_lob -- 指定存储的表空间
chunk 6k -- 指定数据块大小
disable storage in row -- 指定 lob 数据存储在行外
);

2.2 插入大对象列

a. 先插入普通列数据

b. 遇到大对象列时，插入空白构造函数
字符型：empty_clob()、empty_nclob()
二进制型：empty_blob()
二进制文件类型：bfilename 函数指向外部文件
bfilename函数：
bfilename(‘目录对象名’，‘文件名’);
目录对象名只能大写，因为数据词典是以大写方式存储。Oracle是区分大小写的
在创建时，无需将bfilename函数目录对象指向文件名，使用时才做检查二者是否关联

c. 将目录对象名和文件名关联 ( 如果是二进制文件类型 )
授予 CREATE ANY DIRECTORY 权限
GRANT CREATE ANY DIRECTORY TO user_name WITH ADMIN OPTION;

关联目录对象名和文件名
本地：
CREATE DIRECTORY 对象目录名 AS ‘文件名’;
网络：
CREATE DIRECTORY 对象目录名 AS ‘//主机名(IP)/共享目录’;

授予用户对目录对象名的读写权限

GRANT read,write ON DIRECTORY directory_name TO user_name;

例子：
CREATE DIRECTORY SAMPLE_FILES_DIR AS ' /tmp/sample_files';

GRANT read,write ON DIRECTORY SAMPLE_FILES_DIR TO user_name;
插入例子：

INSERT INTO t_lob

VALUES (1,

'Dave',

TO_CLOB('CLOB_Column'),

EMPTY_BLOB (),

BFILENAME ('SAMPLE_FILES_DIR', 'textContent.txt'));

INSERT INTO t_lob

VALUES (2,

'Dave',

TO_CLOB('CLOB_Column'),

EMPTY_BLOB (),

BFILENAME ('SAMPLE_FILES_DIR', 'textContent.txt'));

INSERT INTO t_lob

VALUES (3,

'Dave',

TO_CLOB( 'CLOB_Column'),

EMPTY_BLOB (),

BFILENAME ('SAMPLE_FILES_DIR', 'textContent.txt'));

三、大对象数据的读取和操作：DBMS_LOB包

DBMS_LOB包：包含处理大对象的过程和函数

3.1 读取大对象数据的过程和函数

a. 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 t_lob WHERE id = 1;

LN := DBMS_LOB.GetLength (varC);

Strt := 1;

DBMS_LOB.Read (varC,LN,Strt,vRStr);

DBMS_OUTPUT.put_line ('Return: ' || vRStr);

END;

b. 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 t_lob WHERE id = 1;

LN := 4;

Strt := 1;

vRStr := DBMS_LOB.SUBSTR (varC, LN, Strt);

DBMS_OUTPUT.put_line ('Result is : ' || vRStr);

END;

c. 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 t_lob WHERE id = 1;

vSubStr := 'CLOB';

LN := DBMS_LOB.INSTR (varC, vSubStr);

DBMS_OUTPUT.put_line ('Position is : ' || LN);

vRStr := DBMS_LOB.SUBSTR (varC, LENGTH (vSubStr), LN);

DBMS_OUTPUT.put_line('Position is :'|| LN || ' ; ' || 'Length is ' || LENGTH (vSubStr)

|| ' ; ' || 'Substr is ：'|| vRStr);

END;

d. DBMS_LOB.GetLength()：返回指定LOB数据的长度的函数
DBMS_LOB.GetLength(LOB数据)；

e. 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 t_lob WHERE id = 1;

SELECT resume INTO varC2 FROM t_lob WHERE id = 2;

SELECT resume INTO varC3 FROM t_lob WHERE id = 3;

LN := DBMS_LOB.Compare (varC1, varC1);

DBMS_OUTPUT.put_line ('The Compare Result is : ' || LN);

LN := DBMS_LOB.Compare (varC2, varC3);

DBMS_OUTPUT.put_line ('The Compare Result is : ' || LN);

END;

3.2 操作大对象数据的过程

操作会改变数据库中原有数据，需要加上 Updata锁锁上指定数据列，修改完后提交事务
a. DBMS_LOB.Write()：将指定数量的数据写入LOB的过程
DBMS_LOB.Write(被写入LOB, 写入长度(指写入LOB数据), 写入起始位置(指被写入LOB), 写入LOB数据);
例子：

DECLARE

varC CLOB; -- 被写入LOB

LN NUMBER(4); -- 写入长度

vStrt NUMBER(4); -- 写入起始位置

vWStr VARCHAR2 (1000); -- 写入LOB数据

BEGIN

SELECT resume INTO varC FROM t_lob WHERE id = 1 FOR UPDATE;

vStrt := 5;

vWStr := 'CLOB';

LN := LENGTH (vWStr);

DBMS_LOB.Write (varC,LN,vStrt,vWStr);

DBMS_OUTPUT.put_line ('The Result is : ' || varC);

COMMIT;

END;

b. DBMS_LOB.Append()：将指定的LOB数据追加到指定的LOB数据后的过程
DBMS_LOB.Append(LOB数据, LOB数据)
例子：

DECLARE

varC CLOB;

vAStr VARCHAR2 (1000);

BEGIN

vAStr := ', This is a Large Object Column';

SELECT resume INTO varC FROM t_lob WHERE id = 1 FOR UPDATE;

DBMS_LOB.Append (varC, vAStr);

COMMIT;

DBMS_OUTPUT.put_line ('Appended Result is : ' || varC);

END;

c. 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 t_lob WHERE id = 1 FOR UPDATE;

DBMS_LOB.ERASE (varC, LN, strt);

COMMIT;

DBMS_OUTPUT.put_line ('Erased Result is : ' || varC);

END;

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

例子：

DECLARE

varC CLOB;

LN NUMBER (4);

BEGIN

LN := 4;

SELECT resume INTO varC FROM t_lob WHERE id = 1 FOR UPDATE;

DBMS_LOB.TRIM (varC, LN);

COMMIT;

DBMS_OUTPUT.put_line ('Trimed Result is : ' || varC);

END;

e. 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 t_lob WHERE id = 1 FOR UPDATE;

SELECT resume INTO vSRC_LOB FROM t_lob WHERE id = 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);

--无COMMIT;

DBMS_OUTPUT.put_line ('Copied Result is : ' || vDEST_LOB);

END;

四、图片的存储或二进制文件的存储

4.1 先插入普通数据，遇到大对象列使用empty_blob() 构造空的指针
例子：

create tablespace test_lob datafile '/u01/oracle/oradata/orcl/test_lob.dbf' size 20m;

create table my_lob

(

id number(8) primary key,

fname varchar2(30),

myfile blob

)

Lob (myfile) store as

(

tablespace test_lob

chunk 15K

disable storage in row

);

INSERT INTO my_lob VALUES (1, 'IMG_0210.JPG', EMPTY_BLOB ());

4.2 创建逻辑目录MYDIR( 注意是目录 )
create directory MY_DIR as ' /tmp/IMG_0210';

-- 下面需要使用 PL/SQL 完成
4.3 声明一个 BLOB 类型变量，使用 select into 语句让其指向到 empty_blob() 构造空的指针所指向的存储空间

SELECT myfile INTO BLOB类型变量 FROM my_Lob WHERE id = 1 FOR UPDATE;

4.4 声明一个 BFile 类型变量，关联逻辑目录和物理目录文件，使用 BFileName() 将其指向到待存储的文件
BFile类型变量 := BFileName('MY_DIR','IMG_0210.JPG');

4.5 使用 DBMS_LOB.open() 方法将BFile类型变量所指向的文件打开
DBMS_LOB.Open(BFile类型变量);

4.6 使用 DBMS_LOB.loadfromfile() 方法将BFile类型变量所指向的文件读入到BLOB类型变量所指向的存储空间
DBMS_LOB.LoadfromFile(BLOB类型变量,BFile类型变量,DBMS_LOB.getlength(BFile类型变量));

4.7 使用DBMS_LOB.close() 方法将bfile的变量所指向的文件关闭
DBMS_LOB.Close(BFile类型变量);

4.8 提交事务
commit;

  例子：
DECLARE
varB BLOB;
varF BFILE;
BEGIN
select myfile into varB from my_lob where id = 1 for update;
varF := BFILENAME('MY_DIR','IMG_0210.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 my_lob where id = 1;
DBMS_OUTPUT.PUT_LINE('length is : '||DBMS_LOB.getlength(varB));
END;

例子：创建过程存储图片或二进制文件
create or replace procedure setBLOB(vFileName varchar2)
AS
varF bfile;
varB blob;
vno number(8);
BEGIN
varF := BFILENAME('MY_DIR',vFileName);
DBMS_LOB.Open(varF);
select max(id) into vno from my_lob;
if vno is null then
vno := 1;
else
vno := vno + 1;
end if;
insert into my_lob values(vno,vFileName,empty_blob());
select myfile into varB from my_lob where id = vno for update;
DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
DBMS_LOB.close(varF);
COMMIT;
END;

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

-- 查看文件大小
DECLARE
varB BLOB;
BEGIN
select myfile into varB from my_lob where id = 2;
DBMS_output.put_line('length is : '||DBMS_LOB.getlength(varB));
END;

0 0