LOB主要用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。主要存储字符类型或者二进制类型数据。
一、数据类型分类
1.1按存储数据的类型分：
1.字符类型
CLOB:存储大量单字节字符数据
NLOB:存储定宽多字节数据。
2.二进制类型
BLOB：存储较大的无结构的二进制数据。
3.二进制文件类型
BFILE：将二进制文件存储在数据库外部的操作系统文件中。
1.2按存储方式
1.存储内外表中
CLOB,NLOB,BLOB
2.操作系统文件
BFILE
二、数据插入
2.1声明LOB类型
SQL> create table testlob(
id number,
name varchar2(10),
resume clob,
photo blob,
log bfile
)
lob(resume,photo) store as (
tablespace users
chunk 8k
disable storage in row
);
其中，store as (enable storage in row|disable storage in row)
表示是否允许lob数据保存在行内（与其他字段数据放在表段）。
对于enable storage in row，表示允许小于4000字节的lob字段信息保存在表段，是默认值，
对于大于4000字节的lob字段保存在lob段（同disable storage in row），
当指定enable storage in row的时候，当lob size <4000 bytes的时候，将存储在表数据段里面，“同普通字段没有区别“，可以发生行迁移。
当指定enable storage in row的时候，当lob size >=4000 bytes的时候,将存储在lob段里面，其存储方式和表段存储方式完全不一样，
使用的是chunk为最小单位的存储，没有行迁移和行链接的概念。
2.2数据插入
对于大对象列，先插入空白函数。
字符型：empty_clob(),empty_nclob()
二进制型：empty_blob()
二进制文件类型:bfilename指向外部文件。bfilename('目录名','文件名');
目录名要大写，因为在数据字典中的存储方式为大写。
创建时，不需要将BFILENAME函数逻辑目录指向实际物理目录，使用时才关联。
2.3 逻辑目录和物理目录关联
1.授予权限
grant create any directory to username with admin option;
create or replace 逻辑目录 as '物理目录';
insert into testlob values (1,'Jerry','CLOB',empty_blob(),bfilename('MYDIR','test.jpg'));
SQL> insert into testlob values (1,'Jerry','CLOB',empty_blob(),bfilename('MYDIR','test.jpg'));
1 row inserted
SQL> commit;
SQL> create or replace directory MYDIR as 'D:/';
create or replace directory MYDIR as 'D:/'
ORA-01031: 权限不足
SQL> conn / as sysdba;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
SQL> grant create any directory to scott;
Grant succeeded
SQL> conn scott/tiger;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> create or replace directory MYDIR as 'D:/';
Directory created
三、LOB数据的读取和操作，DBMS_LOB包
3.1.读取 DBMS_LOB.read():从LOB数据中读取指定长度数据到缓冲区的过程。
DBMS_LOB.read(LOB数据, 指定长度, 起始位置, 存储返回LOB类型值变量)
示例：
SQL> set serveroutput on;
SQL> DECLARE
varC CLOB;
vRStr VARCHAR2 (1000);
LN NUMBER (4);
Strt NUMBER (4);
BEGIN
SELECT resume
INTO varC
FROM testlob
WHERE id = 1;
LN := DBMS_LOB.GetLength (varC);
Strt := 1;
DBMS_LOB.Read (varC,
LN,
Strt,
vRStr);
DBMS_OUTPUT.put_line ('Return: ' || vRStr);
END;
SQL> /
Return: CLOB
3.2 DBMS_LOB.substr():从LOB数据中提取子字符串的函数。
DBMS_LOB.substr(LOB数据，指定提取长度，提取起始位置)
SQL> declare
varc clob;
vrstr varchar2(1000);
length number(4);
startPosition number(4);
begin
select resume into varc from testlob where id=1;
length :=3;
startPosition :=1;
vrstr := dbms_lob.substr(varc, length, startPosition);
dbms_output.put_line('结果为：' || vrstr);
end;
/
结果为：CLO
PL/SQL procedure successfully completed
3.3 DBMS_LOB.instr():从LOB数据中查找字符串位置的函数。
DBMS_LOB.instr(LOB数据，子字符串);
SQL> DECLARE
varC CLOB;
vSubStr VARCHAR2 (1000);
vRStr VARCHAR2 (1000);
LN NUMBER (4);
BEGIN
SELECT resume
INTO varC
FROM testlob
WHERE id = 1;
vSubStr := 'OB';
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;
/
位置为: 3
位置为3长度为2的子字符串为：OB
PL/SQL procedure successfully completed
3.4DBMS_LOB.GetLength()：返回指定LOB数据的长度的函数。
DBMS_LOB.GetLength(LOB数据)；
SQL> declare
length number;
varc clob;
begin
select resume into varc from testlob where id=1;
length := dbms_lob.getlength(varc);
dbms_output.put_line('RESUME的长度为'||length);
end;
/
RESUME的长度为4
PL/SQL procedure successfully completed
3.5 DBMS_LOB.Compare()：比较二个大对象是否相等。返回数值0为相等，-1为不相等。
DBMS_LOB.Compare(LOB数据，LOB数据)；
SQL> DECLARE
varC1 CLOB;
varC2 CLOB;
varC3 CLOB;
LN NUMBER (4);
BEGIN
SELECT resume
INTO varC1
FROM testlob
WHERE id = 1;
SELECT resume
INTO varC2
FROM testlob
WHERE id = 1;
LN := DBMS_LOB.Compare (varC1, varC1);
DBMS_OUTPUT.put_line ('比较的结果为: ' || LN);
LN := DBMS_LOB.Compare (varC2, varC3);
DBMS_OUTPUT.put_line ('比较的结果为: ' || LN);
END;
/
比较的结果为: 0
比较的结果为:
PL/SQL procedure successfully completed
四.数据修改
LOB数据修改，需要锁住被修改列，修改后提交
4.1 dbms_log.write(被写入LOB, 写入长度(指写入LOB数据)，写入起始位置(指被写入LOB)，写入LOB数据);
DECLARE
varC CLOB;
vWStr VARCHAR2 (1000);
vStrt NUMBER (4);
LN NUMBER (4);
BEGIN
vWStr := '附加的CLOB';
LN := LENGTH (vWStr);
SELECT resume
INTO varC
FROM testlob
WHERE id = 1
FOR UPDATE ;
vStrt := length(varC) + 1;
DBMS_LOB.Write (varC,
LN,
vStrt,
vWStr);
DBMS_OUTPUT.put_line ('改写结果为: ' || varC);
COMMIT;
END;
SQL> DECLARE
varC CLOB;
vWStr VARCHAR2 (1000);
vStrt NUMBER (4);
LN NUMBER (4);
BEGIN
vWStr := '附加的CLOB';
LN := LENGTH (vWStr);
SELECT resume
INTO varC
FROM testlob
WHERE id = 1
FOR UPDATE ;
vStrt := length(varC) + 1;
DBMS_LOB.Write (varC,
LN,
vStrt,
vWStr);
DBMS_OUTPUT.put_line ('改写结果为: ' || varC);
COMMIT;
END;
/
改写结果为: CLOB附加的CLOB
PL/SQL procedure successfully completed
如果未加入for update,则出现ORA-22920错误：
ORA-22920: 未锁定含有 LOB 值的行
ORA-06512: 在 "SYS.DBMS_LOB", line 1040
ORA-06512: 在 line 19
4.2 DBMS_LOB.Append()：将指定的LOB数据追加到指定的LOB数据后的过程。
DBMS_LOB.Append(LOB数据，LOB数据)；
SQL> DECLARE
varC CLOB;
vAStr VARCHAR2 (1000);
BEGIN
vAStr := '，这是大对象列';
SELECT resume
INTO varC
FROM testlob
WHERE id = 1
FOR UPDATE ;
DBMS_LOB.Append (varC, vAStr);
COMMIT;
DBMS_OUTPUT.put_line ('追加结果为: ' || varC);
END;
/
追加结果为: CLOB附加的CLOB，这是大对象列
PL/SQL procedure successfully completed
4.3DBMS_LOB.Erase()：删除LOB数据中指定位置的部分数据的过程；
DBMS_LOB.Erase(LOB数据，指定删除长度, 开始删除位置)；
SQL> DECLARE
varC CLOB;
LN NUMBER (4);
strt NUMBER (4);
BEGIN
LN := 1;
strt := 5;
SELECT resume
INTO varC
FROM testlob
WHERE id = 1
FOR UPDATE ;
DBMS_LOB.ERASE (varC, LN, strt);
COMMIT;
DBMS_OUTPUT.put_line ('删除结果为: ' || varC);
END;
/
删除结果为: CLOB 加的CLOB，这是大对象列
PL/SQL procedure successfully completed
4.4DBMS_LOB.Trim()：截断LOB数据中从第一位置开始指定长度的部分数据的过程；
DBMS_LOB.Trim(LOB数据，截断长度);
SQL> DECLARE
2 varC CLOB;
3 LN NUMBER (4);
4 BEGIN
5 LN := 4;
6 SELECT resume
7 INTO varC
8 FROM testlob
9 WHERE id = 1
10 FOR UPDATE ;
11
12 DBMS_LOB.TRIM (varC, LN);
13 COMMIT;
14 DBMS_OUTPUT.put_line ('截断结果为: ' || varC);
15 END;
16 /
截断结果为: CLOB
PL/SQL procedure successfully completed
4.5 BMS_LOB.Copy()：从指定位置开始将源LOB复制到目标LOB；
DBMS_LOB.Copy(目标LOB，源LOB，复制源LOB长度，复制到目标LOB开始位置，复制源LOB开始位置)
SQL> DECLARE
vDEST_LOB CLOB;
vSRC_LOB CLOB;
AMOUNT NUMBER;
DEST_OFFSET NUMBER;
SRC_OFFSET NUMBER;
BEGIN
SELECT resume
INTO vDEST_LOB
FROM testlob
WHERE id = 1
FOR UPDATE ;
SELECT resume
INTO vSRC_LOB
FROM testlob
WHERE id = 1;
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;
/
拷贝结果为: CLOBCLOB
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
五、二进制文件的存储
5.1存储图片
SQL> create or replace directory MYDIR as 'D:/';
Directory created
SQL> Declare
2 varB blob;
3 varF Bfile;
4 Begin
5 select photo into varB from testlob where id = 1 for update;
6 varF := bfileName('MYDIR','test.jpg');
7 DBMS_LOB.open(varF);
8 DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));
9 DBMS_LOB.close(varF);
10 commit;
11 End;
12 /
PL/SQL procedure successfully completed
SQL> Declare
2 varB blob;
3 Begin
4 select photo into varB from testlob where id = 1;
5 DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
6 End;
7 /
长度为: 305664
PL/SQL procedure successfully completed
5.2 存储BFILE
SQL>
SQL> DECLARE
waterfall_picture BFILE;
BEGIN
--Invoke BFILENAME to create a BFILE locator
waterfall_picture := BFILENAME('MYDIR','test.jpg');
--Save our new locator in the waterfalls table
update testlob set log=waterfall_picture where id=1;
END;
/
SQL> Declare
varB bfile;
Begin
select log into varB from testlob where id = 1;
DBMS_OUTPUT.PUT_LINE('长度为: '||DBMS_LOB.getlength(varB));
End;
/
长度为: 305664
PL/SQL procedure successfully completed
阅读(2660) | 评论(0) | 转发(0) |
