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

  

从 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 is table of number ;

 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. last loop

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

  end loop ;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multiset union number_sets2;

  for i in number_sets3. first ..number_sets3. last loop

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

  end loop ;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multiset union distinct number_sets2;

  for i in number_sets3. first ..number_sets3. last loop

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

  end loop ;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multiset intersect number_sets2;

  for i in number_sets3. first ..number_sets3. last loop

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

  end loop ;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multiset except number_sets2;

  for i in number_sets3. first ..number_sets3. last loop

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

  end loop ;

 dbms_output.new_line;

 

 number_sets3 := number_sets1 multiset except distinct number_sets2;

  for i in number_sets3. first ..number_sets3. last loop

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

  end loop ;

 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,