oracle 自定義 聚合函數

Oracle自定義聚合函數實現字符串連接的聚合

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?
???? --終止聚集函數的處理,返回聚集函數處理的結果.?
)?
/?
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?
????????? /* 連接 */????
????????? 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 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 ConnStrSum(value Varchar2) return Varchar2?
???? parallel_enable aggregate using string_sum_obj;