重寫Oracle的wm_concat函數，自訂分隔字元、排序

標籤：loop   tag   order by   char   des   term   extend   reg   效果   

        oracle中，wm_concat函數是一個彙總函式，和mysql中的group_concat函數類似，不過group_concat函數比較強大，可以定義分隔字元和排序，當然所謂強大是相對的，這裡假使我們不知道oracle中的over函數，也不知道listagg函數。

       我們先來看看wm_concat函數能實現什麼功能，通俗點==>列傳行，如果不明白，請看下面(可以看到分隔字元預設為‘,‘順序也是雜亂的)

               

       所以，接下來，我們開始重寫wm_concat函數(需要注意和需要說明的地方放在代碼注釋中...)

(1) 因為需要進行排序，首先自訂一個可變數組

-- 定義可變數組，字串類型，長度500，存放列值CREATE OR REPLACE TYPE WYARRAY as TABLE OF VARCHAR(32767) ;

 

(2)自訂排序函數、分隔字元函數

-- 定義分隔字元函數create or replace function delimiter(colValue  in varchar2,                                     delimiter in varchar2) return varchar2 is  rtnValue varchar2(32767);begin  rtnValue := colValue || ‘ delimiter=>‘ || delimiter || ‘; ‘;  return rtnValue;end delimiter;

　　

-- 定義排序函數create or replace function orderby(colValue in varchar2,                                   orderby  in varchar2) return varchar2 is  rtnValue varchar2(32767);begin  rtnValue := colValue || ‘ orderby=>‘ || LOWER(orderby) || ‘; ‘;  return rtnValue;end orderby;

　

(3) 重定義oracle介面函數、以及介面函數的實現體(實現分隔字元和排序的主要代碼)

-- 使用目前使用者許可權(使用authid current_user，定義type為使用者目前使用者的許可權，舉個例子：比如A使用者他可以建立表，但是A使用者在預存程序中如果建立表可能會提示許可權不夠，所以需要用authid current_user進行約束)create or replace type wy_wm_concat authid current_user as object(--拼接字串,存放中間值，當然也可以定義為clob，clob會使用臨時段，導致暫存資料表空間迅速增大;--查看wmsys下的function可以發現Oracle10g到oracle11g內建的wm_concat函數的傳回型別從clob變成varchar2  currStr VARCHAR2(32767),--分割字串  delimiter VARCHAR2(64),--排序字串(asc、desc)  orderby VARCHAR2(64),-- 定義字串數組  strArray WYARRAY,-- 初始化介面函數  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)    RETURN NUMBER,-- 迭代介面函數  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,                                       colValue IN VARCHAR2) RETURN NUMBER,-- 並行時字串合并的介面函數  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,                                     next wy_wm_concat) RETURN NUMBER,-- oracle終止介面函數  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,                                         returnValue OUT VARCHAR2,                                         flags       IN NUMBER)    RETURN NUMBER)

　　

-- 自訂wy_wm_concat type的body部分
create or replace type body wy_wm_concat is  --初始化函數  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)    RETURN NUMBER is  begin    init := wy_wm_concat(‘‘, ‘,‘, ‘asc‘, WYARRAY());    return ODCICONST.Success;  END;  -- 字串拼接，self 為當前聚集合函式的指標,用來與前面的計算結果進行關聯  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,                                       colValue IN VARCHAR2) RETURN NUMBER is      tempStr varchar(500);      extendStr varchar(500);      deStr varchar(100);      deLen int default 0;      segStr varchar(500);      --定義一個二維數組    TYPE varArry IS VARRAY(2) OF VARCHAR2(200);      tempArry varArry := varArry(‘‘, ‘‘);    begin      if instr(colValue, ‘ ‘, 1) > 0 then      tempStr := substr(colValue, 1, instr(colValue, ‘ ‘, 1) - 1);    else      tempStr := colValue;    end if;      --排序和分隔字元    extendStr := REPLACE(colValue, tempStr || ‘ ‘);      if instr(extendStr, ‘ ‘, 1) > 0 then          tempArry(1) := substr(extendStr, 1, instr(extendStr, ‘ ‘, 1) - 1);          tempArry(2) := substr(extendStr, instr(extendStr, ‘ ‘, 1));          for i in 1 .. tempArry.count loop        -- 擷取分隔字元        if (tempArry(i) is not null) and           (instr(tempArry(i), ‘delimiter=>‘) > 0) THEN                  deStr := ‘delimiter=>‘;                  deLen := length(deStr);                  segStr := substr(trim(tempArry(i)),                           instr(trim(tempArry(i)), deStr) + deLen);                  self.delimiter := SUBSTR(segStr, 1, instr(segStr, ‘;‘, -1) - 1);        END IF;              -- 擷取排序字串        if tempArry(i) is not null and           (instr(tempArry(i), ‘orderby=>‘) > 0) THEN                  deStr := ‘orderby=>‘;                  deLen := length(deStr);                  segStr := substr(trim(tempArry(i)),                           instr(trim(tempArry(i)), deStr) + deLen);                  self.orderby := SUBSTR(segStr, 1, instr(segStr, ‘;‘, -1) - 1);                END IF;            end loop;        end if;      -- 存放入數組    self.strArray.extend;      self.strArray(self.strArray.count) := tempStr;      return ODCICONST.Success;  END;  --並行操作是用來合并兩個聚集合函式的兩個不同的指標對應的結果  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,                                     next wy_wm_concat) RETURN NUMBER is  begin      -- 將next數組中元素全部放入self指標對應的數組中    for i in 1 .. next.strArray.count loop          self.strArray.extend;          self.strArray(self.strArray.count) := next.strArray(i);        end loop;      return ODCICONST.Success;  END;  -- 終止函數，返回結果  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,                                         returnValue OUT VARCHAR2,                                         flags       IN NUMBER) RETURN NUMBER IS    temp_rtnValue varchar2(32767);    BEGIN    -- 排序    if INSTR(self.orderby, ‘desc‘) > 0 THEN          for x in (select column_value                  from Table(self.strArray)                 order by 1 DESC) loop              temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;            end loop;    ELSE      for x in (select column_value from Table(self.strArray) order by 1 ASC) loop              temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;            end loop;        END IF;      returnValue := ltrim(temp_rtnValue, self.delimiter);      return ODCICONST.Success;  END;END;

　

(4)自訂聚集合函式

-- 定義聚集合函式(未開啟並行計算功能)create or replace function wy_concat(colValue  VARCHAR2) RETURN VARCHAR2  AGGREGATE USING wy_wm_concat;

　　

  至此，主要的代碼已經全部奉上，看看運行效果，如下：

 

  ①看看調用的預設情況(分隔字元預設是逗號，排序預設是升序，在初始化函數中如此定義的)

  

   ②自訂分隔字元(利用分隔字元函數將分隔字元定義為*)

  

 

   ③降序排序

   

 

    ④去重，為了可以使用wm_concat內建的去重函數，所以在自訂分隔字元和排序函數時，實質是實用了字串處理(如果你覺得處理字串麻煩，可以自訂 type... as object ，在使用的時候可以很方便，不會用的童鞋可以私下問)

   

   

重寫Oracle的wm_concat函數，自訂分隔字元、排序