DBMS_LOB

功能描述

DBMS_LOB包提供了用于处理BLOB（二进制数据类型）、CLOB（字符数据类型）和BFILE数据类型（存储数据库外的大型对象，指向操作系统物理文件）的子程序。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。
NCLOB 是 CLOB 数据类型在Oracle兼容模式中的别名。因此，本文中的 CLOB 数据类型均可使用 NCLOB 数据类型代替。

支持范围

常量

表1 DBMS_LOB支持的常量

常量	值
LOBMAXSIZE	1073741832
DEFAULT_CSID	0
DEFAULT_LANG_CTX	0

LOBMAXSIZE

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量lobmaxsize的值。

declare 
begin
  dbms_output.put_line(dbms_lob.lobmaxsize);
end;
/

返回结果为：

1073741823
ANONYMOUS BLOCK EXECUTE

DEFAULT_CSID

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量default_csid的值。

declare 
begin
  dbms_output.put_line(dbms_lob.default_csid);
end;
/

返回结果为：

0
ANONYMOUS BLOCK EXECUTE

DEFAULT_LANG_CTX

示例

1、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

2、打印常量default_lang_ctx的值。

declare 
begin
  dbms_output.put_line(dbms_lob.default_lang_ctx);
end;
/

返回结果为：

0
ANONYMOUS BLOCK EXECUTE

函数

表2 DBMS_LOB支持的函数

函数	描述
FILEEXISTS	检查服务器是否存在该文件。
GETLENGTH	获取BLOB、CLOB和BFILE数据类型的长度。
SUBSTR	该函数用于返回 LOB 中从指定位置开始的部分内容，从指定的偏移量开始。
INSTR	该函数用来返回LOB中指定模式第n次出现的匹配位置，从指定的偏移量开始。

FILEEXISTS

语法格式

DBMS_LOB.FILEEXISTS (
   file_loc     IN    BFILE)
  RETURN INTEGER;

程序使用的宏扩展：

pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);

参数说明

file_loc

BFILE类型对象。

返回值

0：物理文件不存在。
1：物理文件存在。

异常情况

异常	描述
NOEXIST_DIRECTORY	目录不存在。
NOPRIV_DIRECTORY	没有此目录的权限。
NOPRIV_DIRECTORY	打开文件后，目录已失效。

示例

1、使用数据库安装用户（此处以vastbase为例）在用户目录下创建文件夹和文件。

mkdir ~/bfiletest
vi /home/vastbase/bfile1.txt

在新建文件中输入以下内容，按wq保存并退出。

this is a bfile test file!

2、使用vsql连接数据库后，创建BFILE文件目录。

create directory d_bfile as '/home/vastbase/bfiletest';

3、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

SET SERVEROUTPUT ON;

4、执行如下匿名块，其中调用了DBMS_LOB.fileexists子程序，用于判断BFILE文件是否存在。

文件存在：

declare
l_blob blob;
l_bfile BFILE;
begin
DBMS_LOB.createtemporary (lob_loc => l_blob,
cache => TRUE);
l_bfile := bfilename('d_bfile', 'bfile1.txt');
if DBMS_LOB.FILEEXISTS( l_bfile ) = 1 then
dbms_output.put_line( 'Exists!');
else
dbms_output.put_line( 'Not Exists!');
end if;
dbms_output.put_line('length='||dbms_lob.getlength (l_bfile));
dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
dbms_lob.fileclose(l_bfile);
dbms_output.put_line( 'OK');
end;
/

匿名块返回结果为如下：

Exists!
length=27
OK
ANONYMOUS BLOCK EXECUTE

文件不存在：

declare
l_blob blob;
l_bfile BFILE;
begin
DBMS_LOB.createtemporary (lob_loc => l_blob,
cache => TRUE);
l_bfile := bfilename('d_bfile', 'bfile3.txt');
if DBMS_LOB.FILEEXISTS( l_bfile ) = 1 then
dbms_output.put_line( 'Exists!');
else
dbms_output.put_line( 'Not Exists!');
end if;
end;
/

匿名块返回结果为如下：

Not Exists!
ANONYMOUS BLOCK EXECUTE

GETLENGTH

语法格式

BLOB对象类型：由于存储的内容即为二进制数据，直接返回二进制内容的字节数即可，函数原型为：

DBMS_LOB.GETLENGTH (
   lob_loc    IN  BLOB) 
  RETURN INTEGER;

CLOB对象类型：该对象存储的数据格式为字符数据，返回值为字符数。函数原型为：

DBMS_LOB.GETLENGTH (
lob_loc    IN  CLOB) 
RETURN INTEGER;

BFILE对象类型：函数返回bfile数据指向的操作系统物理文件的长度，以字节为单位，bfile文件的长度包含了文件结尾符（eof）。函数原型为：

DBMS_LOB.GETLENGTH (
 file_loc   IN  BFILE) 
 RETURN INT8;

参数说明

file_loc：将要返回其长度的文件位置。

示例1：

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

create table testclob (data clob);
insert into testclob values('中文测试');

3、查询数据长度。

select dbms_lob. getlength (data) from testclob;

返回结果为：

getlength
-----------
         4
(1 row)

示例2：

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建测试表并插入数据。

create table testblob (data blob);
insert into testblob values(utl_raw.cast_to_raw('中文测试'));

3、查询数据长度。

select dbms_lob. getlength (data) from testblob;

返回结果为：

getlength
-----------
        12
(1 row)

SUBSTR

语法格式

DBMS_LOB.SUBSTR (
   lob_loc    IN   BLOB,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN RAW;
  
DBMS_LOB.SUBSTR (
   lob_loc    IN   CLOB   CHARACTER SET ANY_CS,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
  
DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   amount     IN   INTEGER := 32767,
   offset     IN   INTEGER := 1)
  RETURN RAW;

参数说明

lob_loc

LOB的locator。
file_loc

FILE的locator。
amount

要读取的BLOB的字节数或CLOB的字符数。默认值为32767。
offset

LOB从1开始的字节数（BLOB）或字符数（CLOB）偏移量。默认值为1。

返回值

传入BLOB或BFILE类型时返回RAW类型。
传入CLOB类型时返回VARCHAR2类型。
当任意传入值为NULL，或amount<1、amount>32767、offset<1、offset>LOBMAXSIZE时，返回NULL。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、直接调用SUBSTR函数。

select dbms_lob.substr('ABCD'::clob);
select dbms_lob.substr('ABCD');
select dbms_lob.substr('this is lob function test',1,5) as a1;
select dbms_lob.substr('this is lob function test',length('this is lob function test'),1) as a2;

返回结果为：

substr
--------
 ABCD
(1 row)

substr
--------
 ABCD
(1 row)

 a1
----

(1 row)

            a2
---------------------------
 this is lob function test
(1 row)

INSTR

语法格式

DBMS_LOB.INSTR (
   lob_loc    IN   BLOB,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;
  
DBMS_LOB.INSTR (
   lob_loc    IN   CLOB      CHARACTER SET ANY_CS,
   pattern    IN   VARCHAR2  CHARACTER SET lob_loc%CHARSET,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;
  
DBMS_LOB.INSTR (
   file_loc   IN   BFILE,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

参数说明

lob_loc

待查找的LOB对象。
file_loc

待查找的BFILE类型LOB对象。
pattern

以字节为单位或以字符为单位的模式，用于匹配大对象中的内容。如果lob_loc代表BLOB类型大对象或file_loc代表BFILE类型大对象，那么模式必须是RAW类型。如果lob_loc代表CLOB类型大对象，那么模式必须是VARCHAR2类型。
offset

参数lob_loc或file_loc代表的大对象中搜索模式时的开始位置。第一个字节或字符是位置1。默认值为1。
nth

由给定的偏移量指定起始位置，开始搜索指定模式第n次出现时的位置。默认值为1。

返回值

匹配模式的起始偏移量，以字节或字符为单位。如果找不到模式，则返回0。

以下几种情况下返回NULL：

任何一个或多个IN参数为NULL或无效。
offset < 1 或者 offset > LOBMAXSIZE。
nth < 1 或者 nth > LOBMAXSIZE

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、直接调用instr函数。

select dbms_lob.instr('blob test'::clob,'test',1,1);

返回结果为：

instr
-------
     6
(1 row)

存储过程

表3 DBMS_LOB支持的存储过程

存储过程	描述
OPEN	根据传入的参数打开对应的LOB对象。
FILEOPEN	打开一个BFILE文件。
READ	读取 LOB 的一部分，并从 LOB 开头的绝对偏移量开始，向缓冲区参数返回指定的数量。支持读取BLOB、CLOB、BFILE对象类型。
CLOSE	关闭一个之前打开的LOB或BFILE对象。
FILECLOSE	关闭一个BFILE文件。
WRITE	从BOLB（CLOB）开头的指定偏移量offset开始，将指定数量amount的数据写入BLOB（CLOB）中，数据从缓冲区写入。如果从BLOB（CLOB）指定偏移量开始有数据存在，WRITE函数会从偏移位置开始覆盖指定长度amount的数据。
APPEND	将源BLOB（CLOB）数据追加到目标BLOB（CLOB）数据后。
WRITEAPPEND	将指定数据添加到一个大对象的末尾。
ERASE	用于擦除整个或部分的内部LOB。
COPY	根据传入的参数从指定位置开始将源LOB复制到目标LOB。
LOADFROMFILE	加载BFILE类型的数据到内部LOB中。
LOADBLOBFROMFILE	从BFILE对象加载数据到BLOB对象。
CREATETEMPORARY	用来在临时表空间中创建临时BLOB或CLOB。
FREETEMPORARY	释放临时表空间中的临时BLOB或CLOB。
CONVERTTOBLOB	将CLOB从指定偏移开始的amount个字符转换到BLOB中，并返回转换后的偏移量。

OPEN

语法格式

DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY BLOB,
   open_mode IN            BINARY_INTEGER);
   
DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   open_mode IN            BINARY_INTEGER);
 
DBMS_LOB.OPEN (
   file_loc  IN OUT NOCOPY BFILE,
   open_mode IN            BINARY_INTEGER := file_readonly);

参数说明

lob_loc

LOB类型的对象名。
open_mode

打开模式。对于BLOB，CLOB对象，可以选择LOB_READONLY或者LOB_READWRITE。对于BFILE类型，只能是FILE_READONLY。
- LOB_READONLY：只读。
- LOB_READWRITE：可读写。
- FILE_READONLY：只读。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建含BLOB、CLOB类型字段的表。

create table testtable(id int,c_lob clob,b_lob blob);
insert into testtable values(1,'clob测试',utl_raw.cast_to_raw('blob'));

3、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

4、调用OPEN存储过程。

declare 
  v_clob clob;
  v_clob2 clob;
  len int;
begin
  select c_lob into v_clob from testtable where id=1;
  dbms_lob.open(v_clob,dbms_lob.lob_readwrite);
  dbms_output.put_line('clob1:'||v_clob);
  dbms_lob.close(v_clob);
end;
/

返回结果为：

clob1:clob测试
ANONYMOUS BLOCK EXECUTE

FILEOPEN

语法格式

DBMS_LOB.FILEOPEN (
file_loc   IN OUT BFILE, 
open_mode  IN   BINARY_INTEGER );

参数说明

file_loc

要打开的文件定位。
open_mode

文件访问模式。

示例

1、在操作系统环境下创建文件，输入数据并保存。

vi /home/vastbase/bfile.data

文件内容为：

张三
李四

2、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

3、在数据库中执行如下命令创建目录和表。

create directory d2_bfile as '/home/vastbase';
create table testbfile1(id number,bfile_name bfile);

4、调用bfilename函数构造bfile数据并插入到表中。

insert into testbfile1 values(1,bfilename('d2_bfile', 'bfile.data'));

5、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

set serveroutput on;

6、创建自定义函数：

create or replace function f_read_bfile() returns void as $$
declare
buff raw;
amount int := 1;
offset_ int :=1; --offset是关键字，不能用作参数名，此处写作offset_
lob_loc bfile;
filesize int;
begin
select bfile_name into lob_loc from testbfile1 where id=1;
--打开BFILE文件
dbms_lob.fileopen(lob_loc,0);
--获取文件大小
filesize := dbms_lob.getlength(lob_loc);
raise notice 'amount:%',amount;
--读取文件全部内容
amount = filesize;
dbms_lob.read(lob_loc,amount,offset_,buff);  
dbms_output.put_line('file data:');
dbms_output.put_line(utl_raw.cast_to_varchar2(buff));
--关闭bfile文件
dbms_lob.fileclose(lob_loc);
return;
end;
$$ language plpgsql;

7、调用自定义函数。

select f_read_bfile();

返回结果为：

file data:
张三
李四

 f_read_bfile
--------------

(1 row)

READ

语法格式

DBMS_LOB.READ (
   lob_loc   IN             BLOB,
   amount    IN OUT  NOCOPY INTEGER,
   offset    IN             INTEGER,
   buffer    OUT            RAW);

DBMS_LOB.READ (
   lob_loc   IN             CLOB CHARACTER SET ANY_CS,
   amount    IN OUT  NOCOPY INTEGER,
   offset    IN             INTEGER,
   buffer    OUT            VARCHAR2); 

DBMS_LOB.READ (
   file_loc   IN             BFILE,
   amount    IN OUT   NOCOPY INTEGER,
   offset    IN              INTEGER,
   buffer    OUT             RAW);

参数说明

lob_loc

要读取的 LOB 的定位器。
file_loc

要读取的LOB文件的定位器。
amount

读取文件的长度。为了防止打开的文件过大，单次读取的内容超过机器缓冲区导致系统崩溃，规定单次读取的字节数不能大于32767字节，即amount为大于0小于等于32767的正整数。
offset

用于多次读取文件时设置读取的起始位置，如果offset参数指向bfile文件结尾或超出文件结尾，则将amount参数设置为0，并抛出异常。
buffer

读取操作的输出缓冲区。

示例

读取BFILE请参考DBMS_LOB.FILEOPEN的示例。以下是一个DBMS_LOB.READ读取BLOB的示例：

当前Vastbase支持两种LOB的解析方式，分为直接存储数据或使用定位器存储。此特性受参数enable_oralob_type控制。

此参数只能在实例初始化完成后，第一次启动前在配置文件中进行修改。

如果初始化实例时指定了参数--enable-oralob-type，则此参数必须设置为on，否则无需设置或应设为off。

set serveroutput on;  --使信息从存储过程传输回应用程序并输出在屏幕上
declare
lob blob:=utl_raw.cast_to_raw('123456');
amount int := 4;
off_set int := 3;
buf varchar2(100);
begin
dbms_lob.read(lob,amount,off_set,buf);
dbms_output.put_line(buf);
end;
/

输出读取操作的缓冲区：

33343536
ANONYMOUS BLOCK EXECUTE

CLOSE

语法格式

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY BLOB); 

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); 

DBMS_LOB.CLOSE (
   file_loc   IN OUT NOCOPY BFILE);

参数说明

lob_loc

BLOB或CLOB类型的大对象。
FILE_LOC

BFILE类型对象。

示例

示例可参见DBMS_LOB.open子程序的示例。

FILECLOSE

语法格式

DBMS_LOB.FILECLOSE (
    file_loc IN OUT BFILE);

参数说明

file_loc：要关闭的文件。

示例

参见dbms_lob.fileopen的示例。

WRITE

语法格式

DBMS_LOB.WRITE(
    lob_loc IN OUT NOCOPY BLOB,
    amount IN INTEGER,
    offset IN INTEGER,
    buffer IN RAW);

DBMS_LOB.WRITE(
    lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
    amount IN INTEGER,
    offset IN INTEGER,
    buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);

参数说明

lob_loc

LOB 的locator。
amount

要写入的BLOB字节数或者CLOB的字符数。
offset

写操作中从BLOB开头的字节数偏移量，或者CLOB开头的字符数偏移量，起始处下标为1。
buffer

写入操作的输入缓冲区。

返回值

传入BLOB时返回RAW类型。
传入CLOB时返回TEXT类型。

使用DBMS_LOB.WRITE函数时，以下情况将出现报错：

传入lob_loc、amount、offset参数的任何一个为NULL，超出范围或无效时；

传入amount<1、amount>32767、offset<1、offset>LOBMAXSIZE时。

示例1： 入参lob_loc为BLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.WRITE函数，根据指定偏移量对输入的BLOB内容进行覆盖。

declare
b1 blob :=utl_raw.cast_to_raw('测试ABC123');
amount INTEGER :=3;
off_set INTEGER :=4;
b2 blob :=utl_raw.cast_to_raw('abc');
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(utl_raw.cast_to_varchar2(b1));
end;
/

返回结果如下：

测abcABC123
ANONYMOUS BLOCK EXECUTE

示例2： 入参lob_loc为CLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.WRITE函数，根据指定偏移量对输入的CLOB内容进行覆盖。

declare
b1 clob :='测试ABC123';
amount INTEGER :=3;
off_set INTEGER :=4;
b2 clob :='abc';
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(b1);
end;
/

返回结果如下：

测试Aabc23
ANONYMOUS BLOCK EXECUTE

APPEND

语法格式

DBMS_LOB.APPEND(
    dest_lob IN OUT NOCOPY BLOB,
    src_lob IN BLOB);

DBMS_LOB.APPEND(
    dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
    src_lob IN CLOB CHARACTER SET dest lob%CHARSET);

参数说明

dest_lob

要向其添加数据的目标BLOB（CLOB）的locator。
src_lob

要从中读取数据的源BLOB（CLOB）的locator。

返回值

传入BLOB时返回RAW类型。
传入CLOB时返回TEXT类型。

传入的dest_lob或者src_lob为空时，报错。

示例1： 入参lob_loc为BLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.APPEND函数，将源BLOB追加到目标BLOB后。

declare
b1 blob :=utl_raw.cast_to_raw('测试ABC123');
b2 blob :=utl_raw.cast_to_raw('abc');
BEGIN
dbms_lob.append(b1,b2);
dbms_output.put_line(utl_raw.cast_to_varchar2(b1));
end;
/

返回结果如下：

测试ABC123abc
ANONYMOUS BLOCK EXECUTE

示例2： 入参lob_loc为CLOB。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用DBMS_LOB.APPEND函数，将源CLOB追加到目标CLOB后。

declare
b1 clob :='测试ABC123';
amount INTEGER :=3;
off_set INTEGER :=4;
b2 clob :='abc';
BEGIN
dbms_lob.write(b1,amount,off_set,b2);
dbms_output.put_line(b1);
end;
/

返回结果如下：

测试Aabc23
ANONYMOUS BLOCK EXECUTE

WRITEAPPEND

语法格式

DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY BLOB, 
   amount  IN            INTEGER, 
   buffer  IN            RAW); 
   
DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, 
   amount  IN            INTEGER, 
   buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET);

参数说明

lob_loc

LOB类型的对象名。
amount

追加的长度。
buffer

待追加的内容。

示例： 调用writeappend函数将数据添加对lob的末尾，lob_loc类型为clob。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建包含clob类型的测试表。

create table bak_dbms_lob(
bak_id number(4),
bak_comment clob
);

3、插入测试数据。

insert into bak_dbms_lob(bak_id,bak_comment) values(1,'a');
insert into bak_dbms_lob(bak_id,bak_comment) values(2,'ab');
insert into bak_dbms_lob(bak_id,bak_comment) values(3,'abcdefgccccccc');
insert into bak_dbms_lob(bak_id,bak_comment) values(4,'a bcdefg');

4、查看测试表内容。

select * from bak_dbms_lob;

返回结果为：

bak_id |  bak_comment
--------+----------------
      1 | a
      2 | ab
      3 | abcdefgccccccc
      4 | a bcdefg
(4 rows)

5、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

6、调用writeappend函数。

declare
lob_loc clob;
amount integer:= 18;
buffer_text varchar2(20) := 'added text to clob';
begin
select bak_comment into lob_loc from bak_dbms_lob where bak_id = 2;
dbms_lob.writeappend(lob_loc,amount,buffer_text);
dbms_output.put_line('添加后的字段为：' || lob_loc);
end;
/

返回结果为：

添加后的字段为：abadded text to clob
ANONYMOUS BLOCK EXECUTE

ERASE

功能描述

在LOB对象指针中，从offset开始及后续的amount个字符置为空格字符，即擦除内容。

语法格式

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   BLOB,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   CLOB CHARACTER SET ANY_CS,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

参数说明

lob_loc

要删除的LOB对象指针。
amount

要擦除的字节数（BLOB）或字符数（CLOB）。
offset

擦除点距离LOB开头的绝对偏移量（初始值为1），以字节（BLOB）或字符（CLOB）为单位。

使用说明

擦除LOB的中间数据时，分别为BLOB或CLOB写入零字节填充符或空格。
如果在擦除指定的数字之前已到LOB值的末尾，则擦除的实际字节数或字符数可以与amount参数中指定的数字不同。实际删除的字符数或字节数将在amount参数中返回。

只擦除LOB的部分数据时，LOB的长度不会减小。

异常情况

异常	描述
VALUE_ERROR	任何输入参数均为NULL。
INVALID_ARGVAL	发生以下某种情况: offset 小于 1 或者 offset 大于 LOBMAXSIZE。 amount 小于 1 或者 amount 大于 LOBMAXSIZE
QUERY_WRITE	无法在查询中执行LOB写入。
BUFFERING_ENABLED	如果在LOB上启用了缓冲，则无法在启用LOB缓冲的情况下执行操作。

示例

1、设置serveroutput 为on（允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上）。

SET SERVEROUTPUT ON;

2、执行如下匿名块并调用，其中调用了DBMS_LOB.erase子程序，擦除内容。

declare
l_c clob := 'Hello World!';
l_a number := 9;
begin
dbms_lob.erase( l_c, l_a, 6 );
dbms_output.put_line( 'The clob now = *' || l_c || '*' );
dbms_output.put_line( 'The amount that was erased was: ' || l_a );
end;
/

返回结果为如下：

The clob now = *Hello       *
The amount that was erased was: 7
ANONYMOUS BLOCK EXECUTE

COPY

语法格式

DBMS_LOB.COPY (
  dest_lob    IN OUT NOCOPY BLOB,
  src_lob     IN            BLOB,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

DBMS_LOB.COPY ( 
  dest_lob    IN OUT NOCOPY CLOB  CHARACTER SET ANY_CS,
  src_lob     IN            CLOB  CHARACTER SET dest_lob%CHARSET,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

参数说明

dest_lob

目标LOB对象。
src_lob

源LOB对象。
amount

复制的长度。
dest_offset

目标LOB中的偏移量。
src_offset

源LOB中的偏移量。

示例： 存储过程调用copy函数，输入参数中的dest_lob为clob类型。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、创建含blob类型字段的表并插入数据。

create table testtable_01(id int,c_lob clob);
insert into testtable_01 values(1,'blobtest1测试测试');
insert into testtable_01 values(2,'blobtest2测试测试');

3、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

4、调用COPY函数。

DECLARE
vdest_lob CLOB;
vsrc_lob CLOB;
amount NUMBER;
dest_offset NUMBER;
src_offset NUMBER;
BEGIN
SELECT c_lob INTO vdest_lob FROM testtable_01 WHERE id = 1 ;
SELECT c_lob INTO vsrc_lob FROM testtable_01 WHERE id = 2 ;
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;
/

返回结果为：

拷贝结果为: blobtest1测试测试blobtest2测试测试
ANONYMOUS BLOCK EXECUTE

LOADFROMFILE

语法格式

DBMS_LOB.LOADFROMFILE (
   dest_lob    IN OUT        BLOB, 
   src_file    IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN            INTEGER  := 1, 
   src_offset  IN            INTEGER  := 1);

DBMS_LOB.LOADFROMFILE (
   dest_lob    IN OUT        CLOB, 
   src_file    IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN            INTEGER  := 1, 
   src_offset  IN            INTEGER  := 1);

注意事项

若使用此存储过程加载BFILE类型数据到CLOB中，为加载到有效内容，请确保BFILE的字符集和数据库后端字符集一致。

参数说明

dest_lob

目标BLOB/CLOB对象。
src_file

源BFILE对象。
amount

要从BFILE对象加载到BLOB/CLOB对象的字节数。
amount

开始写入BLOB/CLOB对象的偏移。
src_offset

从BFILE对象开始读取的偏移。

示例

1、创建BFILE类型测试文件（如下语句以数据库安装用户vastbase为例），输入一串内容并保存退出。

mkdir /tmp/file
vi /tmp/file/bfiletest1.txt
asdfgh12345

2、使用vsql连接数据库后，创建测试表。

create table lob_table(key_value int,c_lob nclob);
insert into lob_table values(42,'一二三四五');

3、创建BFILE文件目录。

CREATE OR REPLACE DIRECTORY  dir  as '/tmp/file';

4、创建存储过程，其中调用DBMS_LOB.loadfromfile加载BFILE类型的数据到内部LOB中。

CREATE OR REPLACE PROCEDURE Example_l2f IS
lobd NCLOB;
fils BFILE := BFILENAME('dir','bfiletest1.txt');
amt INTEGER := 2;
BEGIN
SELECT c_lob INTO lobd FROM lob_table WHERE key_value = 42 FOR UPDATE;
--amt := dbms_lob.getlength(fils);
dbms_lob.fileopen(fils, dbms_lob.file_readonly);
dbms_lob.loadfromfile(lobd, fils, amt,2,2);
dbms_output.put_line('lob is :'||lobd);  
COMMIT;
dbms_lob.fileclose(fils);
END;
/

5、调用存储过程。

set serveroutput on;
call Example_l2f();

返回结果如下：

lob is :一sd▒三四五
 example_l2f
-------------

(1 row)

LOADBLOBFROMFILE

语法格式

DBMS_LOB.LOADBLOBFROMFILE (
   dest_lob    IN OUT        BLOB, 
   src_bfile   IN            BFILE, 
   amount      IN            INTEGER, 
   dest_offset IN OUT        INTEGER, 
   src_offset  IN OUT        INTEGER);

参数说明

dest_lob

目标 BLOB 对象。
src_bfile

源BFILE对象。
amount

要从BFILE对象加载到BLOB对象的字节数。
dest_offset

开始写入BLOB对象的偏移。
src_offset

从BFILE对象开始读取的偏移。

CREATETEMPORARY

语法格式

DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY BLOB,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := DBMS_LOB.SESSION);
  
DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := 10);

参数说明

lob_loc

LOB类型的对象名。
cache

是否将LOB读取到缓冲区。
dur

指定何时清除临时LOB。
- 10/ SESSION：会话结束时
- 12/ CALL：调用结束时。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用createtemporary存储过程，创建临时clob。

DECLARE
v_clob CLOB;
v_amount INT;
v_offset INT;
v_char VARCHAR2(100);
BEGIN
v_char := 'Chinese中国人';
v_offset := 1;
v_amount := 8;
dbms_lob.createtemporary(v_clob,TRUE,12);
FOR i IN 1..2 LOOP
dbms_lob.writeappend(v_clob,v_amount,v_char);
dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob));
END LOOP;
END;
/

返回结果为：

Chinese中 字符长度：8
Chinese中Chinese中 字符长度：16
ANONYMOUS BLOCK EXECUTE

FREETEMPORARY

语法格式

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY BLOB); 

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY CLOB CHARACTER SET ANY_CS);

参数说明

lob_loc：LOB类型的对象名。

示例

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;

3、调用freetemporary存储过程，创建临时clob后释放。

DECLARE
v_clob CLOB;
v_amount INT;
v_offset INT;
v_char VARCHAR2(100);
BEGIN
v_char := 'Chinese中国人';
v_offset := 1;
v_amount := 8;
dbms_lob.createtemporary(v_clob,TRUE,12);
dbms_lob.writeappend(v_clob,v_amount,v_char);
dbms_output.put_line(v_clob||' 字符长度：'||dbms_lob.getlength(v_clob));
dbms_lob.freetemporary(v_clob);
dbms_output.put_line(' 释放后再输出：'||v_clob);
END;
/

返回结果为：

Chinese中 字符长度：8
释放后再输出：
ANONYMOUS BLOCK EXECUTE

CONVERTTOBLOB

语法格式

DBMS_LOB.CONVERTTOBOLB(
dest_blob  IN OUT  NOCOPY  BLOB,
src_clob   IN      CLOB CHARACTER SET ANY_CS,
amount     IN      INTEGER,
dest_offset IN OUT INTEGER,
src_offset  IN OUT INTEGER,
blob_csid   IN     NUMBER,
lang_context IN OUT INTEGER,
warning     OUT   INTEGER);

参数说明

dest_lob IN

表示一个BLOB类型的大对象。
dest_lob OUT

表示转换后的BLOB类型的大对象。
src_clob

表示一个CLOB类型的大对象。
amount

表示在参数src_clob所指定的大对象中要转换的字符数量。
dest_offset IN

BLOB类型的目标大对象中字节的位置。
dest_offset OUT

写操作完成后，在BLOB类型大对象中字节的位置。
src_offset IN

转换操作中，CLOB类型大对象开始的位置。
src_offset OUT

在转换操作完成后，CLOB类型大对象中字符的位置。
blob_csid

BLOB类型大对象中的字符集ID。

0并不是真实存在的字符集ID，为0时表示使用源CLOB的字符集。暂不支持其它取值。
lang_context IN

在转换操作中使用的语言环境。默认值为0。
lang_context OUT

转换后的语言环境。
warning

如果转换成功，则返回0；如果遇到不可转换的字符，则返回1。

示例

DBMS_LOB.CONVERTTOBOLB是一个internal语言的函数，此处示例展示了DBMS_LOB.CONVERTTOBOLB函数结合其他函数与常量的使用效果。

1、使用vsql工具连接至客户端。

vsql -d vastbase -p 5432 -r

2、允许将dbms_output.put_line的输出信息输出至vsql的命令界面的屏幕上。

set serveroutput on;
set behavior_compat_options='proc_outparam_override';  --开启此选项后，存储过程的return和out/inout可以同时返回

3、执行以下匿名块。

DECLARE
v_temp1 varchar2(32767) := 'absd';
V_temp1_blob BLOB;
V_temp2 varchar2(32767) := 'efghi';
V_temp2_blob BLOB;
v_in integer := 1;
v_out integer := 1;
v_lang integer := 0;
v_warning integer := 0;
BEGIN
DBMS_LOB.createTemporary (V_temp1_blob, TRUE);
DBMS_LOB.convertToBlob(V_temp1_blob, V_temp1, DBMS_LOB.LOBMAXSIZE, v_in, v_out, DBMS_LOB.DEFAULT_CSID, v_lang, v_warning);
dbms_output.put_line('V_temp1_blob: ' || dbms_lob.getlength(V_temp1_blob));

DBMS_LOB.createTemporary (V_temp2_blob, TRUE);
DBMS_LOB.convertToBlob(V_temp2_blob, V_temp2, DBMS_LOB.LOBMAXSIZE, v_in, v_out, DBMS_LOB.DEFAULT_CSID, v_lang, v_warning);
dbms_output.put_line('V_temp2_blob: ' || dbms_lob.getlength(V_temp2_blob));
END;
/

返回结果为：

V_temp1_blob: 4
V_temp2_blob: 0
ANONYMOUS BLOCK EXECUTE

VastbaseG100

DBMS_LOB

功能描述

注意事项

支持范围

常量

LOBMAXSIZE

DEFAULT_CSID

DEFAULT_LANG_CTX

函数

FILEEXISTS

GETLENGTH

SUBSTR

INSTR

存储过程

OPEN

FILEOPEN

READ

CLOSE

FILECLOSE

WRITE

APPEND

WRITEAPPEND

ERASE

COPY

LOADFROMFILE

LOADBLOBFROMFILE

CREATETEMPORARY

FREETEMPORARY

CONVERTTOBLOB

弹框头部