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;