DBMS_LOB系統包函數文法與樣本-雲原生資料庫 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