在oracle中创建分组函数

例子1：
create or replace type SecondMaxImpl as object
(
  max    NUMBER, -- highest value seen so far
  secmax NUMBER, -- second highest value seen so far
  static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
    return number,
  member function ODCIAggregateIterate(self  IN OUT SecondMaxImpl,
                                       value IN number) return number,
  member function ODCIAggregateTerminate(self        IN SecondMaxImpl,
                                         returnValue OUT number,
                                         flags       IN number)
    return number,
  member function ODCIAggregateMerge(self IN OUT SecondMaxImpl,
                                     ctx2 IN SecondMaxImpl) return number
)
;
create or replace type body SecondMaxImpl is
  static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl)
    return number is
  begin
    sctx := SecondMaxImpl(0, 0);
    return ODCIConst.Success;
  end;
  member function ODCIAggregateIterate(self  IN OUT SecondMaxImpl,
                                       value IN number) return number is
  begin
    if value > self.max then
      self.secmax := self.max;
      self.max    := value;
    elsif value > self.secmax then
      self.secmax := value;
    end if;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateTerminate(self        IN SecondMaxImpl,
                                         returnValue OUT number,
                                         flags       IN number) return number is
  begin
    returnValue := self.secmax;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateMerge(self IN OUT SecondMaxImpl,
                                     ctx2 IN SecondMaxImpl) return number is
  begin
    if ctx2.max > self.max then
      if ctx2.secmax > self.secmax then
        self.secmax := ctx2.secmax;
      else
        self.secmax := self.max;
      end if;
      self.max := ctx2.max;
    elsif ctx2.max > self.secmax then
      self.secmax := ctx2.max;
    end if;
    return ODCIConst.Success;
  end;
end;

CREATE OR REPLACE FUNCTION SecondMax (input NUMBER) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;

 例子2：

create or replace type strcat_type as object
(
  cat_string varchar2(4000),
  cat_flag   varchar2(10),
  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 strcat_type) return number,
  member function ODCIAggregateTerminate(self        IN 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('', 'false');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self  IN OUT strcat_type,
                                       value IN varchar2) return number is
  begin
    cat_flag := 'true';
    begin
      self.cat_string := self.cat_string || ',' || value;
    exception
      when others then
        RAISE_APPLICATION_ERROR(-20123,
                                'ODCIAggregateIterate:length:' ||
                                length(self.cat_string) || 'value:' ||
                                value,
                                TRUE);
    end;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT strcat_type,
                                     ctx2 IN strcat_type) return number is
  begin
    begin
      self.cat_string := self.cat_string || ',' || ctx2.cat_string;
    exception
      when others then
        RAISE_APPLICATION_ERROR(-20123, 'ODCIAggregateMerge:to long', TRUE);
    end;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self        IN strcat_type,
                                         returnValue OUT varchar2,
                                         flags       IN number) return number is
  begin
    if cat_flag = 'true' then
      returnValue := substr(self.cat_string, 2);
    else
      returnValue := '';
    end if;
    return ODCIConst.Success;
  end;
end;

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