The MULTISET UNION operator joins the two collections together, doing the equivalent of a UNION ALL between the two sets. The MULTISET UNION and MULTISET UNION ALL operators are functionally equivalent.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5,6); l_tab2 t_tab := t_tab(5,6,7,8,9,10); BEGIN l_tab1 := l_tab1 MULTISET UNION l_tab2; FOR i IN l_tab1.first .. l_tab1.last LOOP DBMS_OUTPUT.put_line(l_tab1(i)); END LOOP; END; /
The DISTINCT keyword can be added to any of the multiset operations to removes the duplicates. Adding it to the MULTISET UNION operator makes it the equivalent of a UNION between the two sets.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5,6); l_tab2 t_tab := t_tab(5,6,7,8,9,10); BEGIN l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2; FOR i IN l_tab1.first .. l_tab1.last LOOP DBMS_OUTPUT.put_line(l_tab1(i)); END LOOP; END; /
The MULTISET EXCEPT operator returns the elements of the first set that are not present in the second set, doing the equivalent of the MINUS set operator. The MULTISET EXCEPT DISTINCT operator will remove any duplicates.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10); l_tab2 t_tab := t_tab(6,7,8,9,10); BEGIN l_tab1 := l_tab1 MULTISET EXCEPT l_tab2; FOR i IN l_tab1.first .. l_tab1.last LOOP DBMS_OUTPUT.put_line(l_tab1(i)); END LOOP; END; /
The MULTISET INTERSECT operator returns the elements that are present in both sets, doing the equivalent of the INTERSECT set operator. The MULTISET INTERSECT DISTINCT operator will remove any duplicates.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10); l_tab2 t_tab := t_tab(6,7,8,9,10); BEGIN l_tab1 := l_tab1 MULTISET INTERSECT l_tab2; FOR i IN l_tab1.first .. l_tab1.last LOOP DBMS_OUTPUT.put_line(l_tab1(i)); END LOOP; END; /
The IS {NOT} A SET condition is used to test if a collection is populated by unique elements, or not. If the collection is not initialized the function will return NULL. An initialised and empty collection will return true.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_null_tab t_tab := NULL; l_empty_tab t_tab := t_tab(); l_set_tab t_tab := t_tab(1,2,3,4); l_not_set_tab t_tab := t_tab(1,2,3,4,4,4); FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS BEGIN IF p_in IS NULL THEN RETURN 'NULL'; ELSIF p_in THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END; BEGIN DBMS_OUTPUT.put_line('l_null_tab IS A SET = ' || display(l_null_tab IS A SET)); DBMS_OUTPUT.put_line('l_null_tab IS NOT A SET = ' || display(l_null_tab IS NOT A SET)); DBMS_OUTPUT.put_line('l_empty_tab IS A SET = ' || display(l_empty_tab IS A SET)); DBMS_OUTPUT.put_line('l_empty_tab IS NOT A SET = ' || display(l_empty_tab IS NOT A SET)); DBMS_OUTPUT.put_line('l_set_tab IS A SET = ' || display(l_set_tab IS A SET)); DBMS_OUTPUT.put_line('l_set_tab IS NOT A SET = ' || display(l_set_tab IS NOT A SET)); DBMS_OUTPUT.put_line('l_not_set_tab IS A SET = ' || display(l_not_set_tab IS A SET)); DBMS_OUTPUT.put_line('l_not_set_tab IS NOT A SET = ' || display(l_not_set_tab IS NOT A SET)); END; /
The IS {NOT} EMPTY condition is used to test if a collection is empty, or not. If the collection is not initialized the function will return NULL.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_null_tab t_tab := NULL; l_empty_tab t_tab := t_tab(); l_not_empty_tab t_tab := t_tab(1,2,3,4,4,4); FUNCTION display (p_in BOOLEAN) RETURN VARCHAR2 AS BEGIN IF p_in IS NULL THEN RETURN 'NULL'; ELSIF p_in THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END; BEGIN DBMS_OUTPUT.put_line('l_null_tab IS EMPTY = ' || display(l_null_tab IS EMPTY)); DBMS_OUTPUT.put_line('l_null_tab IS NOT EMPTY = ' || display(l_null_tab IS NOT EMPTY)); DBMS_OUTPUT.put_line('l_empty_tab IS EMPTY = ' || display(l_empty_tab IS EMPTY)); DBMS_OUTPUT.put_line('l_empty_tab IS NOT EMPTY = ' || display(l_empty_tab IS NOT EMPTY)); DBMS_OUTPUT.put_line('l_not_empty_tab IS EMPTY = ' || display(l_not_empty_tab IS EMPTY)); DBMS_OUTPUT.put_line('l_not_empty_tab IS NOT EMPTY = ' || display(l_not_empty_tab IS NOT EMPTY)); END; /
The MEMBER condition allows you to test if an element is member of a collection.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5); BEGIN DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? '); IF 3 MEMBER OF l_tab1 THEN DBMS_OUTPUT.put_line('TRUE'); ELSE DBMS_OUTPUT.put_line('FALSE'); END IF; END; /
The SUBMULTISET condition returns true if the first collection is a subset of the second.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5); l_tab2 t_tab := t_tab(1,2,3); l_tab3 t_tab := t_tab(1,2,3,7); BEGIN DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? '); IF l_tab2 SUBMULTISET OF l_tab1 THEN DBMS_OUTPUT.put_line('TRUE'); ELSE DBMS_OUTPUT.put_line('FALSE'); END IF; DBMS_OUTPUT.put('Is l_tab3 SUBMULTISET OF l_tab1? '); IF l_tab3 SUBMULTISET OF l_tab1 THEN DBMS_OUTPUT.put_line('TRUE'); ELSE DBMS_OUTPUT.put_line('FALSE'); END IF; END; /
An initialised, but empty subset will always return true.
SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5); l_tab2 t_tab := t_tab(); BEGIN DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? '); IF l_tab2 SUBMULTISET OF l_tab1 THEN DBMS_OUTPUT.put_line('TRUE'); ELSE DBMS_OUTPUT.put_line('FALSE'); END IF; END; / .................... SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(); l_tab2 t_tab := t_tab(); BEGIN DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? '); IF l_tab2 SUBMULTISET OF l_tab1 THEN DBMS_OUTPUT.put_line('TRUE'); ELSE DBMS_OUTPUT.put_line('FALSE'); END IF; END; /
The CARDINALITY function returns the number of elements in the collection, similar to the COUNT method, but it is available from SQL.
CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10); / SELECT CARDINALITY(tab1) FROM (SELECT t_number_tab (1, 2, 3, 4) AS tab1 FROM dual); ---output: 4
SELECT tab1 FROM (SELECT t_number_tab(1, 2, 3, 4) AS tab1 FROM dual) WHERE CARDINALITY(tab1) = 4; --- output: T_NUMBER_TAB(1, 2, 3, 4) --or SET SERVEROUTPUT ON DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5); BEGIN DBMS_OUTPUT.put_line('COUNT = ' || l_tab1.COUNT); DBMS_OUTPUT.put_line('CARDINALITY = ' || CARDINALITY(l_tab1)); END; / output: COUNT = 5 CARDINALITY = 5
The POWERMULTISET function accepts a nested table and returns a "nested table of nested tables" containing all the possible subsets from the original nested table.
CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10); / SELECT * FROM TABLE(POWERMULTISET(t_number_tab (1, 2, 3, 4))); output: COLUMN_VALUE ---------------------------------------- T_NUMBER_TAB(1) T_NUMBER_TAB(2) T_NUMBER_TAB(1, 2) T_NUMBER_TAB(3) T_NUMBER_TAB(1, 3) T_NUMBER_TAB(2, 3) T_NUMBER_TAB(1, 2, 3) T_NUMBER_TAB(4) T_NUMBER_TAB(1, 4) T_NUMBER_TAB(2, 4) T_NUMBER_TAB(1, 2, 4) T_NUMBER_TAB(3, 4) T_NUMBER_TAB(1, 3, 4) T_NUMBER_TAB(2, 3, 4) T_NUMBER_TAB(1, 2, 3, 4)
The POWERMULTISET_BY_CARDINALITY function is similar to the POWERMULTISET function, but it allows us to limit the output to just those subsets that have the specified cardinality. In the following example we return only those subsets that have a cardinality of 2.
SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY(t_number_tab (1, 2, 3, 4), 2)); output: T_NUMBER_TAB(1, 2) T_NUMBER_TAB(1, 3) T_NUMBER_TAB(1, 4) T_NUMBER_TAB(2, 3) T_NUMBER_TAB(2, 4) T_NUMBER_TAB(3, 4)
The SET function returns a collection containing the distinct values from a collection.
CREATE OR REPLACE TYPE t_number_tab AS TABLE OF NUMBER(10); / SET LINESIZE 100 COLUMN basic_out FORMAT A35 COLUMN set_out FORMAT A35 SELECT tab1 AS basic_out, SET(tab1) AS set_out, CARDINALITY(tab1) AS card_out, CARDINALITY(SET(tab1)) AS card_set FROM (SELECT t_number_tab (1, 2, 3, 4, 4, 4) AS tab1 FROM dual); output: BASIC_OUT SET_OUT CARD_OUT CARD_SET ----------------------------------- ----------------------------------- ---------- ---------- T_NUMBER_TAB(1, 2, 3, 4, 4, 4) T_NUMBER_TAB(1, 2, 3, 4) 6 4
In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.
Total : 27273
Today :9
Today Visit Country :