Oracle PL/SQL Nested Table MULTISET DISTINCT
The MULTISET DISTINCT operator will get distinct set of data from a PL/SQL nested table. The same can also be achieved by the SET operator as shown in the example below. The PL/SQL nested table nt_base has 12 elements. The distinct data in this table is 10 (2 and 6 are duplicate elements). By nt_dist MULTISET UNION DISTINCT nt_base results in 10 distinct elements. By nt_dist MULTISET UNION SET(nt_base) results in 20 elements (10 from nt_dist and distinct 10 elements from nt_base). The CARDINALITY shows the number of elements in the PL/SQL nested table.

DECLARE
  nt_base nt_numtype := nt_numtype(1, 2, 9, 11, 7, 6, 3, 4, 2, 6, 5, 18);
  nt_dist nt_numtype := nt_numtype();
  FUNCTION op_nt(i_nt IN nt_numtype) RETURN VARCHAR2 IS
   s_Nested_tab VARCHAR2(200);
  BEGIN
   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;
   RETURN s_nested_tab;
  END op_nt;
BEGIN
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY of nt_base ['||CARDINALITY(nt_base)||']');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||' nt_base ['||nt_base.COUNT||'] -> ('||op_nt(nt_base)||')');
  nt_dist := nt_dist MULTISET UNION DISTINCT nt_base;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY of nt_dist ['||CARDINALITY(nt_dist)||']');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||' nt_dist ['||nt_dist.COUNT||'] -> ('||op_nt(nt_dist)||')');
  nt_dist := nt_dist MULTISET UNION SET(nt_base);
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY of nt_dist ['||CARDINALITY(nt_dist)||']');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||' nt_dist ['||nt_dist.COUNT||'] -> ('||op_nt(nt_dist)||')');
  nt_base := SET(nt_base);
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY of nt_base ['||CARDINALITY(nt_base)||']');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||' nt_base ['||nt_base.COUNT||'] -> ('||op_nt(nt_base)||')');
END;
/



Back

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

  1395