如何在Oracle PL/SQL中创建表无关的聚合UDF？

现有如下staff表：

CREATE TABLE staff (
  id INT,
  name CHAR(9)
);

插入以下数据：

INSERT INTO staff (id, name) VALUES (1, 'Joe');
INSERT INTO staff (id, name) VALUES (2, 'Bob');
INSERT INTO staff (id, name) VALUES (3, 'Alice');

需要创建一个类似内置AVG函数的多行UDF（用户定义聚合函数），使其能通过如下方式调用：

SELECT vowel_count(name) FROM staff;

假设元音为[AaIiEeOoUu]，需得到结果：

| vowel_count(name) |
|-------------------|
|                 6 |

请问该UDF接收表列作为输入的语法是什么？函数模板如下：

CREATE OR REPLACE FUNCTION vowel_cnt(/* 此处应填写什么？ */) 
  RETURN NUMBER
IS
  ...
BEGIN
  ...
END;

要求该函数需与表无关，如同SUM、AVG等内置函数。使用环境为Oracle PL/SQL及SQL Developer工具。

要实现这种类似SUM、AVG的多行聚合逻辑，你需要创建用户定义聚合函数——Oracle中单纯的普通函数无法实现跨行汇总，必须结合自定义对象类型和聚合函数语法来完成。

1. 定义聚合计算的对象类型 #

这个类型用于维护聚合的状态（比如累计的元音总数），并实现聚合所需的三个核心方法：

CREATE OR REPLACE TYPE VowelCountType AS OBJECT (
  total_vowels NUMBER,
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT VowelCountType) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT VowelCountType, value IN VARCHAR2) RETURN NUMBER,
  MEMBER FUNCTION ODCIAggregateTerminate(self IN VowelCountType, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY VowelCountType IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT VowelCountType) RETURN NUMBER IS
  BEGIN
    sctx := VowelCountType(0);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT VowelCountType, value IN VARCHAR2) RETURN NUMBER IS
    v_char CHAR(1);
  BEGIN
    -- 遍历输入字符串的每个字符，统计元音数量
    FOR i IN 1..LENGTH(value) LOOP
      v_char := SUBSTR(value, i, 1);
      IF v_char IN ('A','a','I','i','E','e','O','o','U','u') THEN
        self.total_vowels := self.total_vowels + 1;
      END IF;
    END LOOP;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN VowelCountType, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnValue := self.total_vowels;
    RETURN ODCIConst.Success;
  END;
END;
/

2. 创建聚合函数（对应你的模板） #

函数参数定义为VARCHAR2类型（可兼容Oracle的CHAR、VARCHAR2等字符列，自动隐式转换），并指定使用上面的聚合类型：

CREATE OR REPLACE FUNCTION vowel_cnt(input_str VARCHAR2) 
  RETURN NUMBER
AGGREGATE USING VowelCountType;
/

3. 验证调用 #

现在可以像内置聚合函数一样使用它，且不受表限制：

SELECT vowel_cnt(name) AS vowel_count FROM staff;

执行后得到预期结果：

| VOWEL_COUNT |
|-------------|
|           6 |

补充说明 #

• 该函数支持分组统计，搭配GROUP BY即可实现按维度汇总，比如：

-- 示例：若有部门列，按部门统计元音总数
SELECT dept_id, vowel_cnt(name) FROM staff GROUP BY dept_id;

• 参数用VARCHAR2而非特定表的列类型，确保了函数的通用性，和SUM、AVG的无表依赖特性一致。

内容的提问来源于stack exchange，提问作者nluka