Understanding SET Operations In NESTED TABLE

join_methods

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.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *