Oracle PL/SQL Nested Table MULTISET INTERSECT operator
PL/SQL nested table MULTISET INTERSECT operator results in data this is common between two nested tables. In the example below, the data common between nt_u1 and nt_u2 is returned (1,6,7,2) by multiset intersect. By reversing the nested tables (nt_u2 multiset intersect nt_u1), the result is (1,2,6,7). The SET operator results in distinct dataset in a PL/SQL nested table. The multiset intersect of distinct values of nt_d1 and nt_d2 results in a empty collection (0 element nested table).

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_d1 nt_numtype := nt_numtype(1,3,3,5,5,7);
  nt_d2 nt_numtype := nt_numtype(2,4,4,6,6,8);
  nt_uq1 nt_numtype := nt_numtype();
  nt_uq2 nt_numtype := nt_numtype();
  nt_ix 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('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)||')');
  nt_ix := nt_u1 MULTISET INTERSECT nt_u2;
  DBMS_OUTPUT.PUT_LINE('nt_U1 Intersect nt_U2 ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');
  nt_ix := nt_u2 MULTISET INTERSECT nt_u1;
  DBMS_OUTPUT.PUT_LINE('nt_U2 Intersect nt_U1 ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');
  DBMS_OUTPUT.PUT_LINE('Nested Table nt_D1 ['||nt_d1.COUNT||'] -> ('||op_nt(nt_d1)||')');
  nt_uq1 := SET(nt_d1);
  DBMS_OUTPUT.PUT_LINE('Unique Table nt_d1 ['||nt_uq1.COUNT||'] -> ('||op_nt(nt_uq1)||')');
  DBMS_OUTPUT.PUT_LINE('Nested Table nt_d2 ['||nt_d2.COUNT||'] -> ('||op_nt(nt_d2)||')');
  nt_uq2 := SET(nt_d2);
  DBMS_OUTPUT.PUT_LINE('Unique Table nt_d2 ['||nt_uq2.COUNT||'] -> ('||op_nt(nt_uq2)||')');
  nt_ix := nt_numtype();
  nt_ix := SET(nt_d1) MULTISET INTERSECT SET(nt_d2);
  DBMS_OUTPUT.PUT_LINE('SET(nt_d1) Intersect SET(nt_d2) ['||nt_ix.COUNT||'] -> ('||op_nt(nt_ix)||')');
END;
/



Back

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

  1268