String Aggregation Techniques字符串连接技巧

参考网址:

http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php#listagg

http://docs.oracle.com/cd/B14117_01/appdev.101/b10800/dciaggref.htm

http://www.cxy.me/BBS/view26-22712-1.htm

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2196162600402

LISTAGG Analystic Function in 11g Release 2

The LISTAGG analytic function was introduced in Oracle 11g Release 2(11g中引入), making it very easy to aggregate strings. The nice thing about this function is it also allows us to order the elements in the concatenated list. (允许为聚合的元素进行排序)If you are using 11g Release 2 you should use this function for string aggregation.

1 SELECT DEPTNO,
2        LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS EMPLOYEES
3   FROM EMP
4  GROUP BY DEPTNO;

WM_CONCAT Built-in Function (Not Supported)

If you are not running 11g Release 2, but are running a version of the database where the WM_CONCAT function is present, then it is a zero effort solution as it performs the aggregation for you. It is actually an example of a user defined aggregate function described below(类似于自定义的聚合函数), but Oracle have done all the work for you.

1 SELECT DEPTNO, WM_CONCAT(ENAME) AS EMPLOYEES FROM EMP GROUP BY DEPTNO;

Note. WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.

(官方当前没有文档记录，这类一般不再Oracle的实例中被支持，可以使用用户自定义的聚合函数)

User-Defined Aggregate Function

The WM_CONCAT function described above is an example of a user-defined aggregate function that Oracle have already created for you(WM_CONCAT是Oracle已经实现的一个聚合函数). If you don't want to use WM_CONCAT, you can create your own user-defined aggregate function as described at asktom.oracle.com.

自定义函数:

1 CREATE OR REPLACE FUNCTION GET_EMPLOYEES(P_DEPTNO IN EMP.DEPTNO%TYPE)
2   RETURN VARCHAR2 IS
3   L_TEXT VARCHAR2(32767) := NULL;
4 BEGIN
5   FOR CUR_REC IN (SELECT ENAME FROM EMP WHERE DEPTNO = P_DEPTNO) LOOP
6     L_TEXT := L_TEXT || ',' || CUR_REC.ENAME;
7   END LOOP;
8   RETURN LTRIM(L_TEXT, ',');
9 END;

查询:

1 SELECT DEPTNO, GET_EMPLOYEES(DEPTNO) AS EMPLOYEES FROM EMP GROUP BY DEPTNO;

To reduce the number of calls to the function, and thereby improve performance, we might want to filter the rows in advance.(为了减少调用的次数，我们可以提前过滤行)：

1 SELECT E.DEPTNO, GET_EMPLOYEES(E.DEPTNO) AS EMPLOYEES
2   FROM (SELECT DISTINCT DEPTNO FROM EMP) E;

使用REF_SURSOR的函数:

 1 CREATE OR REPLACE FUNCTION CONCATENATE_LIST(P_CURSOR IN SYS_REFCURSOR)
 2   RETURN VARCHAR2 IS
 3   L_RETURN VARCHAR2(32767);
 4   L_TEMP   VARCHAR2(32767);
 5 BEGIN
 6   LOOP
 7     FETCH P_CURSOR
 8       INTO L_TEMP;
 9     EXIT WHEN P_CURSOR%NOTFOUND;
10     L_RETURN := L_RETURN || ',' || L_TEMP;
11   END LOOP;
12   RETURN LTRIM(L_RETURN, ',');
13 END;

调用语句:需要动态的构造一个CURSOR

1 SELECT E1.DEPTNO,
2        CONCATENATE_LIST(CURSOR (SELECT E2.ENAME
3                            FROM EMP E2
4                           WHERE E2.DEPTNO = E1.DEPTNO)) EMPLOYEES
5   FROM EMP E1
6  GROUP BY E1.DEPTNO;

使用10G中提供的COLLECT函数:

An example on oracle-developer.net uses the COLLECT function in Oracle 10g to get the same result. This method requires a table type and a function to convert the contents of the table type to a string. I've altered his method slightly to bring it in line with this article.

新建TYPE:

1 CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);

函数:

 1 CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
 2                                           p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
 3   l_string     VARCHAR2(32767);
 4 BEGIN
 5   FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
 6     IF i != p_varchar2_tab.FIRST THEN
 7       l_string := l_string || p_delimiter;
 8     END IF;
 9     l_string := l_string || p_varchar2_tab(i);
10   END LOOP;
11   RETURN l_string;
12 END tab_to_string;

其实该函数可以使用另一种方法：

即使用SQL类型的使用TABLE函数转换成行

 1 CREATE OR REPLACE FUNCTION tab2str(p_str2tbltype str2tbltype,
 2                     p_delim       IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
 3     l_result VARCHAR2(4000);
 4   BEGIN
 5     FOR cc IN (SELECT column_value
 6                  FROM TABLE(p_str2tbltype)
 7                 ORDER BY column_value) LOOP
 8       l_result := l_result || p_delim || cc.column_value;
 9     END LOOP;
10     RETURN ltrim(l_result, p_delim);
11   END;

注意：此处可能出现的问题是拼接的字符串的长度超过4000，则应该将拼接的结果以CLOB来展示：

以下是实现CLOB的方式的函数：

具体实现可以参考网址：

http://www.cnblogs.com/caroline/archive/2012/11/13/2768902.html

 1 CREATE OR REPLACE FUNCTION tab2clob(p_str2tbltype str2tbltype,
 2                     p_delim       IN VARCHAR2 DEFAULT ',') RETURN CLOB IS
 3     l_result CLOB;
 4   BEGIN
 5     FOR cc IN (SELECT column_value
 6                  FROM TABLE(p_str2tbltype)
 7                 ORDER BY column_value) LOOP
 8       l_result := l_result || p_delim || cc.column_value;
 9     END LOOP;
10     RETURN ltrim(l_result, p_delim);
11   END;

上述两个函数的调用方式是一致的。

调用:

1 SELECT DEPTNO,
2        TAB_TO_STRING(CAST(COLLECT(ENAME) AS T_VARCHAR2_TAB)) AS EMPLOYEES
3   FROM EMP
4  GROUP BY DEPTNO;

在这里，介绍下TABLE函数的使用：

1 SELECT *
2   FROM TABLE (SELECT CAST(COLLECT(ename) AS t_varchar2_tab) AS employees
3                 FROM emp)

使用CONNECT_BY_PATH结合分析函数来实现：

这个SQL语句动态的构造了树形结构的关系，curr和prev两个列直接的关系

实现的SQL语句：

 1 SELECT deptno,
 2        ltrim(MAX(sys_connect_by_path(ename, ','))
 3              keep(dense_rank LAST ORDER BY curr),
 4              ',') AS employees
 5   FROM (SELECT deptno,
 6                ename,
 7                row_number() over(PARTITION BY deptno ORDER BY ename) AS curr,
 8                row_number() over(PARTITION BY deptno ORDER BY ename) - 1 AS prev
 9           FROM emp)
10  GROUP BY deptno
11 CONNECT BY prev = PRIOR curr
12        AND deptno = PRIOR deptno
13  START WITH curr = 1;

在上述语句中，可以不使用分析函数dense_rank,直接使用MAX就可以实现功能：

 1 SELECT deptno,
 2        ltrim(MAX(sys_connect_by_path(ename, ',')), ',') AS employees
 3   FROM (SELECT deptno,
 4                ename,
 5                row_number() over(PARTITION BY deptno ORDER BY ename) AS curr,
 6                row_number() over(PARTITION BY deptno ORDER BY ename) - 1 AS prev
 7           FROM emp)
 8  GROUP BY deptno
 9 CONNECT BY prev = PRIOR curr
10        AND deptno = PRIOR deptno
11  START WITH curr = 1;

也可以采用如下的功能来实现：较上一种SQL语句可能更为简单：

参考网址：http://www.williamrobertson.net/documents/one-row.html

 1 SELECT deptno,
 2        ltrim(sys_connect_by_path(ename, ','), ',')
 3   FROM (SELECT deptno,
 4                ename,
 5                row_number() over(PARTITION BY deptno ORDER BY ename) - 1 AS seq
 6           FROM emp)
 7  WHERE connect_by_isleaf = 1
 8 CONNECT BY seq = PRIOR seq + 1
 9        AND deptno = PRIOR deptno
10  START WITH seq = 0;

上述的语句同样避免了使用GROUP不子句

在此可以学习树结构构造字符串转换成行的一种思想：

即使用：Hierarchical + CONNECT_BY_ROOT : 适用范围：10g

此处需要注意：在Oracle11中，不允许在条件使用

connect_by_root语句，在11G中下面的语句存在错误

 1 SELECT id,
 2        connect_by_root id,
 3        LEVEL AS p,
 4        rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
 5   FROM t_str_row
 6 CONNECT BY id = connect_by_root
 7  id
 8        AND LEVEL <=
 9            length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
10  ORDER BY 1,
11           2;

表中存储的字符串为：

使用上述语句的结果：

对于实现上述的功能，还可以采用以下的SQL来实现：

即使用Hierarchical + DBMS_RANDOM: 适用范围：10g及以后版本

SELECT id,
       LEVEL AS p,
       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv
  FROM t_str_row
CONNECT BY nocycle
 id = PRIOR id
       AND PRIOR dbms_random.value IS NOT NULL
       AND LEVEL <=
           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))
 ORDER BY 1,
          2;

--COLLECT参考：http://www.cnblogs.com/caroline/archive/2011/12/19/2294056.html