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 including:

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; /

Fonte: http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php