Oracle中處理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  /

本欄目更多精彩內容：http://www.bianceng.cn/database/Oracle/

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相關的文檔。