Multiset operators- 10g New Features

Multiset operators combine the results of two nested tables into a single nested table. Here is a quick demonstration about that.

declare
    type t1 is table of number;
    list1 t1 := t1(1,2,3,4,5);
    list2 t1 := t1(1,2,3);
    list3 t1;
begin
    list3 := list1  multiset except list2;
    dbms_output.put_line (' Results of Multiset Except');   
    dbms_output.put_line (' ');
    for i in list3.first..list3.last
    loop
        dbms_output.put_line (list3(i));
    end loop;   
    dbms_output.put_line (' ');   
   
    list3.delete;
    list3 := list1 multiset intersect list2;
    dbms_output.put_line (' Results of Multiset Intersect');
    dbms_output.put_line (' ');   
    for i in list3.first..list3.last
    loop
        dbms_output.put_line ( list3(i));
    end loop;
    dbms_output.put_line (' ');   
   
    list3.delete;
    list3 := list1 multiset union list2;
    dbms_output.put_line (' Results of Multiset Union');
    dbms_output.put_line (' ');       
    for i in list3.first..list3.last
    loop
        dbms_output.put_line (list3(i));
    end loop;
    dbms_output.put_line (' ');       
   
    list3.delete;
end;    


scott@ORCL> declare
  2     type t1 is table of number;
  3     list1 t1 := t1(1,2,3,4,5);
  4     list2 t1 := t1(1,2,3);
  5     list3 t1;
  6  begin
  7     list3 := list1  multiset except list2;
  8     dbms_output.put_line (' Results of Multiset Except');
  9     dbms_output.put_line (' ');
 10     for i in list3.first..list3.last
 11     loop
 12             dbms_output.put_line (list3(i));
 13     end loop;
 14     dbms_output.put_line (' ');
 15
 16     list3.delete;
 17     list3 := list1 multiset intersect list2;
 18     dbms_output.put_line (' Results of Multiset Intersect');
 19     dbms_output.put_line (' ');
 20     for i in list3.first..list3.last
 21     loop
 22             dbms_output.put_line ( list3(i));
 23     end loop;
 24     dbms_output.put_line (' ');
 25
 26     list3.delete;
 27     list3 := list1 multiset union list2;
 28     dbms_output.put_line (' Results of Multiset Union');
 29     dbms_output.put_line (' ');
 30     for i in list3.first..list3.last
 31     loop
 32             dbms_output.put_line (list3(i));
 33     end loop;
 34     dbms_output.put_line (' ');
 35
 36     list3.delete;
 37  end;
 38  /
 Results of Multiset Except

  4
  5

 Results of Multiset Intersect

 1
 2
 3

 Results of Multiset Union

 1
 2
 3
 4
 5
 1
 2
 3

PL/SQL procedure successfully completed.