oracle 聚类函数 group 连接字符 clob

create or replace type CLOB_GROUP_OBJ as object ( 
    sum_string clob,
    static function ODCIAggregateInitialize(v_self in out CLOB_GROUP_OBJ) return number, 
    member function ODCIAggregateIterate(self in out CLOB_GROUP_OBJ, value in varchar2) return number, 
    member function ODCIAggregateMerge(self in out CLOB_GROUP_OBJ, v_next in CLOB_GROUP_OBJ) return number, 
    member function ODCIAggregateTerminate(self in CLOB_GROUP_OBJ, return_value out clob ,v_flags in number) return number
)
/
create or replace type body CLOB_GROUP_OBJ is
    static function ODCIAggregateInitialize(v_self in out CLOB_GROUP_OBJ) return number is
    begin
        v_self := CLOB_GROUP_OBJ(null);
        return ODCICONST.Success;
    end;
    member function ODCIAggregateIterate(self in out CLOB_GROUP_OBJ, value in varchar2) return number is
    begin 
         self.sum_string := self.sum_string || value||'|';
         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 CLOB_GROUP_OBJ, v_next in CLOB_GROUP_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 CLOB_GROUP_OBJ, return_value out clob ,v_flags in number) return number is
    begin
         return_value:= self.sum_string;
         return ODCICONST.Success;
    end;
end;
/
create or replace function GROUPCATCLOB(value varchar2) return clob
    parallel_enable aggregate using CLOB_GROUP_OBJ;


http://blog.chinaunix.net/u3/97653/showart.php?id=2255719