如何使用PolarDBPostgreSQL版的DBMS_LOB系统包_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

DBMS_LOB包提供了在BLOB、CLOB和NCLOB上运行的子程序，可以使用DBMS_LOB访问和操作LOB或完整LOB的特定部分。

DBMS_LOB变量总览

变量	数据类型	变量值	说明
CALL	BINARY_INTEGER	12	创建具有调用持续时间的临时LOB。
DEFAULT_CSID	INTEGER	0	默认的字符集ID。
DEFAULT_LANG_CTX	INTEGER	0	默认的语言上下文。
LOB_READONLY	BINARY_INTEGER	0	以只读方式打开指定的LOB。
LOB_READWRITE	BINARY_INTEGER	1	打开指定的LOB读/写。
BLOBMAXSIZE	INTEGER	16777216 (16 MB)	设置BLOB的最大值（以字节为单位）。
CLOBMAXSIZE	INTEGER	4194304 (4 MB)	设置CLOB的最大值（以字节为单位）。
NO_WARNING	INTEGER	0	表示成功，无警告信息。
SESSION	BINARY_INTEGER	10	创建具有会话持续时间的临时LOB。说明在TimesTen中，LOB持续时间不能超过事务结束。当事务结束时相应的定位器失效时，临时LOB内容将被销毁。
TRANSACTION	BINARY_INTEGER	11	创建具有事务持续时间的临时LOB。
WARN_INCONVERTIBLE_CHAR	INTEGER	1	由转换函数用来指示存在不可转换的字符。

DBMS_LOB子程序总览

子程序	说明
COPY PROCEDURE	将全部或部分源LOB复制到目标LOB。
ERASE PROCEDURE	删除全部或部分LOB。
SUBSTR FUNCTION	返回从指定偏移量开始的LOB值的一部分。
GETLENGTH FUNCTION	返回LOB值的长度。对于BLOB，以字节为单位。对于CLOB，以字符为单位。
INSTR FUNCTION	返回LOB中模式第N次出现的匹配位置。
COMPARE FUNCTION	比较两个整个LOB或两个LOB的一部分。
APPEND PROCEDURE	将源LOB的内容附加到目标LOB。
READ PROCEDURE	从LOB中指定偏移量处开始读取数据。
WRITE PROCEDURE	从指定偏移量将数据写入LOB。
WRITEAPPEND PROCEDURE	将缓冲区附加到LOB的末尾。
TRIM PROCEDURE	将LOB值修改为指定长度。
GET_STORAGE_LIMIT FUNCTION	返回指定LOB的LOB类型的存储限制。

COPY

从指定位置开始将源LOB复制到目标LOB。

语法

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

DBMS_LOB.COPY (
  dest_lob        IN        OUTTEXT,
  src_lob         IN        TEXT,
  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的偏移量。

示例

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='11223344'::raw::BLOB;
  DBMS_LOB.COPY(dest_lob,'AABBCCDDEEFF'::raw::BLOB,6,6,1);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x3131323233414142424343

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='11223344';
  DBMS_LOB.COPY(dest_lob,'AABBCCDDEEFF',6,6,1);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
11223AABBCC

ERASE

删除LOB数据中指定位置的部分数据的过程。

语法

DBMS_LOB.ERASE (
    lob_loIN            OUT         BYTEA,
    amount              IN OUT      INTEGER, 
    p_offset            IN          INTEGER := 1);

DBMS_LOB.ERASE (
    lob_loc             IN OUT      TEXT,
    amount              IN OUT      INTEGER, 
    p_offset            IN          INTEGER := 1);

参数说明

参数	说明
lob_loc	目标LOB。
amount	删除长度。
p_offset	偏移量。

示例

DECLARE
  dest_lob BLOB;
  amount integer;
BEGIN
  dest_lob:='E6B58BE8AF95'::raw::BLOB;
  amount := 2;
  DBMS_LOB.ERASE(dest_lob,amount,3);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x4536000038424538414639

DECLARE
  dest_lob CLOB;
  amount integer;
BEGIN
  dest_lob:='E6B58BE8AF95'::CLOB;
  amount := 2;
  DBMS_LOB.ERASE(dest_lob,amount,3);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
E6  8BE8AF9

SUBSTR

返回从LOB数据中提取子字符串的函数。

语法

DBMS_LOB.SUBSTR (
    lob_loc             IN OUT      BYTEA,
    amount              IN OUT	    INTEGER := 32767, 
    p_offset            IN          INTEGER := 1)
RETURNS BYTEA;

DBMS_LOB.SUBSTR (
    lob_loc             IN OUT      TEXT,
    amount              IN OUT      INTEGER := 32767, 
    p_offset            IN          INTEGER := 1)
RETURNS TEXT;

参数说明

参数	说明
lob_loc	目标LOB。
amount	提取长度。
p_offset	偏移量。

示例

SELECT DBMS_LOB.SUBSTR('\x112233445566778899'::RAW::BLOB,2,2);
 SUBSTR 
--------
 \x2233
(1 row)

SELECT DBMS_LOB.SUBSTR('AABBCCDD'::CLOB,2,2);
 SUBSTR 
--------
 AB
(1 row)

GETLENGTH

返回指定LOB数据的长度的函数。

语法

DBMS_LOB.GETLENGTH (
    lob_loc             IN OUT 	BYTEA)
RETURNS INT4;

DBMS_LOB.GETLENGTH (
    lob_loc             IN OUT 	TEXT)
RETURNS INT4;

参数说明

参数	说明
lob_loc	目标LOB。

示例

SELECT DBMS_LOB.GETLENGTH('AABBCCDD'::RAW::BLOB);
 GETLENGTH 
-----------
         8
(1 row)

SELECT DBMS_LOB.GETLENGTH('AABBCCDD'::CLOB);
 GETLENGTH 
-----------
         8
(1 row)

INSTR

从LOB数据中查找子字符串位置的函数。

语法

DBMS_LOB.INSTR (
    lob_loc             IN          BYTEA,
    pattern             IN          BYTEA,
    p_offset            IN          INT4 := 1,
    nth                 IN          INT4 := 1)
RETURNS INTEGER;

DBMS_LOB.INSTR (
    lob_loc             IN          TEXT,
    pattern             IN          TEXT,
    p_offset            IN          INT4 := 1,
    nth                 IN          INT4 := 1)
RETURNS INTEGER;

参数说明

参数	说明
lob_loc	目标LOB。
pattern	子LOB。
p_offset	偏移量。
nth	重复次数。

示例

SELECT DBMS_LOB.INSTR('\x112233445566778899'::RAW::BLOB,'\x22'::RAW,1,1);
 INSTR 
-------
     2
(1 row)

SELECT DBMS_LOB.INSTR('112233445566778899'::CLOB,'22'::CLOB,1,1);
 INSTR 
-------
     3
(1 row)

COMPARE

比较两个大对象是否相等的函数。

语法

DBMS_LOB.COMPARE (
    lob_1           IN          BYTEA,
    lob_2           IN          BYTEA,
    amount          IN          INT4 := 2147483647,
    offset_1        IN          INTEGER := 1,
    offset_2        IN          INTEGER := 1)
RETURNS INT4;

DBMS_LOB.COMPARE (
    lob_1           IN          TEXT,
    lob_2           IN          TEXT,
    amount          IN          INT4 := 2147483647,
    offset_1        IN          INTEGER := 1,
    offset_2        IN          INTEGER := 1)
RETURNS INT4;

参数说明

参数	说明
lob_1	比较对象一。
lob_2	比较对象二。
amount	比较长度。
offset_1	LOB_1偏移量。
offset_2	LOB_2偏移量。

示例

SELECT DBMS_LOB.COMPARE('\x112233445566778899'::BLOB,'\x111133445566778899'::BLOB,1,2,2);
 COMPARE 
---------
       1
(1 row)


SELECT DBMS_LOB.COMPARE('E6'::CLOB,'E6B58BE8AF95'::CLOB,6,1,1);
 COMPARE 
---------
      -1
(1 row)

APPEND

将指定的LOB数据追加到指定的LOB数据后的过程。

语法

DBMS_LOB.APPEND (
    dest_lob            IN          BYTEA,
    src_lob             IN          BYTEA);

DBMS_LOB.APPEND (
    dest_lob            IN          TEXT,
    src_lob             IN          TEXT);

参数说明

参数	说明
dest_lob	目标LOB。
src_lob	子LOB。

示例

DECLARE
  a BLOB:='\x41414242'::RAW::BLOB;
BEGIN
  DBMS_LOB.APPEND(a,'\x'::RAW::BLOB);
  DBMS_OUTPUT.PUT_LINE(a);
END;
\x41414242

DECLARE
  a CLOB:='AABB'::CLOB;
BEGIN
  DBMS_LOB.APPEND(a,'CCDD'::CLOB);
  DBMS_OUTPUT.PUT_LINE(a);
END;
AABBCCDD

READ

从LOB数据中读取指定长度数据到缓冲区（变量中）的过程。

语法

DBMS_LOB.READ (
    lob_loc             IN          BYTEA,
    amount              IN OUT      INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         BYTEA);

DBMS_LOB.READ (
    lob_loc             IN          TEXT,
    amount              IN OUT      INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         TEXT);

参数说明

参数	说摸
dest_lob	目标LOB。
amount	写入长度。
p_offset	偏移量。
buffer	写入目标对象。

示例

DECLARE
  dest_lob BLOB;
  a raw;
  m int;
BEGIN
  m:=2;
  dest_lob:='E6B58BE8AF95'::raw::BLOB;
  DBMS_LOB.READ(dest_lob,m,4,a);
  DBMS_OUTPUT.PUT_LINE(a || ',' || m);
END;
\x3538,2

DECLARE
  dest_lob CLOB;
  a text;
  m int;
BEGIN
  m:=20;
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_LOB.READ(dest_lob,m,3,a);
  DBMS_OUTPUT.PUT_LINE(a || ',' || m);
END;
B58BE8AF95,10

WRITE

将指定数量的数据写入LOB的过程。

语法

DBMS_LOB.WRITE (
    lob_loc             IN OUT      BYTEA,
    amount              IN          INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         BYTEA);

DBMS_LOB.WRITE (
    lob_lo              IN OUT      TEXT,
    amount              IN	        INTEGER,
    p_offset            IN          INTEGER,
    buffer              OUT         TEXT);

参数说明

参数	说明
dest_lob	目标LOB。
amount	写入长度。
p_offset	偏移量。
buffer	缓冲区变量。

示例

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='\x112233445566778899'::raw::BLOB;
  DBMS_LOB.WRITE(dest_lob,2,4,'\xAABBCCDDEEFF'::raw::BLOB);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
\x112233aabb66778899

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='112233445566778899'::CLOB;
  DBMS_LOB.WRITE(dest_lob,2,4,'AABBCCDDEEFF'::CLOB);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
112AA3445566778899

WRITEAPPEND

将缓冲区数据写到LOB尾部。

语法

DBMS_LOB.WRITEAPPEND (
    lob_loc             IN OUT      BYTEA,
    amount              IN          INTEGER,
    buffer              IN          BYTEA);

DBMS_LOB.WRITEAPPEND (
    lob_loc             IN OUT 	    TEXT,
    amount              IN          INTEGER,
    buffer              IN          TEXT);

参数说明

参数	说明
lob_loc	被写入的LOB。
amount	写入长度。
buffer	缓冲区数据。

示例

DECLARE
  lob_loc BLOB:='\x112233'::RAW::BLOB;
BEGIN
  DBMS_LOB.WRITEAPPEND(lob_loc,5,'\xAABBCCDDEE'::RAW);
  DBMS_OUTPUT.PUT_LINE(lob_loc);
END;
\x112233aabbccddee

DECLARE
  lob_loc CLOB:='112233'::CLOB;
BEGIN
  DBMS_LOB.WRITEAPPEND(lob_loc,10,'AABBCCDDEE'::text);
  DBMS_OUTPUT.PUT_LINE(lob_loc);
END;
112233AABBCCDDEE

TRIM

截断LOB数据中从第一位置开始指定长度的部分数据的过程。

语法

DBMS_LOB.TRIM (
    lob_loc             IN OUT 	    BYTEA,
    newlen              IN          INTEGER);

DBMS_LOB.TRIM (
    lob_loc             IN OUT      TEXT,
    newlen              IN          INTEGER);

参数说明

参数	说明
lob_loc	目标LOB。
newlen	截取的长度。

示例

DECLARE
  dest_lob BLOB;
  newlen int;
BEGIN
  newlen:=2;
  dest_lob:='\xE6B58BE8AF95'::raw::BLOB;
  DBMS_LOB.TRIM(dest_lob,newlen);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
  \xe6b5

DECLARE
  dest_lob CLOB;
  newlen int;
BEGIN
  newlen:=2;
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_LOB.TRIM(dest_lob,newlen);
  DBMS_OUTPUT.PUT_LINE(dest_lob);
END;
  E6

GET_STORAGE_LIMIT

返回LOB数据存储大小的上限值。

语法

DBMS_LOB.GET_STORAGE_LIMIT (
    lob_loc             IN OUT 	    BYTEA);

DBMS_LOB.GET_STORAGE_LIMIT (
    lob_loc             IN OUT      TEXT);

参数说明

参数	说明
lob_loc	目标LOB。

示例

DECLARE
  dest_lob BLOB;
BEGIN
  dest_lob:='\xE6B58BE8AF95'::raw::BLOB;
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GET_STORAGE_LIMIT(dest_lob));
END;
2147483647

DECLARE
  dest_lob CLOB;
BEGIN
  dest_lob:='E6B58BE8AF95'::CLOB;
  DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GET_STORAGE_LIMIT(dest_lob));
END;
2147483647