Oracle 10g PLSQL New Features (Part 4) - Assigning To PLSQL Nested Tables

Oracle 10g PLSQL new features part 1 covered the use of regular expressions in PLSQL. Part 2 showed us how to use the ANSI multiset and set operators to compare nested tables and part 3 demonstrated the use of set operators to determine the properties of a nested table collection. In this article we'll look at how to assign to (i.e. populate) nested tables using the following operators: MULTISET UNION [DISTINCT], MULTISET INTERSECT [DISTINCT], MULTISET EXCEPT [DISTINCT] and SET.

How To Assign To Oracle 10g PLSQL Collections Using Set Operators

Using MULTISET UNION [DISTINCT] The DISTINCT keyword is optional and without it the MULTISET UNION operator acts on plsql collections in a similar way to the UNION ALL operator in SQL. The result in PL/SQL is a nested table collection comprising all the members of either

collection, including any duplicates. Adding the DISTINCT keyword results in the removal of duplicates, making MULTISET UNION DISTINCT the equivalent of UNION in SQL.

Using MULTISET INTERSECT [DISTINCT]

This is similar to the INTERSECT operator in SQL, however in PLSQL, the resulting collection includes duplicates if the DISTINCT key word is not specified.

Using MULTISET EXCEPT [DISTINCT]

MULTISET EXCEPT  acts in a similar manner as the SQL statement clause MINUS. In PLSQL however, the resulting collection includes duplicates if the DISTINCT key word is not specified.

Using SET

The SET operator is a unary operator that serves to remove duplicates from a collection and therefore turn it into a set.

Examples of using these operators

Let's put all of this together into one PL/SQL script to demonstrate how they work.

declare
type nested_table is table of number;
table_1 nested_table := nested_table(1,3,6,4,4,3);
table_2 nested_table := nested_table(1,2,3,1,4,4);
table_3 nested_table ;
num                  NUMBER :=3;

    PROCEDURE display_members (operation varchar2) IS

        members VARCHAR2(2000);
        prefix CHAR;

    BEGIN

        for i in table_3.first .. table_3.last loop
            members := members||prefix||table_3(i);
            prefix := ',';
        end loop;

        dbms_output.put_line('result of table_1 '||operation||' table_2 is '||members);

    END display_members;
   
begin

    table_3 := table_1 multiset union table_2;
    display_members('multiset union');

    table_3 := table_1 multiset union DISTINCT table_2;
    display_members('multiset union distinct');

    table_3 := table_1 multiset intersect table_2;
    display_members('multiset intersect');

    table_3 := table_1 multiset intersect DISTINCT table_2;
    display_members('multiset intersect distinct');
   
    table_3 := table_1 multiset except table_2;
    display_members('multiset except');

    table_3 := table_1 multiset except DISTINCT table_2;
    display_members('multiset except distinct');

    table_3 := set (table_2); -- remove duplicates
       
end;

Results

Running this Oracle 10g plsql anonymous block produces the following results:

1. result of table_1 multiset union table_2 is 1,3,6,4,4,3,1,2,3,1,4,4 (note the duplicates).
2. result of table_1 multiset union distinct table_2 is 1,3,6,4,2 (no duplicates this time).
3. result of table_1 multiset intersect table_2 is 1,3,4,4 (includes duplicates).
4. result of table_1 multiset intersect distinct table_2 is 1,3,4 (no duplicates).
5. result of table_1 multiset except table_2 is 6,3 (only one 3 in table_2, but two in table_1)
6. result of table_1 multiset except distinct table_2 is 6 (duplicates removed)
7. result of  set (table_2) is 1,2,3,4  (all duplicates removed).