oracle dbms_lob trim,ORACLE LOB处理

最新推荐文章于 2024-06-04 10:56:37 发布

抹茶牛奶泡芙

最新推荐文章于 2024-06-04 10:56:37 发布

阅读量512

点赞数

文章标签： oracle dbms_lob trim

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函数逻辑目录指向实际物理目录，使用时才关联。

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 ;

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

阅读(1331) | 评论(0) | 转发(0) |

抹茶牛奶泡芙

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle dbms_lob trim,ORACLE LOB处理

LOB主要用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。主要存储字符类型或者二进制类型数据。一、数据类型分类1.1按存储数据的类型分：1.字符类型CLOB:存储大量单字节字符数据NLOB:存储定宽多字节数据。2.二进制类型BLOB：存储较大的无结构的二进制数据。3.二进制文件类型BFILE：将二进制文件存储在数据库外部的操作系统文件中。1.2按存储方式1.存储内外表中CLOB,...
复制链接

扫一扫