Subscribe

Share

Database, SQL and PL/SQL

Operate on Collections

Quiz yourself about set operations on PL/SQL collections.

By Steven Feuerstein Oracle ACE Director

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

 TEST your SQL and PL/SQL knowledge.

 WATCH Feuerstein’s “Practically Perfect PL/SQL” videos.

 MEET the Oracle Developer Advocates team.

 

Photography by Ricardo Gomez, Unsplash