字串連線超長的解決（二）

在我的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的處理速度要比字串慢一些。