Thursday, September 28, 2017

Avoiding reinventing the wheel - use MULTISET EXCEPT to get set relative complement

Today in an application I stumbled upon a PL/SQL function created to return those elements of one nested table that did not exist in another nested table. Not a huge function, but still a bit of work and some thinking that had been done some while ago to compare the elements and create the desired output nested table.

The trouble is, that this functionality already natively exists in PL/SQL (and SQL) - the developer had actually reinvented the wheel. In set theory this is called a relative complement and PL/SQL and SQL has operator MULTISET EXCEPT to perform this set operation.

So just as a warning to all that you should really be aware of the built-in functionality of any programming language, I'll show you this function and how I replaced it.