Nested Table Multiset Operations

The essential advance made in collections starting with Oracle Database 10g is that the database treats nested tables more like the multisets that they actually are. The database provides high-level set operations that can be applied to nested tables and only, for the time being, to nested tables. Here is a brief summary of these set-level capabilities:

Operation

Return value

Description

=

BOOLEAN

Compares two nested tables, and returns TRUE if they have the same named type and cardinality and if the elements are equal.

<> or !=

BOOLEAN

Compares two nested tables, and returns FALSE if they differ in named type, cardinality, or equality of elements.

[NOT] IN ()

BOOLEAN

Returns TRUE [FALSE] if the nested table to the left of IN exists in the list of nested tables in the parentheses.

x MULTISET EXCEPT [DISTINCT] y

NESTED TABLE

Performs a MINUS set operation on nested tables x and y, returning a nested table whose elements are in x, but not in y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword instructs Oracle to eliminate any element in x which is also in y, regardless of the number of occurrences.

x MULTISET INTERSECT [DISTINCT] y

NESTED TABLE

Performs an INTERSECT set operation on nested tables x and y, returning a nested table whose elements are in both x and y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table, including ...

Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.