logo for asktheoracle.net
leftimage for asktheoracle.net

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.

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.

An example of using these operators

We've put all of this together into the following PL/SQL script.

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)

Need help with your Oracle systems? Follow the links for Oracle training and consultancy in the UK 

Sign up to our newsletter to ensure you don't miss out on new articles on Oracle 10g PLSQL and SQL and on acquiring the knowledge you need to do your job better, faster and smarter.