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;
/