Oracle10g :Nested Table Enhancements
2011-06-24 17:03 Tracy. 阅读(224) 评论(0) 编辑 收藏 举报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; /
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2011/06/24/2089176.html