DBMS_LOB

功能描述

DBMS_LOB用于提供一组对blob（二进制数据类型）、clob（字符数据类型）和bfile数据类型（存储数据库外的大型对象，指向操作系统物理文件）操作的函数或过程。

注意事项

该功能仅在数据库兼容模式为Oracle时能够使用（即创建DB时DBCOMPATIBILITY='A'），在其他数据库兼容模式下不能使用该特性。
write函数和append函数仅Vastbase G100 V2.2 Build 10(Patch No.12)及以后补丁版本支持此功能。

子程序

该内置包支持以下子程序：

子程序	描述
GETLENGTH	获取blob、clob和bfile数据类型的长度。
FILEOPEN	打开一个bfile文件。
READ	读取 LOB 的一部分，并从 LOB 开头的绝对偏移量开始，向缓冲区参数返回指定的数量。支持读取BLOB、CLOB、BFILE对象类型。
FILECLOSE	关闭一个bfile文件。
OPEN	根据传入的参数打开对应的LOB对象。
COPY	根据传入的参数从指定位置开始将源LOB复制到目标LOB。
WRITEAPPEND	将指定数据添加到一个大对象的末尾。
CREATETEMPORARY	用来在临时表空间中创建临时BLOB或CLOB。
FREETEMPORARY	释放临时表空间中的临时BLOB或CLOB。
INSTR	该函数用来返回LOB中指定模式第n次出现的匹配位置，从指定的偏移量开始。
WRITE	从BOLB（CLOB）开头的指定偏移量offset开始，将指定数量amount的数据写入BLOB（CLOB）中，数据从缓冲区写入。如果从BLOB（CLOB）指定偏移量开始有数据存在，WRITE函数会从偏移位置开始覆盖指定长度amount的数据。
APPEND	将源BLOB（CLOB）数据追加到目标BLOB（CLOB）数据后。

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、创建测试表并插入数据。

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

2、查询数据长度。

select dbms_lob. getlength (data) from testclob;

返回结果为：

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

示例2：

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

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

2、查询数据长度。

select dbms_lob. getlength (data) from testblob;

返回结果为：

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

FILEOPEN

语法格式

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

参数说明

file_loc：要打开的文件定位。
open_mode：文件访问模式。

READ

语法格式

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

参数说明

file_loc：lob要检查的文件。
amount：读取文件的长度。为了防止打开的文件过大，单次读取的内容超过机器缓冲区导致系统崩溃，规定单次读取的字节数不能大于32767字节，即amount为大于0小于等于32767的正整数。
offset：用于多次读取文件时设置读取的起始位置，如果offset参数指向bfile文件结尾或超出文件结尾，则将amount参数设置为0，并抛出异常。
buffer：读取操作的输出缓冲区。

示例

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

仅Vastbase G100 V2.2 Build 10(Patch No.17)及以后版本支持CLOB和BLOB。

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

FILECLOSE

语法格式

DBMS_LOB.FILECLOSE (
    file_loc IN OUT BFILE);

参数说明

file_loc：要关闭的文件。

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、创建含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'));

2、调用open存储过程。

set serveroutput on;
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

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、创建含blob类型字段的表并插入数据。

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

2、调用COPY函数。

set serveroutput on;
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

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、创建含测试表。

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

2、插入测试数据。

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');
select * from bak_dbms_lob;

返回结果为：

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

3、调用调用writeappend函数。

set serveroutput on;
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

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：调用结束时）。

示例

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

set serveroutput on;
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类型的对象名。

示例

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

set serveroutput on;
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

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

示例

调用instr函数。

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

返回结果为：

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

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、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

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

set serveroutput on;

4、调用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、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

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

set serveroutput on;

4、调用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、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

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

set serveroutput on;

4、调用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、创建并切换至兼容模式为Oracle的数据库db_oracle下。

CREATE DATABASE db_oracle dbcompatibility='A';    
\c db_oracle

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

set serveroutput on;

4、调用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

示例

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

 vi /home/vastbase/bfile.data
 cat /home/vastbase/bfile.data

文件内容为：

张三
李四

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

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

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

insert into testbfile1 values(1,bfilename('d_bfile', 'bfile.data'));
set serveroutput on;

4、创建函数。

create or replace function f_read_bfile() returns void as $$
declare
buff raw;
amount int := 0 ;
offset int :=0;
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,0,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;

5、调用函数。

select f_read_bfile();

返回结果为：

file data:
张三
李四

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

(1 row)

VastbaseG100

DBMS_LOB

功能描述

注意事项

子程序

GETLENGTH

FILEOPEN

READ

FILECLOSE

OPEN存储过程

COPY存储过程

WRITEAPPEND存储过程

CREATETEMPORARY存储过程

FREETEMPORARY存储过程

INSTR函数

WRITE函数

APPEND函数

示例

弹框头部