Oracle MULTISET UNION

In Oracle PL/SQL, the MULTISET UNION collection operation returns the merging of two collections

MULTISET UNION syntax in Oracle PL/SQL

nt1 MULTISET UNION nt2

Collection parameters and arguments

• nt1, nt2 – collections to combine

Note:

• Operations are possible only with nested tables collections. Both collections that participate in an operation must be of the same type. The result of the operation is also a collection of nested tables.

Consider some examples of operations on the MULTISET UNION collections to understand how to use MULTISET UNION in Oracle PL/SQL

For example:

DECLARE
reslt varchar2(100);
TYPE nested_typ IS TABLE OF NUMBER;
nt1 nested_typ := nested_typ(1,2,3);
nt2 nested_typ := nested_typ(3,2,1);
nt3 nested_typ := nested_typ(2,3,1,3);
answer nested_typ;
BEGIN
answer := nt1 MULTISET UNION nt2;
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||to_char(answer(iter));
end if;
end loop;
dbms_output.put_line('nt1 MULTISET UNION nt2 = '||reslt);
--Output: nt1 MULTISET UNION nt2 = 1, 2, 3, 3, 2, 1
answer := nt1 MULTISET UNION nt3;
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||to_char(answer(iter));
end if;
end loop;
dbms_output.put_line('nt1 MULTISET UNION nt3 = '||reslt);
--Output: nt1 MULTISET UNION nt3 = 1, 2, 3, 2, 3, 1, 3.
END;

Another example of MULTISET UNION with line collections

DECLARE
reslt varchar2(100);
TYPE nested_typ IS TABLE OF varchar2(20);
nt1 nested_typ := nested_typ('alpha', 'beta', 'gamma');
nt2 nested_typ := nested_typ('beta', 'gamma', 'alpha');
nt3 nested_typ := nested_typ('beta', 'alpha', 'gamma', 'delta');
answer nested_typ;
BEGIN
answer := nt1 MULTISET UNION nt2;
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||answer(iter);
end if;
end loop;
dbms_output.put_line('nt1 MULTISET UNION nt2 = '||reslt);
--Output: nt1 MULTISET UNION nt2 = alpha, beta, gamma, gamma, alpha
answer := nt1 MULTISET UNION nt3;
for iter in answer.first ... answer.last
loop
if iter = 1 then
reslt:=answer(iter);
else
reslt := reslt ||', '||answer(iter);
end if;
end loop;
dbms_output.put_line('nt1 MULTISET UNION nt3 = '||reslt);
--Output: nt1 MULTISET UNION nt3 = alpha, beta, gamma, alpha, gamma, delta
END;

Set-Level Manipulation of Nested Tables (MULTISET)