|
在歷史跟蹤或遷移測試中經常使用幾乎完全相同的表，在處理這些表時一個常見的任務是將表進行對比并報告或處理不同的記錄。在類 UNIX 環境中，有一個用于比較文件的 diff 命令，在 Windows 中，有一個 FC 命令。如果傳入兩個文件的名稱，它將會顯示要添加或刪除哪些行才能使文件完全相同。
在 Oracle 數據庫中，沒有標準的類 diff 的命令（雖然有很多非 Oracle 工具可以在數據庫之外完成這一任務）。其實在 SQL 中有一些方法可以用來進行類 diff 的對比，但是這里有一個最簡單的方法，也有一個（對于非標準數據的）比較好的方法。
首先，我們將創建示范這一功能所需的測試數據。我們還同時創建第二個完全相同的表，第二個表比第一個表少了一行記錄，又添加了一行記錄。
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 返回第一個查詢中出現而第二個查詢中沒有出現的記錄；INTERSECT 返回兩個查詢中都出現的記錄；UNION 返回兩個查詢的記錄。
這個例子中使用的方法與 diff 稍微有點相似，我們使用 < 字符指出只出現第一個表中的記錄，使用 > 字符指出只出現第二個表中的記錄，使用等號（=）指出兩個表中都出現的記錄。下面是顯示兩個表的不同之處的一個簡單方法：
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
你可以將這個查詢保存在某個地方，或者創建一個腳本保存在 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
你甚至還可以對比兩個查詢——只要它們返回的列數相同，并且列的類型相互兼容：
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:
如果你的表不是標準表，那么這種方法就存在一個問題。集合操作會對返回的記錄進行一個 DISTINCT 操作。如果在你的表中有重復的記錄，那么它們將不會作為不同的的記錄返回�？紤]下面這個例子：
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
結果顯示一切都好，即使你有重復的數據。此外，這個數據作為報表看上去相當好，而且如果知道每個表的具體結構，你還可以使用這個結果刪除重復的記錄或插入缺少的數據，實際上能夠使用通用查詢結果是相當好的事情。
在“更好的”方法中，為了允許這些特殊的特性，我可以結合使用了 DBMS_SQL 的動態 SQL 和管道（pipelined）函數。在這段代碼中，函數自動為每個表構建一個 SQL 語句，這個SQL 語句包括 ROWID 和一系列記錄，數據還被排序（強迫所有的類型轉為字符數據以使得排序和比較操作可以正確工作——雖然下面的例子過于簡單），而且對比每一列的值。它使用數據字典表來描述表：
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，所以你可以操作從查詢得來的原始記錄。如果你想要更改原始表中的數據以避免出現“變異表”問題，那么你可能需要將 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 = '>');
|
