Oracle 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

转自：http://blog.sina.com.cn/s/blog_7282b74b0100rnqp.html