oracle中多行合并彙總函式

標籤：style   blog   http   color   io   os   使用   ar   資料   

在oracle資料庫中， 進列欄位合并，可以使用wm_concat(column)函數，但是在這種方法不被Oracle所推薦，因為WMSYS使用者用於Workspace Manager，其函數對象可能因版本而不同而出現異常，這種變化在11.2.0.3及10.2.0.5中體現出來。原本WM_CONCAT函數傳回值為 VARCHAR2，但在一些版本下就會變更為CLOB。這一變化導致了很多程式的異常。Oracle建議使用者使用自訂函數來實現該功能，而不是使用WorkSpace的這個內建函式。這個函數包含一個Type、Type Body、Function，可以參考Oracle的實現方式來實現這個函數。

 

 

COUNTRY              CITY
-------------------- --------------------
中國                 台北
中國                 香港
中國                 上海
日本                 東京
日本                 大阪

COUNTRY              CITY
-------------------- --------------------
中國                 台北 香港 上海
日本                 東京 大阪

select country,strcat(city) from t_city group by country

1.建立類型

create or replace type strcat_type as object (
cat_string varchar2(4000),
static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,
member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return 
number,
member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) 
return number,
member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out 
varchar2,flags in number) return number
);

2. 建立類型體

create or replace type body strcat_type is
static function ODCIAggregateInitialize(cs_ctx IN OUT strcat_type) return number
is
begin
      cs_ctx := strcat_type( null );
      return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT strcat_type,
                                       value IN varchar2 )
return number
is
begin
      self.cat_string := self.cat_string || ‘;‘|| value;
      return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN Out strcat_type,
                                         returnValue OUT varchar2,
                                         flags IN number)
return number
is
begin
      returnValue := ltrim(rtrim(self.cat_string,‘;‘),‘;‘);
      return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT strcat_type,
                                     ctx2 IN Out strcat_type)
return number
is
begin
      self.cat_string := self.cat_string || ‘;‘ || ctx2.cat_string;
      return ODCIConst.Success;
end;

end;

3.建立函數
CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;

oracle中多行合并彙總函式