Nested Table Enhancements

This entry is part 3 of 13 in the series New Pl/Sql features 10g

Prior to Oracle 10g tasks like comparing 2 nested tables for equality were code intensive and had to be performed at element level. With 10g new functionality was introduced that will finally treat nested tables as the sets they are:

  • compare nested tables for equality
  • test whether an element is a member of a nested table
  • test whether one nested table is a subset of another
  • perform set operations such as union and intersection

The table displayed contains a complete overview of all operators.

Operator Description Syntax Example
= True if two nested tables are exactly equal (number of elements, datatype and/or values). IF my_list = my_other_list THEN…
<>, != True if two nested tables are not exactly equal (difference in number of elements, datatype and/or values). IF my_list != my_other_list THEN…
ntt IS [NOT] A SET True if all elements in a nested table are unique values answer := my_list IS A SET
ntt IS [NOT] EMPTY True if the nested table is empty (has no elements) answer := my_list IS EMPTY;
expr [NOT] MEMBER [OF] ntt True if a value exists within any of the elements of a nested table answer := ‘book’ MEMBER OF my_list;
ntt1 [NOT] SUBMULTISET [OF] ntt2 true if all elements of ntt1 are at least in ntt2. Ntt2 can have more elements that do not exist in ntt1. answer := my_list SUBMULTISET your_list;
ntt1 [NOT] in (ntt2,ntt3,…) Test if ntt1 is equal to any of the nested tables in the list between ( ). answer := my_list IN (his_list, her_list)
nnt1 MULTISET EXCEPT [DISTINCT] ntt2 Performs a minus between 2 nested tables just like a table minus in SQL. Adding the DISTINCT keyword flushes out any remaining duplicates in the result set. my_list := my_list MULTISET EXCEPT my_other_list
ntt1 MULTISET INTERSECT [DISTINCT] ntt2 Performs an intersect on 2 nested tables just like an intersect in SQL. Adding the DISTINCT keyword flushes out any remaining duplicates in the result set. my_list := my_list MULTISET INTERSECT my_other_list
ntt1 MULTISET UNION [DISTINCT] ntt2 Performs a union between 2 nested tables just like a table union in SQL. Adding the DISTINCT keyword performs like a union all in SQL. my_list := my_list MULTISET UNION my_other_list;
SET(ntt) Returns a set with only the distinct values of the original set (basically a select distinct) my_list := SET(my_other_list);

NOTE: Since Oracle does not retain ordering and subscripts when a nested table is stored in / retrieved fromĀ  the database. All comparison between nested tables behave according to this. The order of elements and subscript are always ignored when comparison are made. So list(1,2,3) is equal to list(3,2,1)

Equality Example

NB: The last comparison shows that, as usual, a comparison with NULL always lead to “not equal”

Adding/Substracting Example

Checking the elements example

Series Navigation<< Implicit CLOB/NCLOB ConversionNew IEEE Floating-Point Types >>

Posted in Pl/Sql

Leave a Reply