在oracle里自定义一个字符串的聚集函数

一、定义type、 type body、function

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
)

 

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
      --1. concat string
      self.cat_string := self.cat_string || ','|| value;
      -- 2.get union set
      -- if  instr(self.cat_string, value ) = 0 or self.cat_string is null then
      --        self.cat_string := self.cat_string || ',' || value ;
      -- else
      --        self.cat_string := self.cat_string ||'' ;
      -- end if ;
      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;

 

CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;

 

二、使用该聚集函数

 

    数据库中已经存在一张表A，如下所示：

SQL> desc a;
Name Type     Nullable Default Comments 
------- ------------- -------- ------- -------- 
COUNTRY VARCHAR2(100) Y                       
CITY VARCHAR2(100) Y                  

 

SQL> select * from a;
 
COUNTRY                                                             CITY
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
中国                                                                      台北
中国                                                                      香港
中国                                                                      上海
日本                                                                      东京
日本                                                                      大阪

 

    下面来使用步骤一中创建的strcat函数：

SQL> SELECT t.country,strcat(t.city) FROM A t GROUP BY t.country; 
 
COUNTRY                                                             STRCAT(T.CITY)
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
日本                                                                      东京,大阪
中国                                                                      台北,香港,上海