Saturday, January 24, 2015

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);



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect