Oracle9i添加 wm_concat函数（转）

一：

创建type头

create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
     sum_string varchar2(4000),
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,
     --对象初始化
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number,
     --聚合函数的迭代方法(这是最重要的方法)
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number,
     --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
     
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number
     --终止聚集函数的处理,返回聚集函数处理的结果.
)


创建type具体
create or replace type body string_sum_obj is
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
     begin
         v_self := string_sum_obj(null);
         return ODCICONST.Success;
     end;
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is
     begin
          /* 连接，解决逗号分隔第一个字母是逗号的问题 */   
           if not self.sum_string is null then
          self.sum_string := self.sum_string ||','|| value;
          else
          self.sum_string := self.sum_string || value;
          end if;
          return ODCICONST.Success;
          /* 最大值 */
          if self.sum_string<value then
              self.sum_string:=value;
          end if;
          /* 最小值 */
          if self.sum_string>value then
       self.sum_string:=value;          
          end if;
          
          return ODCICONST.Success;
     end;
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is
     begin
          /* 连接 */   
          self.sum_string := self.sum_string || v_next.sum_string;
          return ODCICONST.Success;
          /* 最大值 */
          if self.sum_string<v_next.sum_string then
              self.sum_string:=v_next.sum_string;
          end if;

          /* 最小值 */
          if self.sum_string>v_next.sum_string then
              self.sum_string:=v_next.sum_string;          
          end if;
          
          return ODCICONST.Success;
     end;
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is
     begin
          return_value:= self.sum_string;
          return ODCICONST.Success;
     end;
end;


创建函数
create or replace function wm_concat(value Varchar2) return Varchar2
     parallel_enable aggregate using string_sum_obj;

二：

先创建这个类型

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 FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;


几个用法：

9i: SYS_CONNECT_BY_PATH 函数



SQL> select id, replace(wmsys.wm_concat(rmak), ',', '') from test group by id;

        ID REPLACE(WMSYS.WM_CONCAT(RMAK),
---------- --------------------------------------------------------------------------------
         9 timggg
        21 littlefff
        23 tom



SQL> select * from idtable;

        ID NAME
---------- ------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn

6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
  2  group by id;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        10 ab,bc,cd
        10 ab,bc,cd
        20 ab,bc,cd,hi,ij,mn
        20 ab,bc,cd,hi,ij,mn
        20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab
        10 ab,bc
        10 ab,bc,cd
        20 ab,bc,cd,hi
        20 ab,bc,cd,hi,ij
        20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab,bc,cd
        10 ab,bc,cd
        10 ab,bc,cd
        20 hi,ij,mn
        20 hi,ij,mn
        20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

        ID NAME
---------- --------------------------------------------------------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn

6 rows selected