字符串连接超长的解决wmsys.wm_concat()

在我的BLOG中第一篇文章写的就是字符串聚合连接的例子：http://yangtingkun.itpub.net/post/468/3380。

后来还写过一篇用SQL实现相同功能的文章：http://yangtingkun.itpub.net/post/468/388003。

不过上面两种方法都会面临一个问题，就是如果聚集连接的字符串长度如果超过了VARCHAR2类型所允许的最大长度，就会导致字符串超长的错误。

字符串连接超长的解决（一）：http://yangtingkun.itpub.net/post/468/482093

前一篇文章介绍了修改输出参数以及聚集函数返回值数据类型的方法，这种方法可以将输出结果的最大值增加到32767，但是如果数据量进一步增加，这种方法就行不通了：

SQL> SELECT F_LINK(OBJECT_NAME) FROM DBA_OBJECTS WHERE ROWNUM < 10000;
ERROR:
ORA-22813: 操作数值超出系统的限制

未选定行

而解决这个问题最简单的方法是修改自定义类型中内部变量的类型，将其修改为LOB类型：

SQL> CREATE OR REPLACE TYPE T_LINK_LOB AS OBJECT (
  2  V_LOB CLOB,
  3  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB) RETURN NUMBER,
  4  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER,
  5  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER,
  6  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER
  7  )
  8  /

类型已创建。

SQL> CREATE OR REPLACE TYPE BODY T_LINK_LOB IS
  2  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT NOCOPY T_LINK_LOB) RETURN NUMBER IS
  3  BEGIN
  4  SCTX := T_LINK_LOB(NULL);
  5  DBMS_LOB.CREATETEMPORARY(SCTX.V_LOB, TRUE, DBMS_LOB.SESSION);
  6  DBMS_LOB.OPEN(SCTX.V_LOB, DBMS_LOB.LOB_READWRITE);
  7  RETURN ODCICONST.SUCCESS;
  8  END;
  9 
10  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT NOCOPY T_LINK_LOB, VALUE IN VARCHAR2) RETURN NUMBER IS
11  BEGIN
12  DBMS_LOB.WRITEAPPEND(SELF.V_LOB, LENGTH(VALUE) + 1, VALUE || ',');
13  RETURN ODCICONST.SUCCESS;
14  END;
15  
16  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LINK_LOB, RETURNVALUE OUT NOCOPY CLOB, FLAGS IN NUMBER) RETURN NUMBER IS
17  BEGIN
18  DBMS_LOB.CREATETEMPORARY(RETURNVALUE, TRUE, DBMS_LOB.CALL);
19  DBMS_LOB.COPY(RETURNVALUE, SELF.V_LOB, DBMS_LOB.GETLENGTH(SELF.V_LOB) - 1);
20  RETURN ODCICONST.SUCCESS;
21  END;
22 
23  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT NOCOPY T_LINK_LOB, CTX2 IN T_LINK_LOB) RETURN NUMBER IS
24  BEGIN
25  NULL;
26  RETURN ODCICONST.SUCCESS;
27  END;
28  END;
29  /

类型主体已创建。

SQL> CREATE OR REPLACE FUNCTION F_LINK_LOB(P_STR VARCHAR2) RETURN CLOB
  2  AGGREGATE USING T_LINK_LOB;
  3  /

函数已创建。

通过LOB重新实现字符串累加的功能后，理论上讲不太可能在出现上面连接字符串超长的问题了：

SQL> SET AUTOT TRACE STAT
SQL> SELECT F_LINK_LOB(OBJECT_NAME) FROM DBA_OBJECTS WHERE ROWNUM < 10000;

Statistics
----------------------------------------------------------
7  recursive calls
    5427375  db block gets
     189829  consistent gets
          0  physical reads
          0  redo size
    1801748  bytes sent via SQL*Net to client
    1055711  bytes received via SQL*Net from client
       5832  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

需要注意的是，虽然利用LOB能解决这个问题，但是除非字符串长度确实超过了32767的限制，否则不要使用这种方式来处理，因为LOB的处理速度要比字符串慢一些。