PL/SQL is a procedural language. Hence, its name: Procedural Language (extensions to) SQL.
It has if statements and all sorts of loops. You can start and stop things, go to a different location in your code, etc. The bottom line is that you construct an algorithm (set of steps) that the runtime engine must move through to accomplish its task.
SQL, on the other hand, takes a very different approach, one that is at the very heart of its incredible success: you describe the result you are interested in (the data you want to see or how you want the data to change), and the runtime engine figures out how to get the job done.
That makes SQL declarative. But its power comes from more than that. It is also set-oriented, which means that you think about and manipulate data as sets, not discrete pieces of data. It also means that SQL is based on a solid mathematical foundation, again a reason for its longevity as the most popular data management language in the history of the known universe.
A long, long time ago, it was thought that SQL was an end-user language and that nothing more was needed. I can still remember my first demonstrations of SQL statements as an Oracle pre-sales consultant, showing everyone just how easy it was and how “anyone” could do it.
Well.
That didn’t last very long. Applications were being written in C around SQL. It was clear that more was needed. It also became clear, as the demands on SQL grew, and its feature set grew, and the challenges it was expected to meet grew, that while maybe some SQL was suitable for some end users, most of it was not.
And so PL/SQL was added to the Oracle Database toolset so that developers (most definitely not end-users) could implement complex, often procedural logic in a language that was portable to any operating system running the Oracle Database.
But that does not take anything away from the fundamental awesomeness of set-based, declarative problem-solving.
Now, generally, it’s been thought that the SQL (set, declarative) and PL/SQL (row-by-row, procedural) have separate domains and never the two will meet (except for, of course, being able to write SQL natively inside a PL/SQL block).
But those lines have been blurring for years. SQL, for example, got a case expression for conditional logic and, most wonderfully, the with clause (aka, common table expression or CTE) allowing top-down design of complex SQL and code “reuse” within a SQL statement. And then of course there is the SQL macro feature added in Oracle Database 21c.
But hey, this is Steven here. The guy who knows a lot about PL/SQL and not much about anything else. So enough about SQL. I am much better equipped to talk about the way that declarative and set-based features have been creeping into PL/SQL.
Nested Table Multisets
Nested tables are one of the three types of collections in PL/SQL, along with associative arrays (aka, index-by tables) and varying arrays (varrays). Sure, each of these three types has its own distinct characteristics, but the nested table rises above the others when it comes to set-based processing.
That’s because nested tables are multisets. A multiset is a collection of data that has no inherent ordering or index, and may contain duplicates. You are very familiar with multisets, of that, I am sure….because relational tables are multisets!
[Note: in theory, a multiset has no inherent ordering, but we cheat a little bit on that score with nested tables. You can assign elements to specific indexes in a nested table. You can access elements by that index, iterate by that index, and so on. It’s OK, you can do that. I’ve done it. But you are really cooking with gas, as the saying goes, when you start to use the true set-oriented features of nested tables, moving beyond index-based operations (which are, for example, the only way to work with associative arrays).]
And what can you do with relational tables? Among other things, you can perform set operations on them, like union, intersect, and minus. Well, you can do the same thing with nested tables!
Right inside your PL/SQL code, you can apply set operations to nested tables, using variations on the SQL them:
multiset union
multiset intersect
multiset except (for some reason, the keyword “minus” was not to be used)
There are some differences between the SQL and PL/SQL versions: for example, a SQL union automatically removes duplicates (add all to keep them). But the multiset union preserves duplicates (add distinct to remove them).
Here’s an example of multiset union to give you an idea of how to use these features:
- First, I create a schema-level nested table of strings:
create or replace type strings_nt is table of varchar2(1000)
- Then I create a package specification that populates two collections of that type with the names of some of the favorite authors of mine and my wife’s. Notice the duplicates. I add a little helper procedure to display the contents of a collection (gee, it would be nice if that could be built into PL/SQL!).
create or replace package authors_pkg
is
steven_authors strings_nt := strings_nt(
'ROBIN HOBB',
'ROBERT HARRIS',
'ROBERT HARRIS',
'DAVID BRIN',
'DAVID BRIN',
'SHERI S. TEPPER',
'CHRISTOPHER ALEXANDER'
);
veva_authors strings_nt := strings_nt(
'ROBIN HOBB',
'SHERI S. TEPPER',
'ANNE MCCAFFREY',
'DAVID BRIN',
'DAVID BRIN'
);
procedure show_authors (
title_in in varchar2,
authors_in in strings_nt
);
end;
/
- The package body implements the display procedures. Notice that it assumes the collection is densely filled (every index value between 1 and the count is defined). This is a valid assumption when working with multiset. The result of the operation will always be either an empty collection or one that is populated starting at index 1 and then sequentially filled.
create or replace package body authors_pkg
is
procedure show_authors (
title_in in varchar2,
authors_in in strings_nt
)
is
begin
DBMS_OUTPUT.put_line(title_in);
for indx in 1..authors_in.COUNT loop
DBMS_OUTPUT.put_line(indx
|| ' = '
|| authors_in(indx));
end loop;
end show_authors;
end;
/
- And now a block to exercise the code.
declare
our_authors strings_nt;
begin
our_authors := authors_pkg.steven_authors
multiset union
authors_pkg.veva_authors;
authors_pkg.show_authors('Steven and Veva', our_authors);
DBMS_OUTPUT.put_line('Union inside SQL');
for rec in (
select COLUMN_VALUE
from table ( authors_pkg.veva_authors
multiset union authors_pkg.steven_authors )
order by COLUMN_VALUE
) loop
DBMS_OUTPUT.put_line(rec.COLUMN_VALUE);
end loop;
our_authors := authors_pkg.steven_authors
multiset union distinct authors_pkg.veva_authors;
authors_pkg.show_authors('Steven then Veva with DISTINCT', our_authors);
end;
/
- Here’s the output you’ll see:
Steven and Veva 1 = ROBIN HOBB 2 = ROBERT HARRIS 3 = ROBERT HARRIS 4 = DAVID BRIN 5 = DAVID BRIN 6 = SHERI S. TEPPER 7 = CHRISTOPHER ALEXANDER 8 = ROBIN HOBB 9 = SHERI S. TEPPER 10 = ANNE MCCAFFREY 11 = DAVID BRIN 12 = DAVID BRIN Union inside SQL ANNE MCCAFFREY CHRISTOPHER ALEXANDER DAVID BRIN DAVID BRIN DAVID BRIN DAVID BRIN ROBERT HARRIS ROBERT HARRIS ROBIN HOBB ROBIN HOBB SHERI S. TEPPER SHERI S. TEPPER Steven then Veva with DISTINCT 1 = ROBIN HOBB 2 = ROBERT HARRIS 3 = DAVID BRIN 4 = SHERI S. TEPPER 5 = CHRISTOPHER ALEXANDER 6 = ANNE MCCAFFREY
Rather than make this post really long with lots of examples, I suggest you go to this LiveSQL link, which will show you the list of scripts whose titles contain the word “multiset”.
https://livesql.oracle.com/apex/f?p=590:49:::NO:RP,49:P49_SEARCH:multiset
Bottom line: if you ever find yourself writing code with associative arrays to merge two collections, or find the common elements between two collections, and so on, switch to nested tables and let the PL/SQL runtime engine do the heavy lifting!
Set Operations
But wait, there’s more! In addition to the multiset operators, the wonderful PL/SQL team implemented a number of set oriented operators for use on nested tables, including:
set – removes duplicates from a nested table
submultiset of – returns true if one nested table is entirely contained within another
is empty – determine if a nested table has any elements (equivalent to count = 0)
member of – find out if a value is in the nested table
Here are some examples and links to LiveSQL scripts.
submultiset – https://livesql.oracle.com/apex/livesql/file/content_CDK8N4G949NO70Z79LLC82HLP.html
DECLARE
steven_authors strings_nt
:= strings_nt ('ROBIN HOBB',
'ROBERT HARRIS',
'DAVID BRIN',
'SHERI S. TEPPER',
'CHRISTOPHER ALEXANDER');
eli_authors strings_nt
:= strings_nt ('SHERI S. TEPPER', 'DAVID BRIN');
null_authors strings_nt
:= strings_nt ();
PROCEDURE bpl (val IN BOOLEAN, text_in IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line (text_in || ' ' ||
CASE val
WHEN TRUE THEN 'TRUE'
WHEN FALSE THEN 'FALSE'
ELSE 'NULL'
END);
END bpl;
BEGIN
bpl (steven_authors SUBMULTISET OF eli_authors,
'Father follows son?');
bpl (eli_authors SUBMULTISET OF steven_authors,
'Son follows father?');
bpl (steven_authors NOT SUBMULTISET OF eli_authors,
'Father doesn''t follow son?');
bpl (eli_authors NOT SUBMULTISET OF steven_authors,
'Son doesn''t follow father?');
bpl (steven_authors SUBMULTISET OF null_authors,
'Steven in NULL?');
END;
Father follows son? FALSE
Son follows father? TRUE
Father doesn't follow son? TRUE
Son doesn't follow father? FALSE
Steven in NULL? FALSE
member of – https://livesql.oracle.com/apex/livesql/file/content_CHWGE66FYWNMWCD4D0QQL638P.html
DECLARE
TYPE clientele IS TABLE OF VARCHAR2 (64);
client_list_12 clientele := clientele ('Customer 1', 'Customer 2');
client_list_13 clientele := clientele ('Customer 1', 'Customer 3');
client_list_133 clientele
:= clientele ('Customer 1', 'Customer 3', 'Customer 3');
client_list_empty clientele := clientele ();
BEGIN
IF 'Customer 1' MEMBER OF client_list_12
THEN
DBMS_OUTPUT.put_line ('Customer 1 is in the 12 list');
END IF;
IF 'Customer 2' NOT MEMBER OF client_list_13
THEN
DBMS_OUTPUT.put_line ('Customer 2 is not in the 13 list');
END IF;
DBMS_OUTPUT.put_line ('List 133 contains ' || CARDINALITY (client_list_133) || ' items');
IF client_list_empty IS EMPTY
THEN
DBMS_OUTPUT.put_line ('Client list is empty');
END IF;
IF client_list_133 IS NOT EMPTY
THEN
DBMS_OUTPUT.put_line ('Client list 133 is not empty');
END IF;
END;
Customer 1 is in the 12 list
Customer 2 is not in the 13 list
List 133 contains 3 items
Client list is empty
Client list 133 is not empty
Light Lifting
I say this often and with conviction: whenever possible, let Oracle Database do the heavy lifting for you, the hard work. You’ve got enough to do and worry about, without reinventing the wheel. Plus, they have a lot more resources to bring to bear on a problem. Plus, they are writing in C, so their implementation will be faster. Plus, they are writing code for literally millions of people to use.
So whose code do you think will be better tested?
Bottom line: if you find yourself needing to manipulate in-program data (not currently in database tables) in set-kinda ways, make sure you are using nested tables and take advantage of the many cool set-oriented features offered with them.
