ORACLE分析函數(7) 自訂彙總函式

oracle為我們提供了非常豐富的彙總函式，如SUM\AVG\MAX等。除此之外，我們還可以編寫自己的彙總函式，當然，自訂彙總函式也可以作為分析函數來使用。

自訂彙總函式與oracle內建彙總函式並無太大區別，而且它可以使用任何oracle支援的語言來編寫，如PL/SQL\C\C++\JAVA。在本文中，我們以PLSQL為開發語言，嘗試編製我們自己的彙總函式。

編製ORACLE支援的自訂彙總函式，自然要依據oracle指定的規則來編寫，我們不妨稱之為ODCIAggregate規則。下面我們以編寫擷取組中第二大值的的彙總函式為例，看一下自訂彙總函式的編製過程。

1：首先建立一個OBJECT TYPE

CREATE or REPLACE type secmax_context AS object(    firmax NUMBER, -- 儲存最大值，這部分內容根據彙總函式操作的不同，有使用者自行設定    secmax NUMBER, -- 儲存第二大值，這部分內容根據彙總函式操作的不同，有使用者自行設定            --（該步驟是必須的）初始化函數，必須要實現的方法，用於在彙總運算的最開始部分，初始化上下文環境    static FUNCTION ODCIAggregateInitialize(sctx IN OUT secmax_context) RETURN NUMBER,             --（該步驟是必須的）迭代運算函數，oracle依據該函數進行迭代運算，第一個參數為彙總運算的上下文，    --第二個參數為當前需要處理的值,可以為number varchar2等類型，    --在迭代過程中，如果當前值為null，則忽略該次迭代    member FUNCTION ODCIAggregateIterate(self  IN OUT secmax_context,value IN NUMBER) RETURN NUMBER,             --（該步驟是必須的，但是在執行過程中，oracle會有選擇的執行該步驟）該函數用於合并兩個上下文到一個上下文中，在並行和串列環境下均有可能發揮作用    member FUNCTION ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context)RETURN NUMBER,            --（該步驟是必須的）該函數在彙總運算的最後一步運行，用於對結果進行處理並返回處理結果，    --第一個參數為上下文，第二個參數為傳回值，可以為number，varchar2等類型    --第三個參數為標識位    member FUNCTION ODCIAggregateTerminate(self IN secmax_context,returnValue OUT NUMBER,flags IN NUMBER) RETURN NUMBER  );

2：實現該OBJECT TYPE

create or replace type body secmax_context is      static function ODCIAggregateInitialize(sctx IN OUT secmax_context) return number isbegin  sctx := secmax_context(0, 0);    return ODCIConst.Success;  end;        member function ODCIAggregateIterate(self IN OUT secmax_context, value IN number) return number isbegin  if value > self.firmax then    self.secmax := self.firmax;      self.firmax := value;    elsif value > self.secmax then    self.secmax := value;    end if;    return ODCIConst.Success;  end;        member function ODCIAggregateTerminate(self IN secmax_context, returnValue OUT number, flags IN number) return number isbegin  returnValue := self.secmax;    return ODCIConst.Success;  end;        member function ODCIAggregateMerge(self IN OUT secmax_context, ctx2 IN secmax_context) return number isbegin  if ctx2.firmax > self.firmax then    if ctx2.secmax > self.firmax then      self.secmax := ctx2.secmax;            else      self.secmax := self.firmax;      end if;      self.firmax := ctx2.firmax;    elsif ctx2.firmax > self.secmax then    self.secmax := ctx2.firmax;    end if;    return ODCIConst.Success;  end;  end;