oracle dbms_lob trim,DBMS_LOB包基础应用

DBMS_LOB包应用

1、

dbms_lob.compare (lob_1 in blob/clob/bfile, lob_2 in

blob/clob/bfile,amount in integer:=4294967295,offset_1 in

integer:=1,offset2 in integer:=1) return integer;

如果比较结果相同则返回0，不同则返回其他非0整数。

select

id,in_lob,out_lob,dbms_lob.compare(in_lob,out_lob)

from

nyytest.lob_table

where

id=1;

ID

IN_LOB

OUT_LOB

DBMS_LOB.COMPARE(IN_LOB,OUT_LOB)

1

SYS DUAL TABLE VALID

SYS DUAL TABLE VALID

0

2、dbms_lob.append

将源lob变量的值添加到目标lob变量的尾部，只适用于clob和blob，bfile不适用。APPEND过程把1个LOB中的数据添加到第2个LOB中。由于需要对1个LOB值进行更新操作，所以在执行APPEND过程前，被更新的记录必须要锁定。

Dbms_lob.append(dest_log,src_lob)

测试时遇到了一个错误ORA-22920: 未锁定含有 LOB 值的行

原语句如下：

declare

des_lob clob;

src_lob clob;

begin

select in_lob into des_lob

from

lob_table where id=1;

src_lob:=' nyyadd';

dbms_lob.append(des_lob,src_lob);

dbms_output.put_line(des_lob);

end;

ORACLE官方文档对该错误的说明：

ORA-22920 row

containing the LOB value is not locked

Cause: The row

containing the LOB value must be locked before updating the LOB

value.

Action: Lock the

row containing the LOB value before updating the LOB

value.

修改pl/sql程序，增加for

update之后执行正常

declare

des_lob clob;

src_lob clob;

begin

select in_lob into des_lob

from

lob_table where id=1 for update;

src_lob:=' nyyadd';

dbms_lob.append(des_lob,src_lob);

dbms_output.put_line(des_lob);

end;

DBMS Output for Batch 1

SYS DUAL TABLE VALIDnyyadd nyyadd

nyyadd

3、

DBMS_LOB.ERASE(

lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,

amount IN OUT NOCOPY INTEGER,--

总数变量(即要删除的字节或字符个数)

offset IN INTEGER:=1—起始位置

)

如果删除一个BLOB值中的数据，则代之以空格填充此CLOB。由于对一个LOB值执行了更新，所以必须用SELECT FOR UPDATE命令来锁定行数据

用户删除lob变量的全部内容或部分内容，只适用于内部LOB，不适用bfile

select

id,in_lob,out_lob

from

lob_table

where

id=1

ID

IN_LOB

OUT_LOB

1

SYS DUAL TABLE VALID

SYS DUAL TABLE VALID

declare

l_1

clob;

l_2

clob;

amount number;

offset number;

begin

select in_lob into l_1

from

lob_table

where

id=1 for update;

amount:=6;

offset:=3;

dbms_lob.erase(l_1,amount,offset);

dbms_output.put_line(l_1);

end;

DBMS Output for Batch 1

SY

TABLE VALID

---查看表里的数据也相应发生了变化

select id,in_lob,out_lob

from

lob_table

where

id=1

ID

IN_LOB

OUT_LOB

1

SY

TABLE VALID

SYS DUAL TABLE VALID

4、DBMS_LOB.WRITE(

Lob_loc IN OUT NOCOPY BLOB/CLOB,

Amount IN BINARY_INTEGER,--要写入的字节或字符个数

Offset IN INTEGER,--写操作的起始点

Buffer IN RAW/VARCHAR2-- 分配被写入的字符串或二进制数据的缓冲区变量

)

WRITE过程允许在LOB中指定的位置写入数据。例如，可以在一个BLOB列的某一部分中写入二进制数据，并覆盖掉已有的数据。也可以使用WRITE。由于WRITE过程会更新LOB的值，所以必须用SELECT FOR UPDATE命令来锁定行数据。

declare

l_1

clob;

l_2

varchar2(20);

amount number;

offset number;

begin

select in_lob into l_1

from

lob_table

where

id=2 for update;

l_2:='测试110524';

amount:=length(l_2);

offset:=dbms_lob.getlength(l_1)+1;

dbms_lob.write(l_1,amount,offset,l_2);

dbms_output.put_line(l_1);

end;

DBMS Output for Batch 1

PUBLIC DUAL SYNONYM

VALID测试110524

5、DBMS_LOB.GELENGTH

获取LOB类型的长度，适用于bfile、blob、clob、nclob

6、 DBMS_LOB.INSTR(

Lob_loc IN BLOB/CLOB/NCLOB/BFILE,

Pattern IN RAW/VARCHAR2,--指定要搜索的二进制串或者字符串

Offset IN INTERGER:=1,--搜索的起始位置

Nth IN INTEGER:=1

--指定次数

) RETURN INTEGER；

INSTR函数在一个LOB数据值上执行函数SQL INSTR，返回特定数据在lob中从某位置开始第n次出现的位置，适用于bfile、blob、clob、nclob

select

id,in_lob,dbms_lob.getlength(in_lob),dbms_lob.instr(in_lob,'N',1,2),out_lob

from

lob_table

where

id=2

ID

IN_LOB

DBMS_LOB.GETLENGTH(IN_LOB)

DBMS_LOB.INSTR(IN_LOB,'N',1,2)

OUT_LOB

2

PUBLIC DUAL SYNONYM VALID测试110524

33

17

PUBLIC DUAL SYNONYM VALID

7、DBMS_LOB.SUBSTR

(

Lob_loc IN BLOB/CLOB/BFILE,

Amount IN INTEGER:=32762,-- 截取的字节或字符个数

Offset IN INTEGER:=1--开始位置

) RETURN RAW/VARCHAR2

在一个LOB数据值上执行SQL SUBSTR函数,返回从指定位置开始的部分内容，适用于bfile、blob、clob、nclob

select id,in_lob,dbms_lob.substr(in_lob,4,3)

from

lob_table

where

id=2

ID

IN_LOB

DBMS_LOB.SUBSTR(IN_LOB,4,3)

2

PUBLIC DUAL SYNONYM VALID测试110524

BLIC

8、DBMS_LOB.TRIM

(

Lob_loc IN OUT NOCOPY BLOB/CLOB/NCLOB,

Newlen IN INTEGER—截断后lob长度

)

从值的结尾处开始删除字符或字节(与SQL RTRIM函数类似)。该过程更新LOB列，所以需要锁定和解锁

declare

l_1

clob;

begin

select in_lob into l_1

from

lob_table

where

id=2 for update;

dbms_lob.trim(l_1,10);

dbms_output.put_line(l_1);

end;

DBMS Output for Batch 1

PUBLIC DUA