如何获取Oracle嵌套表中的唯一元素及其出现次数？

你拥有一个存储邮箱列表的PL/SQL嵌套表，其中存在重复邮箱，需要统计每个邮箱的唯一值及出现次数。直接用普通SELECT语句查询该嵌套表时会抛出ORA-00942: table or view does not exist错误，原因是嵌套表属于PL/SQL集合类型，无法直接通过SQL查询。

你的嵌套表定义及数据添加方式如下：

TYPE t_email_type IS TABLE OF VARCHAR2(100);
t_emails t_email_type := t_email_type();

-- 循环添加数据
t_emails.extend;
t_emails(t_emails.LAST) := user_r.email;

方案一：纯PL/SQL循环统计 #

通过遍历嵌套表，使用关联数组记录每个邮箱的出现次数：

DECLARE
  TYPE t_email_type IS TABLE OF VARCHAR2(100);
  t_emails t_email_type := t_email_type();
  -- 定义关联数组用于计数
  TYPE t_count_type IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(100);
  t_email_counts t_count_type;
  v_email VARCHAR2(100);
BEGIN
  -- 模拟数据填充（实际为业务循环添加）
  t_emails.extend(11);
  t_emails(1) := 'a@mail.com';
  t_emails(2) := 'b@mail.com';
  t_emails(3) := 'c@mail.com';
  t_emails(4) := 'd@mail.com';
  t_emails(5) := 'c@mail.com';
  t_emails(6) := 'c@mail.com';
  t_emails(7) := 'a@mail.com';
  t_emails(8) := 'a@mail.com';
  t_emails(9) := 'b@mail.com';
  t_emails(10) := 'b@mail.com';
  t_emails(11) := 'c@mail.com';

  -- 遍历统计次数
  FOR i IN t_emails.FIRST .. t_emails.LAST LOOP
    v_email := t_emails(i);
    IF t_email_counts.EXISTS(v_email) THEN
      t_email_counts(v_email) := t_email_counts(v_email) + 1;
    ELSE
      t_email_counts(v_email) := 1;
    END IF;
  END LOOP;

  -- 输出结果
  DBMS_OUTPUT.PUT_LINE('| Email       | Number |');
  DBMS_OUTPUT.PUT_LINE('|-------------|--------|');
  v_email := t_email_counts.FIRST;
  WHILE v_email IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('| ' || RPAD(v_email, 12) || ' | ' || LPAD(t_email_counts(v_email), 6) || ' |');
    v_email := t_email_counts.NEXT(v_email);
  END LOOP;
END;
/

方案二：转换为SQL可识别集合后查询 #

如果希望用SQL语句统计，需要将PL/SQL局部类型转换为SQL引擎可识别的集合类型，有两种方式：

方式1：创建SQL级别的集合类型 #

先在SQL层定义类型，之后即可直接用TABLE()函数查询：

-- 创建SQL级类型
CREATE OR REPLACE TYPE t_email_type AS TABLE OF VARCHAR2(100);
/

然后在PL/SQL中使用：

DECLARE
  t_emails t_email_type := t_email_type();
BEGIN
  -- 模拟数据填充
  t_emails.extend(11);
  t_emails(1) := 'a@mail.com';
  t_emails(2) := 'b@mail.com';
  t_emails(3) := 'c@mail.com';
  t_emails(4) := 'd@mail.com';
  t_emails(5) := 'c@mail.com';
  t_emails(6) := 'c@mail.com';
  t_emails(7) := 'a@mail.com';
  t_emails(8) := 'a@mail.com';
  t_emails(9) := 'b@mail.com';
  t_emails(10) := 'b@mail.com';
  t_emails(11) := 'c@mail.com';

  -- 用SQL分组统计
  FOR rec IN (
    SELECT column_value AS email, COUNT(*) AS number_of_occurrences
    FROM TABLE(t_emails)
    GROUP BY column_value
    ORDER BY email
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('| ' || rec.email || ' | ' || rec.number_of_occurrences || ' |');
  END LOOP;
END;
/

方式2：转换为Oracle系统内置集合类型 #

无需自定义SQL类型，将PL/SQL集合转换为SYS.ODCIVARCHAR2LIST后查询：

DECLARE
  TYPE t_email_type IS TABLE OF VARCHAR2(100);
  t_emails t_email_type := t_email_type();
BEGIN
  -- 模拟数据填充
  t_emails.extend(11);
  t_emails(1) := 'a@mail.com';
  t_emails(2) := 'b@mail.com';
  t_emails(3) := 'c@mail.com';
  t_emails(4) := 'd@mail.com';
  t_emails(5) := 'c@mail.com';
  t_emails(6) := 'c@mail.com';
  t_emails(7) := 'a@mail.com';
  t_emails(8) := 'a@mail.com';
  t_emails(9) := 'b@mail.com';
  t_emails(10) := 'b@mail.com';
  t_emails(11) := 'c@mail.com';

  -- 转换为系统集合后执行SQL统计
  FOR rec IN (
    SELECT column_value AS email, COUNT(*) AS number_of_occurrences
    FROM TABLE(CAST(t_emails AS SYS.ODCIVARCHAR2LIST))
    GROUP BY column_value
    ORDER BY email
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('| ' || rec.email || ' | ' || rec.number_of_occurrences || ' |');
  END LOOP;
END;
/

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