在歷史跟蹤或遷移測試中經(jīng)常使用幾乎完全相同的表，在處理這些表時一個常見的任務(wù)是將表進行對比并報告或處理不同的記錄。在類 UNIX 環(huán)境中，有一個用于比較文件的 diff 命令，在 Windows 中，有一個 FC 命令。如果傳入兩個文件的名稱，它將會顯示要添加或刪除哪些行才能使文件完全相同。
　　在 Oracle 數(shù)據(jù)庫中，沒有標(biāo)準(zhǔn)的類 diff 的命令（雖然有很多非 Oracle 工具可以在數(shù)據(jù)庫之外完成這一任務(wù)）。其實在 SQL 中有一些方法可以用來進行類 diff 的對比，但是這里有一個最簡單的方法，也有一個（對于非標(biāo)準(zhǔn)數(shù)據(jù)的）比較好的方法。
　　首先，我們將創(chuàng)建示范這一功能所需的測試數(shù)據(jù)。我們還同時創(chuàng)建第二個完全相同的表，第二個表比第一個表少了一行記錄，又添加了一行記錄。


　　create table emp2 as select * from emp;
　　delete from emp2 where empno = 7499;
　　insert into emp2 values (7777,'STEPHENS','WRITER',7369,sysdate,100,null,10);
　　簡單的方法是使用 SQL 集合操作：MINUS、INTERSECT 和 UNION。MINUS 返回第一個查詢中出現(xiàn)而第二個查詢中沒有出現(xiàn)的記錄；INTERSECT 返回兩個查詢中都出現(xiàn)的記錄；UNION 返回兩個查詢的記錄。
　　這個例子中使用的方法與 diff 稍微有點相似，我們使用 < 字符指出只出現(xiàn)第一個表中的記錄，使用 > 字符指出只出現(xiàn)第二個表中的記錄，使用等號（=）指出兩個表中都出現(xiàn)的記錄。下面是顯示兩個表的不同之處的一個簡單方法：
　　select '<',t.* from (select * from emp minus select * from emp2) t
　　union select '=',t.* from (select * from emp intersect select * from emp2) t
　　union select '>',t.* from (select * from emp2 minus select * from emp) t;

　　' EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
　　- ----- ---------- --------- ----- --------- ----- ----- ------
　　< 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
　　= 7369 SMITH CLERK 7902 17-DEC-80 800 20
　　= 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
　　= 7566 JONES MANAGER 7839 02-APR-81 2975 20
　　= 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
　　= 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
　　= 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
　　= 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
　　= 7839 KING PRESIDENT 17-NOV-81 5000 10
　　= 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
　　= 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
　　= 7900 JAMES CLERK 7698 03-DEC-81 30
　　= 7902 FORD ANALYST 7566 03-DEC-81 3000 20
　　= 7934 MILLER CLERK 7782 23-JAN-82 1300 10
　　> 7777 STEPHENS WRITER 7369 29-JUL-04 100 10
　　你可以將這個查詢保存在某個地方，或者創(chuàng)建一個腳本保存在 SQL*Plus 路徑下的一個目錄中，這樣在你下次需要對比一對表時，你就可以直接 SQL *Plus 中運行它，而無須重新鍵入整個 SQL 了：
　　REM -- diff.sql -- compare two tables
　　select '<',t.* from (select * from &&1 minus select * from &&2) t
　　union
　　select '=',t.* from (select * from &&1 intersect select * from &&2) t
　　union
　　select '>',t.* from (select * from &&2 minus select * from &&1) t;

　　SQL> @diff emp emp2
　　你甚至還可以對比兩個查詢——只要它們返回的列數(shù)相同，并且列的類型相互兼容：
　　REM - diffqry.sql - compare two tables
　　select '<',t.* from (&&1 minus &&2) t
　　union
　　select '=',t.* from (&&1 intersect &&2) t
　　union
　　select '>',t.* from (&&2 minus &&1) t;

　　SQL> @diffqry "select empno from emp" "select empno from emp2"
　　There is a problem with this approach if your tables aren't normalized. The set operations do a DISTINCT on the rows returned. If you have duplicate rows in your table, they will not be returned as distinct. Consider this example:
　　如果你的表不是標(biāo)準(zhǔn)表，那么這種方法就存在一個問題。集合操作會對返回的記錄進行一個 DISTINCT 操作。如果在你的表中有重復(fù)的記錄，那么它們將不會作為不同的的記錄返回�？紤]下面這個例子：
　　create table t1 (a varchar2(30));
　　create table t2 (a varchar2(30));

　　insert into t1 values('foo');
　　insert into t1 values('foo');
　　insert into t2 values('foo');

　　SQL> @diff t1 t2

　　' A
　　- ------------------------------
　　= foo
　　結(jié)果顯示一切都好，即使你有重復(fù)的數(shù)據(jù)。此外，這個數(shù)據(jù)作為報表看上去相當(dāng)好，而且如果知道每個表的具體結(jié)構(gòu)，你還可以使用這個結(jié)果刪除重復(fù)的記錄或插入缺少的數(shù)據(jù)，實際上能夠使用通用查詢結(jié)果是相當(dāng)好的事情。
　　在“更好的”方法中，為了允許這些特殊的特性，我可以結(jié)合使用了 DBMS_SQL 的動態(tài) SQL 和管道（pipelined）函數(shù)。在這段代碼中，函數(shù)自動為每個表構(gòu)建一個 SQL 語句，這個SQL 語句包括 ROWID 和一系列記錄，數(shù)據(jù)還被排序（強迫所有的類型轉(zhuǎn)為字符數(shù)據(jù)以使得排序和比較操作可以正確工作——雖然下面的例子過于簡單），而且對比每一列的值。它使用數(shù)據(jù)字典表來描述表：
　　REM -- different approach

　　create or replace type diff_t as object
　　(
　　 dir char,
　　 rowid1 varchar2(18),
　　 rowid2 varchar2(18)
　　)
　　/
　　show errors;

　　create or replace type diff_tab as table of diff_t
　　/
　　show errors;

　　create or replace function diff(t1 varchar2,t2 varchar2)
　　 return diff_tab pipelined
　　is
　　 cnt integer := 0; -- number of columns
　　 -- sql statements
　　 sel varchar2(4000) := 'select rowid';
　　 ord varchar2(4000) := ' order by ';
　　 -- cursors
　　 c1 integer;
　　 c2 integer;
　　 -- execute/fetch results
　　 r1 integer;
　　 r2 integer;
　　 -- rowids
　　 rid1 varchar2(18);
　　 rid2 varchar2(18);
　　 -- row comparison result
　　 cmp integer;
　　 -- column values
　　 v1 varchar2(4000);
　　 v2 varchar2(4000);
　　begin
　　 -- describe the first table and assume the second table
　　matches
　　 for row in
　　 (
　　 select * from all_tab_columns
　　 where table_name = upper(t1)
　　 order by column_id
　　 )
　　 loop
　　 sel := sel ',to_char(' row.column_name ')';
　　 ord := ord to_char(cnt+2) ',';
　　 cnt := cnt + 1;
　　 end loop;
　　 ord := ord '1';
　　 sel := sel ' from ';
　　 -- sort rows by columns, in order
　　 c1 := dbms_sql.open_cursor;
　　 c2 := dbms_sql.open_cursor;
　　 dbms_sql.parse(c1,sel t1 ord,dbms_sql.native);
　　 dbms_sql.parse(c2,sel t2 ord,dbms_sql.native);
　　 -- need to re-describe the columns to define result positions
　　 dbms_sql.define_column(c1,1,'',18);
　　 dbms_sql.define_column(c2,1,'',18);
　　 for i in 1 .. cnt loop
　　 dbms_sql.define_column(c1,i+1,'',4000);
　　 dbms_sql.define_column(c2,i+1,'',4000);
　　 end loop;
　　 -- execute the queries
　　 r1 := dbms_sql.execute(c1);
　　 r2 := dbms_sql.execute(c2);
　　 -- fetch the first rows
　　 r1 := dbms_sql.fetch_rows(c1);
　　 r2 := dbms_sql.fetch_rows(c2);
　　 while r1 > 0 and r2 > 0 loop
　　 dbms_sql.column_value(c1,1,rid1);
　　 dbms_sql.column_value(c2,1,rid2);
　　 -- compare rows
　　 cmp := 0;
　　 for i in 1 .. cnt loop
　　 dbms_sql.column_value(c1,i+1,v1);
　　 dbms_sql.column_value(c2,i+1,v2);
　　 if (v1 is not null and v2 is null) or v1 < v2 then
　　 cmp := -1;
　　 elsif (v1 is null and v2 is not null) or v1 > v2 then
　　 cmp := 1;
　　 end if;
　　 exit when cmp != 0;
　　 end loop;
　　 -- move according to comparison
　　 if cmp = -1 then
　　 pipe row (diff_t('<',rid1,null));
　　 elsifcmp = 1 then
　　 pipe row (diff_t('>',null,rid2));
　　 else
　　 pipe row (diff_t('=',rid1,rid2));
　　 end if;
　　 if cmp <= 0 then
　　 r1 := dbms_sql.fetch_rows(c1);
　　 end if;
　　 if cmp >= 0 then
　　 r2 := dbms_sql.fetch_rows(c2);
　　 end if;
　　 end loop;
　　 while r1 > 0 loop
　　 dbms_sql.column_value(c1,1,rid1);
　　 pipe row (diff_t('<',rid1,null));
　　 r1 := dbms_sql.fetch_rows(c1);
　　 end loop;
　　 while r2 > 0 loop
　　 dbms_sql.column_value(c2,1,rid2);
　　 pipe row (diff_t('>',null,rid2));
　　 r2 := dbms_sql.fetch_rows(c2);
　　 end loop;
　　 dbms_sql.close_cursor(c1);
　　 dbms_sql.close_cursor(c2);
　　 return;
　　end;
　　/
　　show errors;

　　select * from table(diff('t1','t2'));

　　D ROWID1 ROWID2
　　- ------------------ ------------------
　　= AAAIR1AABAAAMwKAAA AAAIR2AABAAAMwSAAA
　　< AAAIR1AABAAAMwKAAB


　　因為返回了 ROWID，所以你可以操作從查詢得來的原始記錄。如果你想要更改原始表中的數(shù)據(jù)以避免出現(xiàn)“變異表”問題，那么你可能需要將 ROWID 臨時存儲在某個地方。
　　REM -- return empno from rows only in emp
　　select empno from emp where rowid in
　　(select rowid1 from table(diff('emp','emp2')) t where dir = '<');

　　REM -- delete rows from emp2 that don't exist in emp
　　create table emp_del as select * from table(diff('emp','emp2'));
　　delete from emp2 where rowid in (select rowid2 from emp_del where dir = '>');