Oracle PL/SQL Nested Table ANSI Set Operations
The example below shows the various PL/SQL nested table ANSI set operation features available in Oracle (code tested in version 10gR2).

DECLARE
  TYPE nested_tab IS TABLE OF NUMBER;
  nt_odd nested_tab := nested_tab(1,3,5,7,9);
  nt_even nested_tab := nested_tab(2,4,6,8);
  nt_mix nested_tab := nested_tab(0,1,2,3,4);
  nt_test nested_tab := nested_tab(2,6,4,8);
  nt_null nested_tab := nested_tab();
  nt_flag BOOLEAN;
 
  FUNCTION get_tf(i_flag BOOLEAN) RETURN VARCHAR2 AS
  BEGIN
   IF i_flag THEN RETURN ' -> True';
   ELSE RETURN ' -> False';
   END IF;
  END get_tf;
BEGIN
  nt_flag := nt_odd SUBMULTISET nt_even;
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'(1,3,5,7,9) SUBMULTISET (2,4,6,8) '||get_tf(nt_flag));
  nt_flag := nt_odd NOT SUBMULTISET OF nt_even;
  DBMS_OUTPUT.PUT_LINE('(1,3,5,7,9) NOT SUBMULTISET OF (2,4,6,8) '||get_tf(nt_flag));
  nt_flag := nt_odd SUBMULTISET nt_mix;
  DBMS_OUTPUT.PUT_LINE('(1,3,5,7,9) SUBMULTISET (0,1,2,3,4) '||get_tf(nt_flag));
  nt_flag := nt_even SUBMULTISET nt_test;
  DBMS_OUTPUT.PUT_LINE('(2,4,6,8) SUBMULTISET (2,6,4,8) '||get_tf(nt_flag));
  nt_flag := nt_even IN (nt_odd,nt_test);
  DBMS_OUTPUT.PUT_LINE('(2,4,6,8) IN ((2,4,6,8),(2,6,4,8)) '||get_tf(nt_flag));
  nt_flag := 2 MEMBER OF nt_odd;
  DBMS_OUTPUT.PUT_LINE('2 MEMBER OF (1,3,5,7,9) '||get_tf(nt_flag));
  nt_flag := nt_odd IS A SET;
  DBMS_OUTPUT.PUT_LINE('(1,3,5,7,9) IS A SET '||get_tf(nt_flag));
  nt_flag := nt_null IS EMPTY;
  DBMS_OUTPUT.PUT_LINE('() IS EMPTY [CARDINALITY '|| CARDINALITY(nt_null)||'] '||get_tf(nt_flag));
END;
/



Back

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

  1392