Operate on Collections
Quiz yourself about set operations on PL/SQL collections.
By Steven Feuerstein
July/August 2016
We all know and love SQL for its high-level set operations; there’s no need to write a program to iterate through rows to do grouping, ordering, unions, intersections, and so on. Just describe the data you want and let the Oracle Database SQL engine do all the heavy lifting for you.
And then there’s PL/SQL: a procedural language designed to perform if-then-else logic that SQL cannot easily handle and to build powerful APIs on top of your data structures. So this means that you can’t perform set operations on PL/SQL collections, right? Wrong!
One of the three types of PL/SQL collections is the nested table. One of the most delightful features of nested tables is the ability to use MULTISET operators on them to perform unions, intersections, minuses, and more—completely outside of any SQL statement!
The quizzes in this column introduce you to some of the MULTISET-related functionality for nested tables.
I create the following package specification for use in all three quizzes:
CREATE OR REPLACE PACKAGE plch_pkg IS /* "IS TABLE" without "INDEX BY" means: nested table! */ TYPE numbers_t IS TABLE OF NUMBER; /* Put values in each collection using the construction function */ primes numbers_t := numbers_t (3, 11, 5, 7, 11); under_ten numbers_t := numbers_t (4, 5, 8, 6, 7); lotsa_dups numbers_t := numbers_t (1, 1, 2, 2, 3, 3); no_dups numbers_t := numbers_t (1, 2, 3); END; /
Question 1
MULTISET Union
One really nice aspect of object types is that PL/SQL automatically creates a constructor function with the same name as the type, which you can use to initialize an instance of an object type.
Which of the following choices displays “7” after execution?
a.
DECLARE
l_numbers plch_pkg.numbers_t;
BEGIN
l_numbers :=
plch_pkg.primes MULTISET UNION ALL plch_pkg.under_ten;
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
b.
DECLARE
l_numbers plch_pkg.numbers_t;
BEGIN
l_numbers :=
plch_pkg.primes MULTISET UNION DISTINCT plch_pkg.under_ten;
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
c.
DECLARE
l_numbers plch_pkg.numbers_t;
BEGIN
l_numbers :=
plch_pkg.primes MULTISET UNION plch_pkg.under_ten;
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
Quiz Summary
MULTISET UNION (and the other variations) works differently from SQL’s UNION. In SQL, unless you include ALL, duplicates are removed. With MULTISET UNION, unless you include DISTINCT, duplicates are retained.
I should also point out that although you can use MULTISET inside PL/SQL code, the exact same functionality is available within SQL. That makes sense, given that you can create relational tables that have columns whose datatypes are nested table types.
Read more about multiset operators.
Question 2
The SET Function
Mathematically speaking, a multiset differs from a set in two ways: a multiset may contain duplicates, and the order of elements in it does not matter. Kind of sounds like a relational table, right? It should—relational tables are multisets, too.
Although it can be useful to allow duplicates in a multiset, it is also very helpful to be able to easily and quickly get rid of those duplicates.
Which of the following choices displays “3” after execution?
a.
DECLARE l_numbers plch_pkg.numbers_t; BEGIN l_numbers := SET (plch_pkg.lotsa_dups); DBMS_OUTPUT.put_line (l_numbers.COUNT); END; /
b.
DECLARE
l_numbers plch_pkg.numbers_t;
BEGIN
l_numbers :=
plch_pkg.lotsa_dups MULTISET INTERSECT
plch_pkg.lotsa_dups;
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
c.
DECLARE
l_numbers plch_pkg.numbers_t;
BEGIN
l_numbers :=
plch_pkg.lotsa_dups MULTISET EXCEPT
plch_pkg.lotsa_dups;
DBMS_OUTPUT.put_line (l_numbers.COUNT);
END;
/
Quiz Summary
When working with nested tables, you have a basic decision to make: are duplicates OK? If not, make sure you include DISTINCT in your MULTISET operations and use SET to quickly and simply remove any duplicates.
Read more about SET.
Question 3
SUBMULTISET
I have two nested tables in my PL/SQL program and need to figure out if one collection is entirely contained within the other. Sure, I could write a program involving nested loops and comparisons and so forth. But do I really need to do that?
Which of the following choices displays “Contained” after execution?
a.
BEGIN
IF plch_pkg.lotsa_dups SUBMULTISET OF plch_pkg.no_dups
THEN
DBMS_OUTPUT.put_line ('Contained');
ELSE
DBMS_OUTPUT.put_line ('Escaped');
END IF;
END;
/
b.
BEGIN
IF plch_pkg.no_dups SUBMULTISET OF plch_pkg.lotsa_dups
THEN
DBMS_OUTPUT.put_line ('Contained');
ELSE
DBMS_OUTPUT.put_line ('Escaped');
END IF;
END;
/
c.
DECLARE
l_numbers plch_pkg.numbers_t
:= plch_pkg.lotsa_dups MULTISET EXCEPT
plch_pkg.no_dups;
BEGIN
IF l_numbers.COUNT = 0
THEN
DBMS_OUTPUT.put_line ('Contained');
ELSE
DBMS_OUTPUT.put_line ('Escaped');
END IF;
END;
/
Quiz Summary
SUBMULTISET offers a great reminder: if you are ever writing a program and thinking, “Gee, it really seems like Oracle Database should do this for me,” stop coding. There is a very good chance Oracle Database does contain a feature that will help you. So do your research, and avoid reinventing the wheel!
Read more about SUBMULTISET.
Next Steps
|
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.