oracle dbms_lob trim,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  /

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相关的文档。