Oracle 10g PLSQL New Features (Part 2)

Overall in Oracle 10g PLSQL there are a number of new features that improve the application development environment. We briefly discussed PLSQL regular expressions and compiler enhancements in part 1, so now let's examine the enhancements to PL/SQL collections.

Both Oracle 10g PLSQL and SQL support the ANSI multiset operators enabling nested tables to be compared for equality (=) and inequality (!= or <>) and with set operators [NOT] IN, [NOT] MEMBER [OF] and [NOT] SUBMULTISET.

Various properties of a nested table collection can also be determined using the set operators CARDINALITY, IS [NOT] A SET, IS [NOT] EMPTY.

Finally, you can assign nested tables using the set operators MULTISET UNION [DISTINCT], MULTISET INTERSECT [DISTINCT], MULTISET EXCEPT [DISTINCT] and SET.

Let's look at some examples to see how these work.

declare

    type nested_table is table of number;

    tab1 nested_table := nested_table();
    tab2 nested_table := nested_table();
    tab3 nested_table := nested_table(1);
    tab4 nested_table;

begin

    if tab1 = tab2 then
    else dbms_output.put_line('tab1 differs from tab2');
    end if;
   
    if tab1 = tab3 then
    else dbms_output.put_line('tab1 differs from tab3');
    end if;
   
    if tab1 = tab4 then
    else dbms_output.put_line('tab1 differs from tab4');
    end if;
   
end;

In the above example we compare two nested tables using "=" as if they were simple variables. Running this example produces the following results:-

tab1 is equal to tab2
tab1 differs from tab3
tab1 differs from tab4

This shows - as you would expect - that two empty nested tables match (tab1 and tab2) and that an empty table is not the same as a table which has been populated. The example also shows that a null table (tab4) is not the same as an empty table. We can reverse the equlaity test by replacing "=" with "!=" or "<>"

The IN operator works on nested tables in Oracle 10g PLSQL in a similar way to how it works with other types of variables. In this case it checks whether the nested table on the left of the operator matches any of the nested tables on the right of the operator.

There are a couple of points to note when using this operator with nested tables:

• there has to be an exact match, not a match as a subset
• to build a nested table at runtime for comparison you need to use the constructor method (type name) - you can't compare number constants or number variables as shown in the last 2 statements in the example

declare

type nested_table is table of number;

table_1 nested_table := nested_table(1);
table_2 nested_table := nested_table(1,2,3);
table_3 nested_table := nested_table(1);
num                  NUMBER := 3;

begin

    if table_1 in (table_2) then
        dbms_output.put_line('table_1 in table_2');
    else dbms_output.put_line('table_1 not in table_2');
    end if;

    if table_3 not in (table_2) then
        dbms_output.put_line('table_3 not in table_2');
    else dbms_output.put_line('table_3 in table_2');
    end if;

    if table_3 in (table_1,table_2) then
        dbms_output.put_line('table_3 in table 1 or 2');
    else dbms_output.put_line('table_3 not in table 1 or 2');
 end if;

/*  -- can't do this
    if 3 not in (table_2) then
        dbms_output.put_line('3 not in table_2');
    else dbms_output.put_line('3 in table_2');
    end if;
*/

/*  -- can't do this
    if num not in (table_2) then
        dbms_output.put_line('3 not in table_2');
    else dbms_output.put_line('3 in table_2');
    end if;
*/

end;

Example 2 produces the following results:

table_1 not in table_2
table_3 not in table_2
table_3 in table 1 or 2

The "MEMBER OF" operator is used in Oracle 10g PLSQL to determine whether or not the specificied contsant or variable is in a nested table collection.

Note that you can't use this to determine if a nested table is a subset of another.

declare

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

begin

    if 1 member of table_2 then
        dbms_output.put_line('1 is a member of table_2');
    else dbms_output.put_line('1 not member of table_2');
    end if;

    if num not member of table_2 then
        dbms_output.put_line('num not a member of table_2');
    else dbms_output.put_line('num is a member of table_2');
    end if;

/*  can't do this
    if table_1 member of table_2 then
        dbms_output.put_line('table_1 member of table_2');
    else dbms_output.put_line('table_1 not member of table_2');
    end if;
*/   

end;

Example 3 produces the following results:

1 is a member of table_2
num is a member of table_2

The submultiset operator in Oracle 10g PLSQL enables you to determine whether or not one nested table is a subset of another nested table.

declare

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

begin

    if table_1 submultiset table_2 then
        dbms_output.put_line('table_1 subset of table_2');
    else dbms_output.put_line('table_1 not subset of table_2');
    end if;

    if table_2 not submultiset table_3 then
        dbms_output.put_line('table_2 not subset of table_3');
    else dbms_output.put_line('table_2 is subset of table_3');
    end if;

/* --can't do this
  if 3 not submultiset table_2 then
        dbms_output.put_line('table_2 does not contain 3');
     else dbms_output.put_line('table_3 includes 3');
     end if;
*/

end;

Example 4 produces the following results:

table_1 subset of table_2
table_2 not subset of table_3