Edi Yanto (何 萬 新)

Assigning Nested Tables with Set Operators

Posted on: May 16, 2007


The SQL language has long offered the ability to apply set operations (UNION, INTERSECT, and MINUS) to the result sets of queries. In Oracle Database 10g, you can now use those same high-level, very powerful operators against nested tables (and only nested tables) in your PL/SQL programs and on nested tables declared as columns inside relational tables.

Nested Table Function:

  • MULTISET UNION, all elements in both nested table x and nested table y are returned. If tables x and y are both empty then the returned table will be empty.
  • MULTISET INTERSECT, elements in nested table x that also appear in nested table y are returned. If tables x and y have no common elements the returned table will be empty.
  • MULTISET EXCEPT, elements in nested table x that do not appear in nested table y are returned. If table x has no elements that are not in table y the returned table will be empty.
  • SUBMULTISET OF and NOT SUBMULTISET OF compare two nested tables to determine if all elements in the first nested table also appear in second nested table. This function returns a Boolean value TRUE, FALSE or NULL. The key word OF is optional.

The DISTINCT keyword in nested table function (MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT) eliminates duplicate elements from the results. Nested table x, y and the returned table must all be of the same type. If either x or y is null, then the returned table is null.

nested_table_function.jpg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

May 2007
M T W T F S S
« Apr   Jun »
 123456
78910111213
14151617181920
21222324252627
28293031  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: