A little known, but very handy feature of PL/SQL is the ability to apply set operators, like union, intersect and minus, to nested tables. I can, for example, find all the elements common to two lists of strings with nothing more than the following use of MULTISET INTERSECT:
DECLARE
TYPE nested_type IS TABLE OF NUMBER;
nt1 nested_type := nested_type (1, 2, 3, 4, 5);
nt2 nested_type := nested_type (-1, -77, 3, 2, 1);
comment_set nested_type;
BEGIN
comment_set := nt1 MULTISET INTERSECT nt2;
END;
I had always believed that this feature was only supported for nested tables of scalars, such as strings, numbers and dates.
A recent email from a developer forced me to confront the fact that I surely do not know everything there is to know about PL/SQL.
This developer was using MULTISET UNION with nested tables of records and found that it worked just fine. Wow!
Further investigation revealed that MULTISET operations (along with equality checks) can even be used with multi-level collections, as in:
CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/
CREATE OR REPLACE TYPE num_numbers_t IS TABLE OF numbers_t
/
DECLARE
n1 num_numbers_t :=
num_numbers_t (numbers_t (1), numbers_t (2));
n2 num_numbers_t :=
num_numbers_t (numbers_t (1), numbers_t (2));
n3 num_numbers_t;
BEGIN
IF n1 = n2
THEN
DBMS_OUTPUT.put_line ('=');
END IF;
n3 := n1 MULTISET UNION n2;
n3 := n1 MULTISET INTERSECT n2;
n3 := n1 MULTISET EXCEPT n2;
END;
/
I have not yet completed my investigations to identify the limits of this feature, but there are clearly some limits. For example, this same developer found that an attempt to use MULTSET EXCEPT (a.k.a., "minus") raised this error:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
So hopefully this news has opened your eyes a little bit wider about what is possible in PL/SQL. Look for opportunities to apply MULTISET in your programs. It can replace an awful lot of lines of code.