Set operators (UNION, INTERSECT, MINUS) have long been available in basic SQL to process data in tables, but for data in PL/SQL nested tables, we’d earlier have to go through the ritual of traversing through the collections in a loop, doing a row-by-row comparison.
Oracle 10G onwards, MULTISET features have made possible single-step set operations on nested tables.
Here is a demo with scripts for performing MULTISET operations on nested tables of strings.
The Case Study
Let’s say you have two sets of data about employees in an organization:
1. A set of employees who are engineers
2. A set of employees who are poorly paid
In this example, assume the above sets are stored as nested tables of employee names and the data is:
SET(engineers) = {‘Wally’, ‘Ted’, ‘Asok’, ‘Carol’}
SET(poorly paid employees) = {‘Asok’, ‘Wally’, ‘Ratbert’}
Your PL/SQL code needs to find answers to:
(A) Which engineers are poorly paid?
(B) Which poorly paid employees are not engineers?
(C) Which engineers are well paid?
How will you do it?
MULTISET Operators at Your Service!
Multiset operators combine the results of two nested tables, say SET1 and SET2, into a single resultant nested table in these ways:
MULTISET INTERSECT gives as result a nested table containing values common to SET1 and SET2;
MULTISELECT EXCEPT gives as result a nested table containing values of SET1 that are not in SET2;
MULTISELECT UNION gives as result a nested table containing values of SET1 as well as SET2.
The table and Venn diagram below show how these operators provide answers to the questions above.
| Question | Set Equation | Oracle Operator |
|
Which engineers are poorly paid? |
SET(engineers) INTERSECT SET(poorly paid employees) |
MULTISET INTERSECT |
| Which poorly paid employees are not engineers? | SET(poorly paid employees) MINUS SET(engineers) |
MUTISET EXCEPT |
| Which engineers are well paid? | SET(engineers) MINUS SET(poorly paid employees) |
MUTISET EXCEPT |
| Which employees are either engineers or poorly paid? | SET(engineers) UNION SET(poorly paid employees) |
MULTISET UNION |
Let’s take for implementation problem (C): Which engineers are well paid?
MULTISET EXCEPT Implementation
The solution to "Which engineers are well paid" is elementary with MULTISET EXCEPT.
SET(well-paid employees) =
SET(engineers) MULTISET EXCEPT SET(poorly paid employees)
Code:
DECLARE
TYPE emp_tbl_typ IS TABLE OF VARCHAR2(20);
v_engineers emp_tbl_typ;
v_poorlypaidemployees emp_tbl_typ;
v_wellpaidengineers emp_tbl_typ;
BEGIN
v_engineers := emp_tbl_typ('Wally'
, 'Ted'
, 'Asok'
, 'Carol');
v_poorlypaidemployees := emp_tbl_typ('Asok'
, 'Wally'
, 'Ratbert');
/* Set (well-paid engineers) =
Set (employees who are engineers)
excluding Set (employees who are poorly paid) */
v_wellpaidengineers :=
v_engineers MULTISET EXCEPT v_poorlypaidemployees;
IF v_wellpaidengineers IS NOT EMPTY THEN
dbms_output.put_line('Well-paid engineers:');
FOR i IN 1..v_wellpaidengineers.COUNT LOOP
dbms_output.put_line(v_wellpaidengineers(i));
END LOOP;
END IF;
END;
/
Execution:
SQL> DECLARE
2
3 TYPE emp_tbl_typ IS TABLE OF VARCHAR2(20);
4
5 v_engineers emp_tbl_typ;
6 v_poorlypaidemployees emp_tbl_typ;
7 v_wellpaidengineers emp_tbl_typ;
8
9 BEGIN
10
11
12 v_engineers := emp_tbl_typ('Wally'
13 , 'Ted'
14 , 'Asok'
15 , 'Carol');
16
17 v_poorlypaidemployees := emp_tbl_typ('Asok'
18 , 'Wally'
19 , 'Ratbert');
20
21 /* Set (well-paid engineers) =
22 Set (employees who are engineers)
23 excluding Set (employees who are poorly paid) */
24
25 v_wellpaidengineers :=
26 v_engineers MULTISET EXCEPT v_poorlypaidemployees;
27
28 IF v_wellpaidengineers IS NOT EMPTY THEN
29
30 dbms_output.put_line('Well-paid engineers:');
31
32 FOR i IN 1..v_wellpaidengineers.COUNT LOOP
33 dbms_output.put_line(v_wellpaidengineers(i));
34 END LOOP;
35
36 END IF;
37
38 END;
39 /
Well-paid engineers:
Ted
Carol
PL/SQL procedure successfully completed.
As an exercise, I leave to you the implementations for the other 3 questions. [Hint: A small tweak in the script above would do it.]
(A) Which engineers are poorly paid?
(B) Which poorly paid employees are not engineers?
(D) Which employees are either engineers or poorly paid?
MULTISET Operations and Nested Table Of OBJECTs: : PLS-00306 Error?
While it is known that MULTISET operations work only for nested tables and not for other collection types like varrays or associative arrays, there is an additional limitation — MULTISET operators work as described only with nested tables of standard datatypes like VARCHAR2 or NUMBER. I tried using MULTISET EXCEPT on a nested table of OBJECT, and this is what happened:
SQL> CREATE TYPE emp_obj AS OBJECT (
2 employee_id VARCHAR2(150)
3 , employee_name VARCHAR2(240)
4 )
5 /
Type created.
SQL>
SQL> CREATE TYPE emp_tbl_typ AS TABLE OF emp_obj
2 /
Type created.
SQL> DECLARE
2
3 v_engineers emp_tbl_typ;
4 v_poorlypaidemployees emp_tbl_typ;
5 v_wellpaidengineers emp_tbl_typ;
6
7 BEGIN
8
9 v_engineers := emp_tbl_typ(emp_obj('E101','Wally')
10 , emp_obj('E102','Ted')
11 , emp_obj('E104','Asok')
12 , emp_obj('E103','Carol'));
13
14 v_poorlypaidemployees := emp_tbl_typ(emp_obj('E104','Asok')
15 , emp_obj('E101','Wally')
16 , emp_obj('E108','Ratbert'));
17
18
19 /* Set (well-paid engineers) =
20 Set (employees who are engineers)
21 excluding Set (employees who are poorly paid) */
22
23 v_wellpaidengineers :=
24 v_engineers MULTISET EXCEPT v_poorlypaidemployees;
25
26 IF v_wellpaidengineers IS NOT EMPTY THEN
27
28 FOR i IN 1..v_wellpaidengineers.COUNT LOOP
29 dbms_output.put_line(v_wellpaidengineers(i).employee_name);
30 END LOOP;
31
32 END IF;
33
34 END;
35 /
v_engineers MULTISET EXCEPT v_poorlypaidemployees;
*
ERROR at line 24:
ORA-06550: line 24, column 5:
PLS-00306: wrong number or types of arguments in call to 'MULTISET_EXCEPT_ALL'
ORA-06550: line 23, column 3:
PL/SQL: Statement ignored
ORA-06550: line 29, column 51:
PLS-00302: component 'EMPLOYEE_NAME' must be declared
ORA-06550: line 29, column 7:
PL/SQL: Statement ignored
With a bit of tinkering you’ll get Oracle MULTISET operations to work with complex types as above too. Can you figure out how to do it? Watch out for the next post to know the solution.
Update: here’s the solution: Error PLS-00306 when Working with Nested Table of Objects
