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
版权声明:本文为博客园博主 韩宗泽 原创,作者保留署名权!欢迎通过转载、演绎或其它传播方式来使用本文,但必须在明显位置给出作者署名和本文链接!个人博客,能力有限,若有不当之处,敬请批评指正,谢谢!

posted @ 2017-06-22 14:43  韩宗泽  阅读(1526)  评论(0编辑  收藏  举报
回到顶部