Oracle 自訂聚合函數實作指南：以字串串接為例

Oracle 內建聚合函數（MAX、SUM、AVG 等）僅支援純量資料，對於物件型別、CLOB 等複雜類型無法直接使用。幸運的是，透過實作 ODCIAggregate 介面，開發者可建立自訂聚合函數，使用方式與內建函數無異。

ODCIAggregate 介面四大核心方法

1. ODCIAggregateInitialize：初始化聚合環境，回傳物件實例給 Oracle
2. ODCIAggregateIterate：核心邏輯，遍歷每一筆非 NULL 值，更新聚合上下文
3. ODCIAggregateMerge：合併兩個聚合上下文，平行查詢時使用
4. ODCIAggregateTerminate：結束聚合，回傳最終結果

完整實作範例：字串串接函數 f_concatenate_str

1. 定義物件型別

CREATE OR REPLACE TYPE typ_concatenate_impl AS OBJECT (
  retstr       VARCHAR2(30000),
  separator    VARCHAR2(64) DEFAULT ',',
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT typ_concatenate_impl) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT typ_concatenate_impl, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN typ_concatenate_impl, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER
);

2. 實作型別主體

CREATE OR REPLACE TYPE BODY typ_concatenate_impl IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT typ_concatenate_impl) RETURN NUMBER IS
  BEGIN
    sctx := typ_concatenate_impl('', ',');
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT typ_concatenate_impl, value IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    self.retstr := self.retstr || value || self.separator;
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN typ_concatenate_impl, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := SUBSTR(self.retstr, 1, LENGTH(self.retstr) - 1);
    RETURN ODCICONST.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT typ_concatenate_impl, ctx2 IN typ_concatenate_impl) RETURN NUMBER IS
  BEGIN
    RETURN ODCICONST.SUCCESS;
  END;
END;

3. 建立聚合函數

CREATE OR REPLACE FUNCTION f_concatenate_str(i_str VARCHAR2) RETURN VARCHAR2
AGGREGATE USING typ_concatenate_impl;

測試與對比

CREATE TABLE test (id NUMBER, name VARCHAR2(20));
INSERT INTO test VALUES (1, 'AAA');
INSERT INTO test VALUES (2, 'BBB');
INSERT INTO test VALUES (1, 'ABC');
INSERT INTO test VALUES (3, 'CCC');
INSERT INTO test VALUES (2, 'DDD');
COMMIT;

GROUP BY 查詢

分析函數 OVER(PARTITION BY)

兩者結果一致，差異僅在自訂函數保留結尾分隔符。Oracle 10g 內建未文件化的 wmsys.wm_concat 同樣可用，但自訂函數可彈性控制分隔符、排序等邏輯。

應用場景延伸

• 複雜型別聚合（Object Type、CLOB、XMLType）
• 自訂統計邏輯（中位數、眾數、加權平均）
• 平行查詢優化（實作 Merge 方法）

掌握 ODCIAggregate 介面，即可突破內建函數限制，打造領域專屬的聚合邏輯。