Oracle PL/SQL Nested Table - UNION
Oracle Pl/SQL nested tables are used to store a set of values. A nested table is similar to a 1-dimension array. Unlike VARRAY, PL/SQL nested tables are not bound, but can grow to a maximum upper limit of 2147483647 elements (231 approx.), which corresponding to the upper limit of PLS_INTEGER. The example shown below highlights the use of UNION operator in PL/SQL nested table. Others features such as extending the nested table (EXTEND), deletion of an element (DELETE), getting distinct values by the use of SET() and checking of existance of an element in the nested table (EXISTS) are also shown.
Note: When an element is deleted in a PL/SQL nested table, a gap will occur in the sequence. The EXISTS can be used to check for existance of data in the element. To skip over a gap NEXT can be used.

CREATE OR REPLACE TYPE nt_numtype IS TABLE OF NUMBER;
/



DECLARE
  nt_all nt_numtype := nt_numtype(0);
  nt_odd nt_numtype := nt_numtype(1,3,5,7,9);
  nt_even nt_numtype := nt_numtype(2,4,6,8);
 
  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 i_nt.EXISTS(idx) THEN
     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 IF;
   END LOOP;
   RETURN s_nested_tab;
  END op_nt;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Base Nested Table ['||LPAD(nt_all.COUNT,2,' ')||'] -> ('||op_nt(nt_all)||')');
 
  nt_all := nt_all MULTISET UNION nt_odd;
  DBMS_OUTPUT.PUT_LINE('UNION With nt_odd ['||LPAD(nt_all.COUNT,2,' ')||'] -> ('||op_nt(nt_all)||')');
 
  nt_all := nt_all MULTISET UNION nt_even;
  DBMS_OUTPUT.PUT_LINE('UNION with nt_even ['||nt_all.COUNT||'] -> ('||op_nt(nt_all)||')');
 
  nt_all.EXTEND;
  nt_all(11) := nt_odd(1);
  nt_all.EXTEND;
  nt_all(12) := nt_even(1);
  DBMS_OUTPUT.PUT_LINE('Extended Table ['||nt_all.COUNT||'] -> ('||op_nt(nt_all)||')');
 
  nt_all.DELETE(11);
  DBMS_OUTPUT.PUT_LINE('Deleted Table ['||nt_all.COUNT||'] -> ('||op_nt(nt_all)||')');
 
  nt_all := SET(nt_all);
  DBMS_OUTPUT.PUT_LINE('Unique Nested Table ['||nt_all.COUNT||'] -> ('||op_nt(nt_all)||')');
END;
/



Back

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

  1314