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