自定義函數實現列轉行效果

在 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
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！個人博客，能力有限，若有不當之處，敬請批評指正，謝謝！