Oracle PL/SQL Nested Table MULTISET EXCEPT operator
The MULTISET EXCEPT will return data from first PL/SQL nested table (nt_u1) that is not in the second nested table (nt_u2) as shown in output below (3,9). By reversing the order (data from nt_u2 MULTISET EXCEPT nt_u1), the result is (4,8). The result of nt_u3 MULTISET EXCEPT nt_u4 results in (2,6), which shows that only the first common values are excluded leaving the duplicate values from the nested table nt_u3. CARDINALITY function can be used instead of COUNT to get the number of elements in the nested table. The nt_u3 MULTISET EXCEPT DISTINCT nt_u4 operator results in an NULL or empty nested table by exclusion of a all data from nt_u3 using the distinct value from nt_u4. Reversing the order (nt_u4 MULTISET EXCEPT DISTINCT nt_u3) results in a single element nested table (3).

DECLARE
  nt_u1 nt_numtype := nt_numtype(1,3,6,7,9,2);
  nt_u2 nt_numtype := nt_numtype(1,2,4,6,8,7);
  nt_u3 nt_numtype := nt_numtype(1,2,2,4,6,6);
  nt_u4 nt_numtype := nt_numtype(1,2,3,4,1,6);
  nt_ex1 nt_numtype := nt_numtype();
  FUNCTION op_nt(i_nt IN nt_numtype) RETURN VARCHAR2 IS
   s_Nested_tab VARCHAR2(200);
  BEGIN
   IF i_nt.COUNT > 0 THEN
    FOR idx IN i_nt.FIRST .. i_nt.LAST LOOP
     IF idx < i_nt.COUNT THEN
      s_nested_tab := s_nested_tab||i_nt(idx)||', ';
     ELSE
      s_nested_tab := s_nested_tab||i_nt(idx);
     END IF;
    END LOOP;
   ELSE
     s_nested_tab := '';
   END IF;
  RETURN s_nested_tab;
  END op_nt;
BEGIN
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'Nested Table nt_U1 ['||nt_u1.COUNT||'] -> ('||op_nt(nt_u1)||')');
  DBMS_OUTPUT.PUT_LINE('Nested Table nt_U2 ['||nt_u2.COUNT||'] -> ('||op_nt(nt_u2)||')');
  DBMS_OUTPUT.PUT_LINE('Nested Table nt_U3 ['||nt_u3.COUNT||'] -> ('||op_nt(nt_u3)||')');
  DBMS_OUTPUT.PUT_LINE('Nested Table nt_U4 ['||nt_u4.COUNT||'] -> ('||op_nt(nt_u4)||')');
  nt_ex1 := nt_u1 MULTISET EXCEPT nt_u2;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'nt_U1 MULTISET EXCEPT nt_U2 ['||nt_ex1.COUNT||'] -> ('||op_nt(nt_ex1)||')');
  nt_ex1 := nt_u2 MULTISET EXCEPT nt_u1;
  DBMS_OUTPUT.PUT_LINE('nt_U2 MULTISET EXCEPT nt_U1 ['||nt_ex1.COUNT||'] -> ('||op_nt(nt_ex1)||')');
  nt_ex1 := nt_u3 MULTISET EXCEPT nt_u4;
  DBMS_OUTPUT.PUT_LINE('nt_U3 MULTISET EXCEPT nt_U4 ['||nt_ex1.COUNT||'] -> ('||op_nt(nt_ex1)||')');
  nt_ex1 := nt_u3 MULTISET EXCEPT DISTINCT nt_u4;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'nt_U3 MULTISET EXCEPT DISTINCT nt_U4 ['||nt_ex1.COUNT||'] -> ('||op_nt(nt_ex1)||')');
  nt_ex1 := nt_u4 MULTISET EXCEPT DISTINCT nt_u3;
  DBMS_OUTPUT.PUT_LINE('nt_U4 MULTISET EXCEPT DISTINCT nt_U3 ['||nt_ex1.COUNT||'] -> ('||op_nt(nt_ex1)||')');
END;
/



Refer to Complete Output

Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 03, 2013

  23710