MULTISET EXCEPT takes as arguments two nested tables and returns a nested table whose elements
are in the first nested table but not in the second nested table. The two input nested tables
must be of the same type, and the returned nested table is of the same type as well.
MULTISET EXCEPT的两个参数嵌套表，并返回其元素在第一个嵌套表，但不是在第二个嵌套表嵌套表。两个输入嵌套表必须是同一类型，同一类型以及返回嵌套表。
The ALL keyword instructs Oracle to return all elements in nested_table1 that are not in nested_table2.
For example, if a particular element occurs m times in nested_table1 and n times in nested_table2, then the
result will have (m-n) occurrences of the element if m >n and 0 occurrences if m<=n. ALL is the default.
ALL关键字指示Oracle返回多有在nested_table1中，但是不在nested_table2的所有元素。例如，如果一个特定的元素出现在nested_table1中出现M
次在nested_table2 N次，如果M>N,则返回M> N个该元素，如果M <= N则返回0个该元素。ALL是默认的。
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '1';
11
12 b.extend;
13 b(1) := '1';
14 b.extend;
15 b(2) := '1';
16
17 c := a multiset except b;
18
19 for i in c.first .. c.last loop
20 dbms_output.put_line(c(i));
21 end loop;
22 end;
23 /
1
1 --返回两个1
PL/SQL 过程已成功完成。
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '1';
11
12 b.extend;
13 b(1) := '1';
14 b.extend;
15 b(2) := '1';
16
17 c := b multiset except a; -- b - a
18
19
20 dbms_output.put_line(c.count);
21
22 end;
23 /
0 --当M
PL/SQL 过程已成功完成。
The DISTINCT keyword instructs Oracle to eliminate any element in nested_table1 which is also in nested_table2,
regardless of the number of occurrences.
DISTINCT关键字，从nested_table1中消除在nested_table2中的元素，不管出现的次数。
1 declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '1';
11 b.extend;
12 b(1) := '1';
13 b.extend;
14 b(2) := '1';
15 c := a multiset except DISTINCT b;
16 dbms_output.put_line(c.count);
17* end;
18 /
0 --可以看出集c等于a - b，重复的也算
PL/SQL 过程已成功完成。
MULTISET INTERSECT takes as arguments two nested tables and returns a nested table whose values are common
in the two input nested tables. The two input nested tables must be of the same type, and the returned nested
table is of the same type as well.
MULTISET INTERSECT的需要两个嵌套表参数，并返回一个嵌套表，其值在两个输入嵌套表的共同部分。两个输入嵌套表必须是同一类型，
返回嵌套表也一样
The ALL keyword instructs Oracle to return all common occurrences of elements that are in the two input nested tables,
including duplicate common values and duplicate common NULL occurrences. For example, if a particular value occurs m times
in nested_table1 and n times in nested_table2, then the result would contain the element min(m,n) times. ALL is the default.。
ALL关键字指示Oracle返回在两个嵌套表的公共部分，包括重复值和重复NULL出现。例如，如果一个特定的值发生在
nested_table1和N次在nested_table2 m次，那么结果将包含元素min（M，N）次。 ALL是默认的。
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '2';
9 a(3) := '3';
10 a(4) := '4';
11
12 b.extend;
13 b(1) := '1';
14 b.extend;
15 b(2) := '1';
16
17 c := b multiset INTERSECT a;
18
19 for i in c.first .. c.last loop
20 dbms_output.put_line(c(i));
21 end loop;
22
23 end;
24 /
1 --一次
PL/SQL 过程已成功完成。
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1'; --这里不同
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '4';
11
12 b.extend;
13 b(1) := '1';
14 b.extend;
15 b(2) := '1';
16
17 c := b multiset INTERSECT a;
18
19 for i in c.first .. c.last loop
20 dbms_output.put_line(c(i));
21 end loop;
22
23 end;
24 /
1
1 --两次,取min（M，N）
PL/SQL 过程已成功完成。
The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL, if they exist.
DISTINCT关键字就是去掉的重复值，在有相同值的情况下，如果两个嵌套表中没有相同的部分，则返回空的表。
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '2';
11
12 b.extend;
13 b(1) := '1';
14 b.extend;
15 b(2) := '1';
16
17 c := b multiset INTERSECT DISTINCT a;
18
19 for i in c.first .. c.last loop
20 dbms_output.put_line(c(i));
21 end loop;
22
23 end;
24 /
1 --没有重复的
PL/SQL 过程已成功完成。
1 declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '1';
9 a(3) := '1';
10 a(4) := '2';
11 b.extend;
12 b(1) := '3';
13 b.extend;
14 b(2) := '4';
15 c := b multiset INTERSECT DISTINCT a;
16 dbms_output.put_line(c.count);
17* end;
SQL> /
0 --返回空表
PL/SQL 过程已成功完成。
MULTISET UNION takes as arguments two nested tables and returns a nested table whose values are those of the
two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.
The ALL keyword instructs Oracle to return all elements that are in the two input nested tables, including duplicate
values and duplicate NULL occurrences. This is the default.
The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL,
if they exist
MULTISET UNION返回两个嵌套表的并集，默认包括重复值，DISTINCT关键字去掉了重复值
SQL> declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '2';
9 a(3) := '3';
10 a(4) := '4';
11
12 b.extend;
13 b(1) := '3';
14 b.extend;
15 b(2) := '4';
16
17 c := a multiset UNION b;
18
19 for i in c.first .. c.last loop
20 dbms_output.put_line(c(i));
21 end loop;
22
23 end;
24 /
1
2
3
4
3
4
PL/SQL 过程已成功完成。
1 declare
2 a list_char := list_char();
3 b list_char := list_char();
4 c list_char := list_char();
5 begin
6 a.extend(4);
7 a(1) := '1';
8 a(2) := '2';
9 a(3) := '3';
10 a(4) := '4';
11 b.extend;
12 b(1) := '3';
13 b.extend;
14 b(2) := '4';
15 c := a multiset UNION DISTINCT b;
16 for i in c.first .. c.last loop
17 dbms_output.put_line(c(i));
18 end loop;
19* end;
20 /
1
2
3
4 －－消除了重复值
PL/SQL 过程已成功完成。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25361369/viewspace-713189/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
层次查询
请登录后发表评论
登录
全部评论
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
违法和不良信息举报电话:18101376593