Multiset Operators in PL/SQL

--type creation

create or replace type varchar2_ntt1 as table of varchar2(4000);

/

--functions

select nt.nt1,set(nt.nt1),cardinality(nt.nt1),cardinality(set(nt.nt1)) from (select varchar2_ntt('c','a','b','c')nt1 from dual)nt;

select nt.column_value from table(set(varchar2_ntt('a','b','c','c')))nt;

--comparisons

select cardinality(nt),case when set(nt)=set(nt1) then 'true' else 'false' end  FROM (select varchar2_ntt('a','b','c')nt,varchar2_ntt('a','b','c')nt1 from dual);

select cardinality(nt),case when set(nt) is empty then 'true'else 'false' end  FROM (select varchar2_ntt('a','b','c')nt,varchar2_ntt('a','b','c')nt1 from dual);

select cardinality(nt),case when set(nt) is empty then 'true'else 'false' end  FROM (select varchar2_ntt nt,varchar2_ntt('a','b','c')nt1 from dual);

select cardinality(nt),case when set(nt) is not empty then 'true' else 'false' end  FROM (select varchar2_ntt('a','b','c')nt,varchar2_ntt('a','b','c')nt1 from dual);

select cardinality(nt),case when set(nt) is a set then 'true' else 'false' end  FROM (select varchar2_ntt()nt,varchar2_ntt() nt1 from dual);

--conditions

select case when nt submultiset of nt1 then 'true' else 'false' end  from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select case when nt2 NOT SUBMULTISET OF nt3 then 'true' else 'false' end from (select varchar2_ntt('a','b')nt2,varchar2_ntt('b','c')nt3 from dual);

select case when 'd' member of nt1 then 'yes' else 'no' end from (select varchar2_ntt('a','b','d')nt1 from dual);

--OPerators

--Multiset Operator SQL Set Equivalent

--MULTISET UNION DISTINCT       UNION

--MULTISET UNION                UNION ALL

--MULTISET UNION ALL            UNION ALL

--MULTISET INTERSECT            INTERSECT

--MULTISET INTERSECT DISTINCT   N/A

--MULTISET EXCEPT               MINUS

--MULTISET EXCEPT DISTINCT      N/A

select nt multiset union distinct nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset union nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset union all nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset intersect nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset intersect distinct nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset except nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);

select nt multiset except distinct nt1 from (select varchar2_ntt('a','b')nt,varchar2_ntt('b','c')nt1 from dual);