Multiset Operators In Oracle PL/SQL

Multiset operators combine two nested table objects into a single nested table object.

We can use Multiset with the following phrases:

• MULTISET UNION : Gives the result nested table of all elements of both the nested tables
• MULTISET INTERSECT : Gives the result nested table of common elements present in both the nested tables.
• MULTISET EXCEPT : Gives the result nested table which has elements present in A1 (first nested table) and not present in A2 (second nested table). 

For Example:

SET SERVEROUTPUT ON
DECLARE 
A1 ARRAY1;
A2 ARRAY1;
A3_UNION ARRAY1;
A4_INTERSECT ARRAY1;
A5_EXCEPT ARRAY1;
BEGIN
A1:= ARRAY1('a','b','b','c','d','d','e','e','f','g','h');
A2:=ARRAY1('d','d','e','f');
A3_UNION:=ARRAY1();
A4_INTERSECT:=ARRAY1();
A5_EXCEPT:=ARRAY1();

DBMS_OUTPUT.PUT_LINE('MULTISET UNION Of A1 and A2 :');
A3_UNION:=  A1  MULTISET UNION  A2;

FOR I IN A3_UNION.FIRST .. A3_UNION.LAST LOOP
DBMS_OUTPUT.PUT_LINE(A3_UNION(I));
END LOOP;

/*
Output: 
 MULTISET UNION Of A1 and A2 :
a
b
b
c
d
d
e
e
f
g
h
d
d
e
f    
*/



DBMS_OUTPUT.PUT_LINE('MULTISET Intersect Of A1 and A2 :');
A4_INTERSECT:=  A1  MULTISET INTERSECT  A2;

FOR I IN A4_INTERSECT.FIRST .. A4_INTERSECT.LAST LOOP
DBMS_OUTPUT.PUT_LINE(A4_INTERSECT(I));
END LOOP;

/* Output :
MULTISET Intersect Of A1 and A2 :
d
d
e

f
*/

DBMS_OUTPUT.PUT_LINE('MULTISET Except Of A1 and A2 :');
A5_EXCEPT:=A1  MULTISET EXCEPT A2;

FOR I IN A5_EXCEPT.FIRST .. A5_EXCEPT.LAST LOOP
DBMS_OUTPUT.PUT_LINE(A5_EXCEPT(I));
END LOOP;

/* Output :
MULTISET Except Of A1 and A2 :
a
b
b
c
e
g

h
*/

END;