How can I insert an element into a nested table only if it doesn't exist?

I want to have a nested table holding custom objects, by adding them one by one from multiple cursors. But I don't want to have duplicates in the table. How can I achieve this?

Here is how I add elements to the table:

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;

Note that this is just an example, and in this par开发者_如何学Goticular case the selects could be changed in order to have just one cursor. But in my application things are different. Thanks!

---

If you are using Oracle 10g or higher there are a couple of easy ways to solve this.

One would be to use your existing code, and in the OPERATORS loop use the MEMBER OF operator to test whether it already exists in the collection.

However, a simpler approach would be to define three collections. Use bulk collect to populate two collections with the complete set of records from each query, and then use the third collection to filter out any duplicates.

Soemthing like this:

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;
/

---

"Is it possible to have multiple unions? "

It certain is possible. I don't know whether there is a maximum but if there is it will be a high number. I have found that limits in Oracle tend to be on the generous side; if we find ourselves butting against the limits there's probably a better way of doing whatever it is.