Out of the three collection types, NESTED TABLE collection type supports SET operations in Oracle.
x MULTISET EXCEPT [DISTINCT] y :
Performs a MINUS set operation on nested tables x and y, returning a nested table whose elements are in x, but not in y.[Removes ONE single value from left operand only.
x MULTISET INTERSECT [DISTINCT] y :
Performs an INTERSECT set operation on nested tables x and y, returning a nested table whose elements are in both x and y.
x MULTISET UNION [DISTINCT] y :
Performs a UNION set operation on nested tables x and y, returning a nested table whose elements include all those in x as well as those in y.
SET :
SET operator removes the duplicate elements of the collection type and thereby creates a new set of unique element values.
IS [NOT] A SET :
IS [NOT] A SET determines if a collection has duplicate values or not.
IS [NOT] EMPTY :
IS [NOT] EMPTY determines if a collection is empty or not.
e [NOT] MEMBER [OF] x :
e [NOT] MEMBER [OF] x determines if a particular element belongs to collection or not.
SUBMULTISET :
The SUBMULTISET operator checks whether the left operand nested table type is a subset of the right operand nested table type by comparing them both and returns a Boolean value as a result. This operator can be only performed on two nested table type instances created by the same type and not on two different nested table types, even if they are identical.
SQL> DECLARE
2 TYPE nt_t IS TABLE OF NUMBER;
3
4 nt_v1 nt_t := nt_t (1, 2, 3);
5 nt_v2 nt_t := nt_t (1, 2, 3);
6 nt_v3 nt_t := nt_t (1,2,3,4,4,2,3,1);
7 nt_v4 nt_t := nt_t (1,2,3);
8 nt_v5 nt_t;
9
10 TYPE type_ntt IS TABLE OF VARCHAR2(1);
11
12 l_ntt_var1 type_ntt :=type_ntt('A','B','C','D');
13 l_ntt_var2 type_ntt :=type_ntt('A','B');
14 l_ntt_var3 type_ntt :=type_ntt('D','E');
15 l_ntt_var4 type_ntt :=type_ntt();
16 l_ntt_var5 type_ntt;
17 BEGIN
18 IF nt_v1 = nt_v2
19 THEN
20 DBMS_OUTPUT.PUT_LINE ('Nested Table nt_v1 And nt_v2 Are Same.');
21 END IF;
22
23 IF nt_v3 != nt_v4
24 THEN
25 DBMS_OUTPUT.PUT_LINE ('Nested Table nt_v3 And nt_v4 Are Different.');
26 END IF;
27
28 nt_v5 := nt_v3 MULTISET EXCEPT nt_v4;
29
30 DBMS_OUTPUT.PUT_LINE ('MULTISET EXCEPT');
31
32 FOR rec IN 1..nt_v5.COUNT
33 LOOP
34 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
35 END LOOP;
36
37 nt_v5 := nt_v3 MULTISET EXCEPT DISTINCT nt_v4;
38
39 DBMS_OUTPUT.PUT_LINE ('MULTISET EXCEPT DISTINCT');
40
41 FOR rec IN 1..nt_v5.COUNT
42 LOOP
43 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
44 END LOOP;
45
46 nt_v5 := nt_v3 MULTISET INTERSECT nt_v4;
47
48 DBMS_OUTPUT.PUT_LINE ('MULTISET INTERSECT');
49
50 FOR rec IN 1..nt_v5.COUNT
51 LOOP
52 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
53 END LOOP;
54
55 nt_v5 := nt_v3 MULTISET INTERSECT DISTINCT nt_v4;
56
57 DBMS_OUTPUT.PUT_LINE ('MULTISET INTERSECT DISTINCT');
58
59 FOR rec IN 1..nt_v5.COUNT
60 LOOP
61 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
62 END LOOP;
63
64 nt_v5 := nt_v3 MULTISET UNION nt_v4;
65
66 DBMS_OUTPUT.PUT_LINE ('MULTISET UNION');
67
68 FOR rec IN 1..nt_v5.COUNT
69 LOOP
70 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
71 END LOOP;
72
73 nt_v5 := nt_v3 MULTISET UNION DISTINCT nt_v4;
74
75 DBMS_OUTPUT.PUT_LINE ('MULTISET UNION DISTINCT');
76
77 FOR rec IN 1..nt_v5.COUNT
78 LOOP
79 DBMS_OUTPUT.PUT_LINE ('Index : '||rec||' - '||'Value : '||nt_v5(rec));
80 END LOOP;
81
82 nt_v5 := SET (nt_v3);
83
84 DBMS_OUTPUT.PUT_LINE ('SET');
85
86 FOR rec IN nt_v5.FIRST .. nt_v5.LAST
87 LOOP
88 DBMS_OUTPUT.PUT_LINE (nt_v5 (rec));
89 END LOOP;
90
91 IF nt_v5 IS A SET
92 THEN
93 DBMS_OUTPUT.PUT_LINE ('Collection nt_v5 Is Unique.');
94 END IF;
95
96 IF nt_v3 IS NOT A SET
97 THEN
98 DBMS_OUTPUT.PUT_LINE ('Collection nt_v3 Has Duplicate Value.');
99 END IF;
100
101 nt_v5.DELETE;
102
103 IF nt_v5 IS EMPTY
104 THEN
105 DBMS_OUTPUT.PUT_LINE ('Collection nt_v5 Is Empty.');
106 END IF;
107
108 IF nt_v1 IS NOT EMPTY
109 THEN
110 DBMS_OUTPUT.PUT_LINE ('Collection nt_v1 Is Not Empty.');
111 END IF;
112
113 IF 1 MEMBER OF nt_v1
114 THEN
115 DBMS_OUTPUT.PUT_LINE ('1 Is A Member Of nt_v1');
116 END IF;
117
118 IF 7 NOT MEMBER OF nt_v1
119 THEN
120 DBMS_OUTPUT.PUT_LINE ('7 Is Not A Member Of nt_v1');
121 END IF;
122
123 IF l_ntt_var2 submultiset OF l_ntt_var1 THEN
124 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR2 Is A SubMultiset Of The Type L_NTT_VAR1');
125 ELSE
126 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR2 Is Not A SubMultiset Of The Type L_NTT_VAR1');
127 END IF;
128
129 IF NOT l_ntt_var3 submultiset OF l_ntt_var1 THEN
130 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR3 Is Not A SubMultiset Of The Type L_NTT_VAR1');
131 ELSE
132 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR3 Is A SubMultiset Of The Type L_NTT_VAR1');
133 END IF;
134
135 IF l_ntt_var4 submultiset OF l_ntt_var1 THEN
136 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR4 Is A SubMultiset Of The Type L_NTT_VAR1');
137 ELSE
138 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR4 Is Not A Submultiset Of The Type L_NTT_VAR1');
139 END IF;
140
141 IF l_ntt_var5 submultiset OF l_ntt_var1 THEN
142 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR5 Is A SubMultiset Of The Type L_NTT_VAR1');
143 ELSE
144 DBMS_OUTPUT.PUT_LINE('The Type L_NTT_VAR5 Is Not A SubMultiset Of The Type L_NTT_VAR1');
145 END IF;
146 END;
147 /
--OUTPUT :::
Nested Table nt_v1 And nt_v2 Are Same.
Nested Table nt_v3 And nt_v4 Are Different.
MULTISET EXCEPT
Index : 1 - Value : 4
Index : 2 - Value : 4
Index : 3 - Value : 2
Index : 4 - Value : 3
Index : 5 - Value : 1
MULTISET EXCEPT DISTINCT
Index : 1 - Value : 4
MULTISET INTERSECT
Index : 1 - Value : 1
Index : 2 - Value : 2
Index : 3 - Value : 3
MULTISET INTERSECT DISTINCT
Index : 1 - Value : 1
Index : 2 - Value : 2
Index : 3 - Value : 3
MULTISET UNION
Index : 1 - Value : 1
Index : 2 - Value : 2
Index : 3 - Value : 3
Index : 4 - Value : 4
Index : 5 - Value : 4
Index : 6 - Value : 2
Index : 7 - Value : 3
Index : 8 - Value : 1
Index : 9 - Value : 1
Index : 10 - Value : 2
Index : 11 - Value : 3
MULTISET UNION DISTINCT
Index : 1 - Value : 1
Index : 2 - Value : 2
Index : 3 - Value : 3
Index : 4 - Value : 4
SET
1
2
3
4
Collection nt_v5 Is Unique.
Collection nt_v3 Has Duplicate Value.
Collection nt_v5 Is Empty.
Collection nt_v1 Is Not Empty.
1 Is A Member Of nt_v1
7 Is Not A Member Of nt_v1
The Type L_NTT_VAR2 Is A SubMultiset Of The Type L_NTT_VAR1
The Type L_NTT_VAR3 Is Not A SubMultiset Of The Type L_NTT_VAR1
The Type L_NTT_VAR4 Is A SubMultiset Of The Type L_NTT_VAR1
The Type L_NTT_VAR5 Is Not A SubMultiset Of The Type L_NTT_VAR1
PL/SQL procedure successfully completed.SQL> DECLARE
2 TYPE nt_t IS TABLE OF VARCHAR2(30);
3 nt_v1 nt_t := nt_t ('A','B','C','D','D','B','C','A');
4 nt_v2 nt_t := nt_t ('A','B','C');
5 nt_v3 nt_t;
6 BEGIN
7 nt_v3 := nt_v1 MULTISET EXCEPT nt_v2;
8
9 FOR rec IN 1..nt_v3.COUNT
10 LOOP
11 DBMS_OUTPUT.PUT_LINE ('Index Without DISTINCT : '||rec||' - '||'Value : '||nt_v3(rec));
12 END LOOP;
13
14 nt_v3.DELETE;
15
16 nt_v3 := nt_v1 MULTISET EXCEPT DISTINCT nt_v2;
17
18 FOR rec IN 1..nt_v3.COUNT
19 LOOP
20 DBMS_OUTPUT.PUT_LINE ('Index With DISTINCT : '||rec||' - '||'Value : '||nt_v3(rec));
21 END LOOP;
22 END;
23 /
--OUTPUT :::
Index Without DISTINCT : 1 - Value : D
Index Without DISTINCT : 2 - Value : D
Index Without DISTINCT : 3 - Value : B
Index Without DISTINCT : 4 - Value : C
Index Without DISTINCT : 5 - Value : A
Index With DISTINCT : 1 - Value : D
PL/SQL procedure successfully completed.
