仅当元素不存在时，如何将其插入到嵌套表中？

Question

我想要一个包含自定义对象的嵌套表，通过从多个游标中一一添加它们。但我不想在表中出现重复项。我怎样才能实现这个目标？

以下是我向表中添加元素的方法：

create type recipient as object (firstname varchar2, lastname varchar2, email varchar2);

declare type recipients_list is table of recipient;
rec recipients_list := recipients_list();

cursor admins is
    select firstname, lastname, email
    from users
    where profile_id = 1;

cursor operators is
    select firstname, lastname, email
    from users
    where operator = 1;

-- an user may be both admin and operator

....

for to_email in admins 
loop
    rec.extend;
    rec(rec.last) := recipient(to_email.firstname, to_email.lastname, to_email.email);
end loop;

for to_email in operators 
loop
    rec.extend;
    rec(rec.last) := recipient(to_email.firstname, to_email.lastname, to_email.email);
end loop;

请注意，这只是一个示例，在这种特殊情况下，可以更改选择以便只有一个光标。但在我的应用程序中情况有所不同。谢谢！

Answer 1

如果您使用的是 Oracle 10g 或更高版本，有几种简单的方法可以解决此问题。

一种是使用现有代码，并在 OPERATORS 循环中使用 MEMBER OF 运算符来测试集合中是否已存在。

然而，更简单的方法是定义三个集合。使用批量收集用每个查询中的完整记录集填充两个集合，然后使用第三个集合过滤掉任何重复项。

像这样的事情：

declare 

    type recipients_list is table of recipient;
    recs recipients_list := recipients_list();
    admins recipients_list := recipients_list();
    operators recipients_list := recipients_list();

begin

    select firstname, lastname, email
    bulk collect into admins
    from users
    where profile_id = 1;

    select firstname, lastname, email
    bulk collect into operators
    from users
    where operator = 1;

    recs := admins multiset union distinct operators;

end;
/

---

“是否可以有多个联合？”

这肯定是有可能的。我不知道是否有上限，但如果有的话，那将会是一个很大的数字。我发现 Oracle 中的限制往往比较慷慨；如果我们发现自己正在突破极限，那么无论做什么，都可能有更好的方法。