O'Reilly logo

Oracle PL/SQL Programming, 5th Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required