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 DIRECTO RY 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;