找出两个几乎完全相同的表的不同之处
在历史跟踪或迁移测试中经常使用几乎完全相同的表，在处理这些表时一个常见的任务是将表进行对比并报告或处理不同的记录。在类 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 = '>');
浙公网安备 33010602011771号