Oracle 自定义函数实现列转行效果
在 Oracle 领域，我相信一说到列转行大部分人都会立马想到 WM_CONCAT 函数，我觉得主要是因为该函数比较实用。但事实上 WM_CONCAT 并非官方公开函数，使用会存在一定的风险；函数返回值的格式比较单一（只能用逗号分割）；返回值的长度也限制。
在《.Net程序员学用Oracle系列(20)：层次查询(CONNECT BY)》一文中，详细讲解了 WM_CONCAT 函数的用法。如果不用 WM_CONCAT 函数又该如何实现列转行呢？当数据类别比较少的时候，通过 CASE 判断或 UNION ALL 查询也能实现效果，但问题就是写法太死板，不过一般也没人会这么干。本文接下来会介绍两个自定义函数分别来实现列转行的聚合效果。
第一个自定义函数主要是辅以 COLLECT 函数来实现的，我在回答一个园友的问题时列出了实现步骤，查看该问题。下面是我重新整理后的版本：
第 1 步（创建类型 type_table_string，用于转换 COLLECT 函数的返回值）：
CREATE OR REPLACE TYPE type_table_string IS TABLE OF VARCHAR2(4000);
第 2 步（创建函数 fn_to_string，用于将 type_table_string 类型转换成普通字符串）：
CREATE OR REPLACE FUNCTION fn_to_string(
p_str_tab IN type_table_string,
p_separator IN VARCHAR2 DEFAULT ','
)
RETURN VARCHAR2 IS
v_ret_str VARCHAR2(4000);
BEGIN
FOR i IN 1..p_str_tab.COUNT LOOP
v_ret_str:=v_ret_str||p_separator||p_str_tab(i);
END LOOP;
RETURN LTRIM(v_ret_str,p_separator);
END;
调用方法：
SELECT t.dept_code,fn_to_string(CAST(COLLECT(t.staff_name) AS type_table_string),'|') staff_names
FROM demo.t_staff t GROUP BY t.dept_code;
调用结果：
DEPT_CODE STAFF_NAMES
-------------------------------- ----------------------------------------------
010101 小明|小强|王二
010102 小林|小萨
010103 韩三|小玲
010104 小梅|小燕
010201 小军|小芳|小红
010202 小飞
第二个自定义函数在百度中输入“zh_concat”就能找到一堆的参考，我有尝试寻找 zh_concat 函数的出处，但网上的翻版实在太多，而且大部分比较无耻（没写出处），结果就是我没能找到该函数的出处，因此在这里我也没法儿给出 zh_concat 函数的出处了。
我找到的那些有关 zh_concat 函数的帖子内容普遍比较混乱，于是我决定整理出一个更加清晰、整洁和美观的版本，以便阅读和理解，具体如下：
第 1 步（创建类型 type_concat 的定义）：
CREATE OR REPLACE TYPE type_concat
AUTHID CURRENT_USER AS OBJECT(
v_result_string VARCHAR2(4000),
STATIC FUNCTION odciAggregateInitialize(
concat IN OUT type_concat) RETURN NUMBER,
MEMBER FUNCTION odciAggregateIterate(
SELF IN OUT type_concat,str IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION odciAggregateTerminate(
SELF IN type_concat,return_value OUT VARCHAR2,flags IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION odciAggregateMerge(
SELF IN OUT type_concat,concat IN type_concat) RETURN NUMBER
);
第 2 步（创建类型 type_concat 的 body）：
CREATE OR REPLACE TYPE BODY type_concat
IS
STATIC FUNCTION odciAggregateInitialize(concat IN OUT type_concat)
RETURN NUMBER IS
BEGIN
concat := type_concat(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION odciAggregateIterate(SELF IN OUT type_concat,str IN VARCHAR2)
RETURN NUMBER IS
BEGIN
IF SELF.v_result_string IS NOT NULL THEN
SELF.v_result_string := SELF.v_result_string||','||str;
ELSE
SELF.v_result_string := str;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION odciAggregateTerminate(SELF IN type_concat,return_value OUT VARCHAR2,flags IN NUMBER)
RETURN NUMBER IS
BEGIN
return_value := SELF.v_result_string;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION odciAggregateMerge(SELF IN OUT type_concat,concat IN type_concat)
RETURN NUMBER IS
BEGIN
IF concat.v_result_string IS NOT NULL THEN
SELF.v_result_string := SELF.v_result_string||','||concat.v_result_string;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
第 3 步（创建函数 fn_concat，可替代 WM_CONCAT）：
CREATE OR REPLACE FUNCTION fn_concat(str VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING type_concat;
调用方法：
SELECT t.dept_code,fn_concat(t.staff_name) staff_names FROM demo.t_staff t GROUP BY t.dept_code;
调用结果：
DEPT_CODE STAFF_NAMES
------------------------------------- ----------------------------------------------------
010101 小明,小强,王二
010102 小林,小萨
010103 韩三,小玲
010104 小梅,小燕
010201 小军,小芳,小红
010202 小飞
说明一：上例中的 AUTHID CURRENT_USER 是权限控制的关键字，表示调用者权限，即当前用户。默认为 AUTHID DEFINER，表示定义者权限，即模式拥有者。
说明二：将 type_concat 中 v_result_string 和 return_value 的类型改为 CLOB 类型，并将 fn_concat 的返回值类型也改为 CLOB，就成了 CLOB 版的 fn_concat 了。
本文链接：http://www.cnblogs.com/hanzongze/p/oracle-wm_concat.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！个人博客，能力有限，若有不当之处，敬请批评指正，谢谢！
