使用集合操作符给嵌套表赋值

  

从oracle10g开始，允许将多个嵌套表的结果集合到某个嵌套表中（set，multiset union[distinct]，multiset intersect，multiset except）。其中set有一个嵌套表传入参数，作用是消除嵌套表中的重复内容；multiset union是将两个嵌套表内容合并起来，加入关键词distinct可以消除重复内容；multiset intersect是将两个嵌套表的共同内容返回并赋值给另外一个嵌套表并自动消除重复；multiset except是找出嵌套表1中存在但2中不存在的元素，需要使用distinct关键词消除重复内容。如下为测试代码。

declare

 type number_sets_type istableofnumber;

 number_sets1 number_sets_type := number_sets_type(1,2,3,4,3,1);

 number_sets2 number_sets_type := number_sets_type(5,6,3,4);

 number_sets3 number_sets_type;

begin

 number_sets3 := set(number_sets1);

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multisetunion number_sets2;

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multisetuniondistinct number_sets2;

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multisetintersect number_sets2;

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multisetexcept number_sets2;

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multisetexceptdistinct number_sets2;

 for i in number_sets3.first..number_sets3.lastloop

    dbms_output.put(number_sets3(i)||',');

 endloop;

 dbms_output.new_line;

end;

1,2,3,4,

1,2,3,4,3,1,5,6,3,4,

1,2,3,4,5,6,

3,4,

1,2,3,1,

1,2,