Geneste tabellen in PL/SQL ondersteunen nu meer operaties dan voorheen. Collecties kunnen direct gekoppeld worden aan de waarde van een andere collectie van hetzelfde type, of aan het resultaat van een set van expressies:
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;
/ Vergelijkingen tussen collections zijn ook verbetrerd met de toevoeging van NULL checks, vergelijkings operatoren en set operaties waaronder:
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;
/ De SET functie verwijderd dubbele entries uit je geneste tabellen op een vergelijkbare manier als de 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;
/ |
