PL/SQL使用复合数据类型4（集合操作符、比较集合）

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

从Oracle 10g开始，在编写PL/SQL程序时允许将多个嵌套表的结果组合到某个嵌套表中，这项任务是使用ANSI集合操作符（SET,MULTISET UNION，MULTISET UNION DISTINCT,MULTISET INTERSECT,MULTISET EXCEPT）来完成的。
（1）使用SEET操作符
SET操作符用于取消特定嵌套表中的重复值。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt_table nt_table_type := nt_table_type(2,4,3,1,2);
  nt_result nt_table_type;
BEGIN
  nt_result := SET(nt_table);
  dbms_output.put(‘result:‘);
  FOR i IN 1..nt_result.COUNT LOOP
    dbms_output.put(‘ ‘ || nt_result(i));
  END LOOP;
  dbms_output.new_line;
END;

（2）使用MULTISET UNION操作符
MULTISET UNION用于取得两个嵌套表的并集。当使用该操作符合并嵌套表结果时，结果集中会包含重复值。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt1 nt_table_type := nt_table_type(1,2,3);
  nt2 nt_table_type := nt_table_type(3,4,5);
  nt_result nt_table_type;
BEGIN
  --MULTISET UNION并集操作
  nt_result := nt1 MULTISET UNION nt2;
  dbms_output.put(‘result:‘);
  FOR i IN 1..nt_result.COUNT LOOP
    dbms_output.put(‘ ‘ || nt_result(i));
  END LOOP;
  dbms_output.new_line;
END;

（3）使用MULTISET UNION DISTINCT操作符
MULTISET UNION DISTINCT操作符用于取得两个嵌套表的并集，并取消重复结果。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt1 nt_table_type := nt_table_type(1,2,3);
  nt2 nt_table_type := nt_table_type(3,4,5);
  nt_result nt_table_type;
BEGIN
  nt_result := nt1 MULTISET UNION DISTINCT nt2;
  dbms_output.put(‘result:‘);
  FOR i IN 1..nt_result.COUNT LOOP
    dbms_output.put(‘ ‘ || nt_result(i));
  END LOOP;
  dbms_output.new_line;
END;

（4）使用MULTISET INTERSECT操作符
MULTISET INTERSECT操作符用于取得两个嵌套表的交集。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt1 nt_table_type := nt_table_type(1,2,3);
  nt2 nt_table_type := nt_table_type(3,4,5);
  nt_result nt_table_type;
BEGIN
  nt_result := nt1 MULTISET INTERSECT nt2;
  dbms_output.put(‘result:‘);
  FOR i IN 1..nt_result.COUNT LOOP
    dbms_output.put(‘ ‘ || nt_result(i));
  END LOOP;
  dbms_output.new_line;
END;

（5）使用MULTISET EXCEPT操作符
MULTISET EXCEPT用于取得两个嵌套表的差集。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt1 nt_table_type := nt_table_type(1,2,3);
  nt2 nt_table_type := nt_table_type(3,4,5);
  nt_result nt_table_type;
BEGIN
  nt_result := nt1 MULTISET EXCEPT nt2;
  dbms_output.put(‘result:‘);
  FOR i IN 1..nt_result.COUNT LOOP
    dbms_output.put(‘ ‘ || nt_result(i));
  END LOOP;
  dbms_output.new_line;
END;

比较集合

在Oracle 10g之前，当使用嵌套表类型和VARRAY类型的集合变量时，开发人员可以检测集合变量是否为NULL。从Oracle 10g开始，开发人员还可以比较两个集合变量是否相同，另外还可以在嵌套表上使用CARDINALITY，SUBMULTISET OF，MEMBER OF，IS A SET，IS EMPTY等集合操作符。其中，函数CARDINALITY用于返回嵌套表变量的元素个数，操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集，操作符MEMBER OF用于检测特定数据是否为嵌套表元素，操作符IS A SET用于检测嵌套表是否包含重复的元素值，操作符IS EMPTY用于检测嵌套表是否为NULL。
1、检测集合是否为NULL
当编写复杂的PL/SQL应用程序时，经常需要检测集合变量是否为NULL，在Oracle 10g之前，使用IS NULL操作符可以检测嵌套表或VARRAY变量是否为NULL。

DECLARE
  TYPE name_varray_type IS VARRAY(3) OF VARCHAR2(10);
  name_array name_varray_type;
BEGIN
  IF name_array is NULL THEN
    dbms_output.put_line(‘name_array集合未初始化‘);
  END IF;
END;

但Oracle 10g开始，当检测嵌套表是否为NULL时，不仅可以使用IS NULL操作符，也可以使用IS EMPTY操作符。注意IS EMPTY操作符只适用于嵌套表，而不适用于VARRAY。

DECLARE
  TYPE name_table_type IS TABLE OF VARCHAR2(10);
  name_table name_table_type;
BEGIN
  --虽然说IS EMPTY操作符可以用于嵌套表，测试结果表明有问题。
  IF name_table IS EMPTY THEN
    dbms_output.put_line(‘name_table集合未初始化‘);
  END IF;
END;

2、比较嵌套表是否相同
在Oracle 10g之前，不能直接比较两个嵌套表是否相同。但从Oracle 10g开始，允许使用比较操作符=或!=检测两个嵌套表变量是否相同。注意，使用这两个比较符只能比较嵌套表， 而不能比较VARRAY和索引表。

DECLARE
  TYPE name_table_type IS TABLE OF VARCHAR2(10);
  name_table1 name_table_type;
  name_table2 name_table_type;
BEGIN
  name_table1 := name_table_type(‘张三‘);
  name_table2 := NAME_table_type(‘李四‘);
  IF name_table1 = name_table2 THEN
    dbms_output.put_line(‘两个嵌套表完全相同‘);
  ELSE
    dbms_output.put_line(‘两个嵌套表完全不相同‘);
  END IF;
END;

在嵌套表上使用集合操作符

从Oracle 10g开始，开发人员可以在嵌套表上使用ANSI集合操作符CARDINALITY,MEMBER OF,IS A SET。注意，这些操作符只适用于嵌套表，而不适用于VARRAY和索引表。
（1）使用函数CARDINALITY
函数CARDINALITY用于返回嵌套表变量的元素个数。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt_table nt_table_type := nt_table_type(1,2,3,4);
BEGIN
  dbms_output.put_line(‘元素个数：‘ || CARDINALITY(nt_table));
  dbms_output.put_line(‘元素个数：‘ || nt_table.COUNT);
END;

（2）使用操作符SUBMULTISET OF
操作符SUBMULTISET OF用于确定一个嵌套表是否为另一个嵌套表的子集。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt_table1 nt_table_type := nt_table_type(1,3,2);
  nt_table2 nt_table_type := nt_table_type(1,2,3,4);
BEGIN
  IF nt_table1 SUBMULTISET nt_table2 THEN
    dbms_output.put_line(‘nt_table1 是 nt_table2的子集‘);
  END IF;
END;

（3）使用操作符MEMBER OF
操作符MEMBER OF用于检测特定数据是否为嵌套表的元素。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt_table nt_table_type := nt_table_type(1,2,3,4);
  v1 NUMBER := 1;
BEGIN
  IF v1 MEMBER OF nt_table THEN
    dbms_output.put_line(‘v1 是 nt_table的元素‘);
  END IF;
END;

（4）使用操作符IS A SET
操作符IS A SET用于检测嵌套表是否包含重复的元素值。

DECLARE
  TYPE nt_table_type IS TABLE OF NUMBER;
  nt_table nt_table_type := nt_table_type(1,2,3,4,1);
  v1 NUMBER := 1;
BEGIN
  IF nt_table IS A SET THEN
    dbms_output.put_line(‘嵌套表nt_table无重复值‘);
  ELSE
    dbms_output.put_line(‘嵌套表nt_table包含重复值‘);
  END IF;
END;

PL/SQL使用复合数据类型4（集合操作符、比较集合）,古老的榕树,5-wow.com