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


　　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);
　　簡(jiǎn)單的方法是使用 SQL 集合操作：MINUS、INTERSECT 和 UNION。MINUS 返回第一個(gè)查詢中出現(xiàn)而第二個(gè)查詢中沒(méi)有出現(xiàn)的記錄；INTERSECT 返回兩個(gè)查詢中都出現(xiàn)的記錄；UNION 返回兩個(gè)查詢的記錄。
　　這個(gè)例子中使用的方法與 diff 稍微有點(diǎn)相似，我們使用 < 字符指出只出現(xiàn)第一個(gè)表中的記錄，使用 > 字符指出只出現(xiàn)第二個(gè)表中的記錄，使用等號(hào)（=）指出兩個(gè)表中都出現(xiàn)的記錄。下面是顯示兩個(gè)表的不同之處的一個(gè)簡(jiǎ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
　　你可以將這個(gè)查詢保存在某個(gè)地方，或者創(chuàng)建一個(gè)腳本保存在 SQL*Plus 路徑下的一個(gè)目錄中，這樣在你下次需要對(duì)比一對(duì)表時(shí)，你就可以直接 SQL *Plus 中運(yùn)行它，而無(wú)須重新鍵入整個(gè) 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
　　你甚至還可以對(duì)比兩個(gè)查詢——只要它們返回的列數(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)表，那么這種方法就存在一個(gè)問(wèn)題。集合操作會(huì)對(duì)返回的記錄進(jìn)行一個(gè) DISTINCT 操作。如果在你的表中有重復(fù)的記錄，那么它們將不會(huì)作為不同的的記錄返回�？紤]下面這個(gè)例子：
　　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ù)。此外，這個(gè)數(shù)據(jù)作為報(bào)表看上去相當(dāng)好，而且如果知道每個(gè)表的具體結(jié)構(gòu)，你還可以使用這個(gè)結(jié)果刪除重復(fù)的記錄或插入缺少的數(shù)據(jù)，實(shí)際上能夠使用通用查詢結(jié)果是相當(dāng)好的事情。
　　在“更好的”方法中，為了允許這些特殊的特性，我可以結(jié)合使用了 DBMS_SQL 的動(dòng)態(tài) SQL 和管道（pipelined）函數(shù)。在這段代碼中，函數(shù)自動(dòng)為每個(gè)表構(gòu)建一個(gè) SQL 語(yǔ)句，這個(gè)SQL 語(yǔ)句包括 ROWID 和一系列記錄，數(shù)據(jù)還被排序（強(qiáng)迫所有的類型轉(zhuǎn)為字符數(shù)據(jù)以使得排序和比較操作可以正確工作——雖然下面的例子過(guò)于簡(jiǎn)單），而且對(duì)比每一列的值。它使用數(shù)據(jù)字典表來(lái)描述表：
　　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


　　因?yàn)榉祷亓?ROWID，所以你可以操作從查詢得來(lái)的原始記錄。如果你想要更改原始表中的數(shù)據(jù)以避免出現(xiàn)“變異表”問(wèn)題，那么你可能需要將 ROWID 臨時(shí)存儲(chǔ)在某個(gè)地方。
　　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 = '>');