我试着做你描述到目前为止;给你：
包含电子邮件地址的表（其中某些地址重复）：

SQL> select * from test_mails;

EMAIL
----------
a@mail.com
b@mail.com
c@mail.com
a@mail.com
b@mail.com

您创建的类型;我 * 认为 * 您在PL/SQL过程中使用了类型。如果它是一个应该返回该类型结果的函数，那么它将不起作用，因为它必须在SQL级别创建，所以-这就是我正在做的：

SQL> create or replace type t_email_type is table of varchar2(100);
  2  /

Type created.

函数：FOR循环从表中选择电子邮件地址，并将其放入t_emails。您感兴趣的是第12 -14行中的内容，它显示了如何返回结果：

SQL> create or replace function f_test
  2    return t_email_type
  3  is
  4    t_emails t_email_type := t_email_type();
  5    retval t_email_type;
  6  begin
  7    for user_r in (select email from test_mails) loop
  8      t_emails.extend;
  9      t_emails(t_emails.last) := user_r.email;
 10    end loop;
 11
 12    select distinct column_value
 13      bulk collect into retval
 14      from table(t_emails);
 15    return retval;
 16  end;
 17  /

Function created.

好，让我们来测试一下：

SQL> select * from table(f_test);

COLUMN_VALUE
--------------------------------------------------------------------------------
a@mail.com
b@mail.com
c@mail.com

SQL>

不同的地址;这是你要的

试试看：

SET SERVEROUTPUT ON
Create or replace type t_email_type is table of varchar2(100);
/
Declare     --  Outer block - just to populate variable emails of type t_email_type
    Cursor c_emails IS          
        Select 'a@mail.com' "EMAIL" From Dual Union All
        Select 'b@mail.com' "EMAIL" From Dual Union All
        Select 'c@mail.com' "EMAIL" From Dual Union All
        Select 'd@mail.com' "EMAIL" From Dual Union All
        Select 'c@mail.com' "EMAIL" From Dual Union All
        Select 'c@mail.com' "EMAIL" From Dual Union All
        Select 'a@mail.com' "EMAIL" From Dual Union All
        Select 'a@mail.com' "EMAIL" From Dual Union All
        Select 'b@mail.com' "EMAIL" From Dual Union All
        Select 'b@mail.com' "EMAIL" From Dual Union All
        Select 'c@mail.com' "EMAIL" From Dual;
    email     VarChar2(100);
    emails    t_email_type := t_email_type();
    i         Number(3) := 0;
Begin
    OPEN c_emails;
    LOOP
        FETCH c_emails Into email;
        EXIT WHEN c_emails%NOTFOUND;
        i := i + 1;
        emails.extend;
        emails(i) := email;
    END LOOP;
    CLOSE c_emails;
    --  Inner block - get distinct emails and number of appearances from variable emails of type t_email_type
    Declare
        Cursor c Is
            Select COLUMN_VALUE "EMAIL", Count(*) "NUM_OF_APPEARANCES"
            From    TABLE(emails)
            Group By COLUMN_VALUE
            Order By COLUMN_VALUE;
            cSet    c%ROWTYPE;
            i       Number(3) := 0;
    Begin
        OPEN c;
        LOOP
            FETCH c Into cSet;
            EXIT WHEN c%NOTFOUND;
            i := i + 1;
            If i = 1 Then
                DBMS_OUTPUT.PUT_LINE(RPAD('EMAIL', 20, ' ') || ' ' || LPAD('NUM_OF_APPEARANCES', 20, ' '));
                DBMS_OUTPUT.PUT_LINE(RPAD('-', 20, '-') || ' ' || LPAD('-', 20, '-'));
            End If;
            DBMS_OUTPUT.PUT_LINE(RPAD(cSet.EMAIL, 20, ' ') || ' ' || LPAD(cSet.NUM_OF_APPEARANCES, 20, ' '));
        END LOOP;
        CLOSE c;
    End;
End;
/*  R e s u l t :
anonymous block completed
EMAIL                  NUM_OF_APPEARANCES
-------------------- --------------------
a@mail.com                              3
b@mail.com                              3
c@mail.com                              4
d@mail.com                              1
*/

这段代码的外部代码块只是用来生成数据并将其插入到您定义的类型（t_email_type）的电子邮件变量中。您可能只需要内部代码块就可以获得在表类型中出现次数的电子邮件列表。
提供预期结果的SQL实际上是内部块中的游标：

Select COLUMN_VALUE "EMAIL", Count(*) "NUM_OF_APPEARANCES"
From    TABLE(emails)
Group By COLUMN_VALUE
Order By COLUMN_VALUE;

结果：
| 电子邮件|出现次数|
| - -|- -|
| a@mail.com | 三个|
| b@mail.com | 三个|
| c@mail.com | 四个|
| d@mail.com | 一个|
此致