oracle中的集合的并运算,Oracle集合运算

Oracle8i的集合运算符共有4个(其他版本的没用过，估计只会多，不会少)：

1. Minus

2. Union

3. Union All

4. Intersect

create or replace procedure PRC_COLLECT_TEST is

vn_count long :=0;

begin

select count(1) into vn_count from emp;

dbms_output.put_line('Rows in table emp is:' || vn_count);

/*minus:只取不重复的记录*/

select count(1) into vn_count

from (select t.*, t.rowid from emp t

minus

select s.*, s.rowid from emp s);

dbms_output.put_line('minus rows in table emp is:' || vn_count);

/*union: 取两个查询记录(去掉重复的)**/

select count(1)  into vn_count

from (select t.*, t.rowid from emp t

union

select s.*, s.rowid from emp s);

dbms_output.put_line('union rows in table emp is:' || vn_count);

/*union all: 取两个查询所有的记录(包括重复的)**/

select count(1) into vn_count

from (select t.*, t.rowid from emp t

union all

select s.*, s.rowid from emp s);

dbms_output.put_line('union all rows in table emp is:' || vn_count);

/*intersect: 只取重复的记录(相当于交集吧)*/

select count(1)into vn_count

from (select t.*, t.rowid from emp t

intersect

select s.*, s.rowid from emp s);

dbms_output.put_line('intersect rows in table emp is:' || vn_count);

end PRC_COLLECT_TEST;

/