可以處理LOB欄位的常用字元函式

以前處理LOB欄位都是使用DBMS_LOB包，最近看了文件才發現，原來很多常用的字元函式都是支援LOB欄位的。

 

 

建立一個測試表：

SQL> CREATE TABLE T_LOB (ID NUMBER, CONTENTS CLOB);

Table created.

SQL> DECLARE
  2     V_LOB CLOB;
  3  BEGIN
  4        INSERT INTO T_LOB
  5     VALUES (1, EMPTY_CLOB())
  6     RETURN  CONTENTS INTO V_LOB;
  7     FOR I IN 1..100 LOOP
  8             DBMS_LOB.WRITEAPPEND(V_LOB, 32767, LPAD('A', 32767, 'A'));
  9     END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT ID, DBMS_LOB.GETLENGTH(CONTENTS) FROM T_LOB;

        ID DBMS_LOB.GETLENGTH(CONTENTS)
---------- ----------------------------
         1                      3276700

SQL> SELECT ID, LENGTH(CONTENTS) FROM T_LOB;

        ID LENGTH(CONTENTS)
---------- ----------------
         1          3276700

這裡的LENGTH函式顯然不是對CLOB進行了隱式的TO_CHAR轉換，因為那樣的話，要不然因為CLOB長度太大而報錯，要不然CLOB自動擷取前4000個字元而LENGTH返回的長度則是4000。這裡的LENGTH函式明顯接收的是CLOB型別的引數。

除了LENGTH外，常用的INSTR、SUBSTR等都是支援LOB欄位的：

SQL> DECLARE
  2     V_LOB CLOB;
  3  BEGIN
  4     SELECT CONTENTS
  5     INTO V_LOB
  6     FROM T_LOB
  7     WHERE ID = 1
  8     FOR UPDATE;
  9     DBMS_LOB.WRITEAPPEND(V_LOB, 1000, RPAD('BCDEFG', 1000, 'A'));
 10     COMMIT;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_LOB.INSTR(CONTENTS, 'B')
  2  FROM T_LOB
  3  WHERE ID = 1;

DBMS_LOB.INSTR(CONTENTS,'B')
----------------------------
                     3276701

SQL> SELECT INSTR(CONTENTS, 'B')
  2  FROM T_LOB
  3  WHERE ID = 1;

INSTR(CONTENTS,'B')
-------------------
            3276701

SQL> SELECT DBMS_LOB.SUBSTR(CONTENTS, 10, 3276701)
  2  FROM T_LOB
  3  WHERE ID = 1;

DBMS_LOB.SUBSTR(CONTENTS,10,3276701)
-------------------------------------------------------------------------------------------
BCDEFGAAAA

SQL> SELECT SUBSTR(CONTENTS, 3276701, 10)
  2  FROM T_LOB
  3  WHERE ID = 1;

SUBSTR(CONTENTS,3276701,10)
--------------------------------------------------------------------------------
BCDEFGAAAA

另外，LPAD、RPAD以及LTRIM和RTRIM也是支援CLOB型別的：

SQL> SELECT LTRIM(RTRIM(CONTENTS, 'A'), 'A')
  2  FROM T_LOB
  3  WHERE ID = 1;

LTRIM(RTRIM(CONTENTS,'A'),'A')
--------------------------------------------------------------------------------
BCDEFG

SQL> SELECT LENGTH(LPAD(CONTENTS, 10000000, 'B'))
  2  FROM T_LOB
  3  WHERE ID = 1;

LENGTH(LPAD(CONTENTS,10000000,'B'))
-----------------------------------
                           10000000

字元連線操作||也支援LOB型別：

SQL> SELECT LENGTH(CONTENTS || CONTENTS)
  2  FROM T_LOB
  3  WHERE ID = 1;

LENGTH(CONTENTS||CONTENTS)
--------------------------
                   6555400

如果想找更加詳細的支援LOB的函式說明可以參考LOB相關的文件。