集合赋值

1.将一个集合赋值给另一个集合，集合类型必须是同一个集合类型

declare
  type name_varray_type is varray(4) of varchar2(10);
  name1_varray name_varray_type;
  name2_varray name_varray_type;
begin
  name1_varray:=name_varray_type("scott","smith");
  name2_varray:=name_varray_type("1","2","3","4");
  dbms_output.put_line("name2_varray旧数据:");
  for i in 1..name2_varray.count loop
     dbms_output.put_line(name2_varray(i)); --"1","2","3","4"
  end loop;
  name2_varray := name1_varray;
  dbms_output.put_line("name2_varray新数据:");
  for i in 1..name2_varray.count loop
     dbms_output.put_line(name2_varray(i)); --"scott","smith"
  end loop;
end;
name2_varray旧数据:
1
2
3
4
name2_varray新数据:
scott
smith

--对索引表也可以
declare
  type name_varray_type is table of varchar2(10) index by binary_integer;
  name1_varray name_varray_type;
  name2_varray name_varray_type;
begin
  name1_varray(2):="scott";
  name2_varray:=name1_varray;
  dbms_output.put_line(name2_varray(2));  --"scott"
  dbms_output.put_line(name2_varray(1));  --没有数据时报异常
end;

--给集合赋null
declare
 type name_table_type is table of varchar2(10) index by binary_integer;
 type name_varray_type  is varray(4) of varchar2(10);
  name1_table name_table_type;
  name2_varray name_varray_type;
begin
  --name1_varray:=name_varray_type("scott","smith");
 -- name2_varray:=name_varray_type("1","2","3","4");
  if name1_table is null then  --返回false
     dbms_output.put_line("name1_table为空");
  end if;
  
  if name2_varray is null then   --返回true
     dbms_output.put_line("name2_varray为空:");
  end if;
end;

2.使用集合操作符(对嵌套表适用,10g以后可用)
set  取消嵌套表中的重复值
multiset union  取得两个嵌套表的并集
multiset union distinct 取得两个嵌套表的并集,并去重复
multiset intersect  取得两个嵌套表的交集
multiset except 取得两个嵌套表差集

declare
type name_table_type is table of varchar2(50);
name1_table name_table_type:=name_table_type("scott","scott","smith");
name2_table name_table_type:=name_table_type("cheng","smith");
result_table name_table_type;
begin
  result_table:= set(name1_table); --"scott","smith"
  result_table:= name1_table multiset union distinct name2_table; --"scott","scott","smith","cheng","smith"
  result_table:= name1_table multiset union distinct name2_table; --"scott","smith","cheng"
  result_table:= name1_table multiset intersect name2_table; --"smith"
  result_table:= name1_table multiset except name2_table; --"scott","scott"
end;

3.集合比较
(1).判断是否为空，用is null(嵌套表和数组),索引表不能判断null
(2).对嵌套表判断是否相同(嵌套表),用=或!=(10g以后可用)

declare
type name_table_type is table of varchar2(50);
name1_table name_table_type:=name_table_type("scott");
name2_table name_table_type:=name_table_type("scott");
begin
    if name1_table=name2_table then  --true
       dbms_output.put_line("两个嵌套表相同");
    end if;
end;

(3).集合操作符(嵌套表)(10g以后可用)
cardinality  返回元素个数
submultiset of 判断是否是子集
member of  检测特殊数据是否为嵌套表的元素
is a set  检测是否包含重复值,不存在重复值返回true,存在重复值返回false
is empty  检测是否为空,空返回true,非空返回false

declare
type name_table_type is table of varchar2(50);
name1_table name_table_type:=name_table_type("scott");
name2_table name_table_type:=name_table_type("scott","smith","scott");
begin
    dbms_output.put_line(cardinality(name2_table));  --3
    if name1_table submultiset of name2_table then   --true
      dbms_output.put_line("name1_table是name2_table的子集");
    end if;
    if "smith" member of name2_table then   --true
      dbms_output.put_line("smith是name2_table的元素");
    end if;
    if   name2_table is a set then   --不存在重复值返回true,存在重复值返回false
      dbms_output.put_line("name2_table存在重复元素");
    end if;
    if   name2_table is empty then   --空返回true,非空返回false
      dbms_output.put_line("name2_table为空");
    end if;
end;