Oracle10g :Nested Table Enhancements

Nested Table Enhancements

Nested tables in PL/SQL now support more operations than before. Collections can be assigned directly to the value of another collection of the same type, or to the result of a set expression.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Yellow', 'Green');
  l_col_3 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_3 := l_col_1;
  display('Direct Assignment:', l_col_3);

  -- Expression assignments.
  l_col_3 := l_col_1 MULTISET UNION l_col_2;
  display('MULTISET UNION:', l_col_3);

  l_col_3 := l_col_1 MULTISET UNION DISTINCT l_col_2;
  display('MULTISET UNION DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT l_col_2;
  display('MULTISET INTERSECT:', l_col_3);

  l_col_3 := l_col_1 MULTISET INTERSECT DISTINCT l_col_2;
  display('MULTISET INTERSECT DISTINCT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT l_col_2;
  display('MULTISET EXCEPT:', l_col_3);

  l_col_3 := l_col_1 MULTISET EXCEPT DISTINCT l_col_2;
  display('MULTISET EXCEPT DISTINCT:', l_col_3);
END;
/

Comparisons between collections have also improved with the addition of NULL checks, equality operators and set operations.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue');
  l_col_2 t_colors := t_colors('Red', 'Green', 'Green');
  l_col_3 t_colors;
BEGIN
  IF (l_col_3 IS NULL) AND (l_col_1 IS NOT NULL) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 IS NULL) AND (l_col_1 IS NOT NULL): TRUE');
  END IF;

  l_col_3 := l_col_1;

  IF (l_col_3 = l_col_1) AND (l_col_3 != l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(l_col_3 = l_col_1) AND (l_col_3 != l_col_2): TRUE');
  END IF;

  IF (SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2) THEN
    DBMS_OUTPUT.put_line(CHR(10) || '(SET(l_col_2) SUBMULTISET l_col_1) AND (l_col_1 NOT SUBMULTISET l_col_2): TRUE');
  END IF;

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(l_col_2): ' || CARDINALITY(l_col_2));

  DBMS_OUTPUT.put_line(CHR(10) || 'CARDINALITY(SET(l_col_2)): ' || CARDINALITY(SET(l_col_2)) || ' - Duplicates removed');

  IF l_col_2 IS NOT A SET THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_2 IS NOT A SET: TRUE - Contains duplicates');
  END IF;

  IF l_col_3 IS NOT EMPTY THEN
    DBMS_OUTPUT.put_line(CHR(10) || 'l_col_3 IS NOT EMPTY: TRUE');
  END IF;
END;
/

The SET function removes duplicate entries from your nested table, in a similar way to the SQL DISTINCT aggregate function.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_colors IS TABLE OF VARCHAR2(10);
  l_col_1 t_colors := t_colors('Red', 'Green', 'Blue', 'Green', 'Blue');
  l_col_2 t_colors;

  PROCEDURE display (p_text  IN  VARCHAR2,
                     p_col   IN  t_colors) IS
  BEGIN
    DBMS_OUTPUT.put_line(CHR(10) || p_text);
    FOR i IN p_col.first .. p_col.last LOOP
      DBMS_OUTPUT.put_line(p_col(i));
    END LOOP;
  END;
BEGIN
  -- Basic assignment.
  l_col_2 := l_col_1;
  display('Direct Assignment:', l_col_2);

  -- SET assignments.
  l_col_2 := SET(l_col_1);
  display('MULTISET UNION:', l_col_2);
END; 
/