可以自己写一个存储过程来解决,使用两个游标,这样会方便很多,如下: create or replace procedure cmp_not_pk() as table_dba dba%rowtype; table_dbb dbb%rowtype; cursor cur_dba is select * from dba; cursor cur_dbb is select * from dbb; begin open cur_dba; fetch cur_dba into table_dba; while cur_dba%found loop open cur_dbb; fetch cur_dbb into table_dbb;
while cur_dbb%found loop if (table_dba.pk = table_dbb.pk) if ((table_dba.not_pk1 = table_dbb.not_pk1) and (talbe_dba.not_pk2 = table_dbb.not_pk2) /*条件可以很多*/ dbms_output.put_line(table_dba.pk); end if; end if; end loop;
close cur_dbb; fetch cur_dba into table_dba; end loop;
close cur_dba; end; 这个过程可以完成,但在数据很多的情况下效率会比较低,希望大家共同探讨
方法1,可以自己写存储过程解决。不过先要建立一个DBLINK,因为你是不同的数据库 方法2,我写了一个Perl程序帮你解决实施步骤 1、先到www.activeperl.com下载一个activeperl(ActivePerl-5.6.1.631-MSWin32-x86.msi) 2、安装activeperl 3、在perl的安装目录perl\bin\执行如下命令,必须是联网状态 ppm install dbi 然后install dbd-oracle 4、执行我下面的程序compare2table.pl即可,直接在命令窗口键入这个程序名即可,就会执行 compare2table.pl >result.txt 表示将比较的结果输出到result.txt 5、我的程序里面的数据库的连接名,用户名还有密码,SQL语句等你自己改改 由于你的issue关系到两个数据库,所哟我的程序里有两个连接名,两个用户 相信你能看懂。。 #!/usr/bin/perl #function: compare records with same PKs between two tables #author: atgc #version: 1.0 #date of compilation 24 November ,2004#you can change the following parameters, table1 my $conn_str1 = 'AT'; my $user1 = 'system'; my $pass1 = 'genomics'; my $sql1 = "select a.pa,a.* from TBA a"; #you can change the following parameters, table2 my $conn_str2 = 'GC'; my $user2 = 'system'; my $pass2 = 'genomics'; my $sql2 = "select b.pa,b.* from TBB b";use DBI; $dbh = DBI->connect("DBI:Oracle:$conn_str1",$user1,$pass1); $sth = $dbh->prepare($sql1); $sth->execute(); while(@fields = $sth->fetchrow) { $rowstr=""; foreach $ele(@fields){$rowstr.="$ele\t";} $rowstr=~m/(^.+?)\t/; $hash_table1{$1}=$rowstr; } $sth->finish(); $dbh->disconnect();$dbh = DBI->connect("DBI:Oracle:$conn_str2",$user2,$pass2); $sth = $dbh->prepare($sql2); $sth->execute(); while(@fields = $sth->fetchrow) { $rowstr=""; foreach $ele(@fields){$rowstr.="$ele\t";} $rowstr=~m/(^.+?)\t/; $hash_table2{$1}=$rowstr if (exists($hash_table1{$1})); } $sth->finish(); $dbh->disconnect();foreach $keyname(keys %hash_table2) { if ($hash_table1{$keyname} eq $hash_table2{$keyname}) { print "$keyname\tsame\n"; } else { print "$keyname\tdifference\n"; } } undef(%hash_table1); undef(%hash_table2);
SQL> select * from aa; PA NAME SCORE ---------- -------- ---------- 1 aa 7.2 2 g7a 70SQL> select * from bb; PA NAME SCORE ---------- -------- ---------- 1 aa 7.2 2 g7a 80 3 ff 90D:\>compare2table.pl >result.txtD:\>more result.txt 1 same 2 difference
嗯,用minus的确可以查出两张表ID相同,但是字段不同的记录 SQL> select * from aa; PA MYNAME SCORE ---------- -------- ---------- 1 aa 7.2 2 g7a 70SQL> select * from bb; PA NAME SCORE ---------- -------- ---------- 1 aa 7.2 2 g7a 80 3 ff 90SQL> select bb.* from bb,aa where aa.pa=bb.pa 2 minus select aa.* from aa,bb where aa.pa=bb.pa; PA NAME SCORE ---------- -------- ---------- 2 g7a 80
用union更好一些 还可以同时查出,相同的和不一样的记录 SQL> select pa,count(pa) from 2 ( 3 select bb.* from bb,aa where aa.pa=bb.pa 4 union 5 select aa.* from aa,bb where aa.pa=bb.pa 6 ) 7 group by pa 8 / PA COUNT(PA) ---------- ---------- 1 1 2 2 COUNT(PA)等于1,表示两张表的记录一样 等于2,表示PA一样,但是记录内容有不同的地方不过我的程序可以找出那个字段不同,用一句SQL语句恐怕比较困难。。
create or replace procedure cmp_not_pk() as
table_dba dba%rowtype;
table_dbb dbb%rowtype; cursor cur_dba is select * from dba;
cursor cur_dbb is select * from dbb;
begin
open cur_dba;
fetch cur_dba into table_dba;
while cur_dba%found loop
open cur_dbb;
fetch cur_dbb into table_dbb;
while cur_dbb%found loop
if (table_dba.pk = table_dbb.pk)
if ((table_dba.not_pk1 = table_dbb.not_pk1) and (talbe_dba.not_pk2 = table_dbb.not_pk2)
/*条件可以很多*/
dbms_output.put_line(table_dba.pk);
end if;
end if;
end loop;
close cur_dbb;
fetch cur_dba into table_dba;
end loop;
close cur_dba;
end;
这个过程可以完成,但在数据很多的情况下效率会比较低,希望大家共同探讨
最简单的办法就是,
取出tba的所有记录,
循环: 按tba的主键查找tbb的记录.
比较所有子段.
方法2,我写了一个Perl程序帮你解决实施步骤
1、先到www.activeperl.com下载一个activeperl(ActivePerl-5.6.1.631-MSWin32-x86.msi)
2、安装activeperl
3、在perl的安装目录perl\bin\执行如下命令,必须是联网状态
ppm install dbi
然后install dbd-oracle
4、执行我下面的程序compare2table.pl即可,直接在命令窗口键入这个程序名即可,就会执行
compare2table.pl >result.txt
表示将比较的结果输出到result.txt
5、我的程序里面的数据库的连接名,用户名还有密码,SQL语句等你自己改改
由于你的issue关系到两个数据库,所哟我的程序里有两个连接名,两个用户
相信你能看懂。。
#!/usr/bin/perl
#function: compare records with same PKs between two tables
#author: atgc
#version: 1.0
#date of compilation 24 November ,2004#you can change the following parameters, table1
my $conn_str1 = 'AT';
my $user1 = 'system';
my $pass1 = 'genomics';
my $sql1 = "select a.pa,a.* from TBA a";
#you can change the following parameters, table2
my $conn_str2 = 'GC';
my $user2 = 'system';
my $pass2 = 'genomics';
my $sql2 = "select b.pa,b.* from TBB b";use DBI;
$dbh = DBI->connect("DBI:Oracle:$conn_str1",$user1,$pass1);
$sth = $dbh->prepare($sql1);
$sth->execute();
while(@fields = $sth->fetchrow)
{
$rowstr="";
foreach $ele(@fields){$rowstr.="$ele\t";}
$rowstr=~m/(^.+?)\t/;
$hash_table1{$1}=$rowstr;
}
$sth->finish();
$dbh->disconnect();$dbh = DBI->connect("DBI:Oracle:$conn_str2",$user2,$pass2);
$sth = $dbh->prepare($sql2);
$sth->execute();
while(@fields = $sth->fetchrow)
{
$rowstr="";
foreach $ele(@fields){$rowstr.="$ele\t";}
$rowstr=~m/(^.+?)\t/;
$hash_table2{$1}=$rowstr if (exists($hash_table1{$1}));
}
$sth->finish();
$dbh->disconnect();foreach $keyname(keys %hash_table2)
{
if ($hash_table1{$keyname} eq $hash_table2{$keyname})
{
print "$keyname\tsame\n";
}
else
{
print "$keyname\tdifference\n";
}
}
undef(%hash_table1);
undef(%hash_table2);
---------- -------- ----------
1 aa 7.2
2 g7a 70SQL> select * from bb; PA NAME SCORE
---------- -------- ----------
1 aa 7.2
2 g7a 80
3 ff 90D:\>compare2table.pl >result.txtD:\>more result.txt
1 same
2 difference
建好dblink后,用 minus函数
minus用起来很简单,楼主试试看就知道了
SQL> select * from aa; PA MYNAME SCORE
---------- -------- ----------
1 aa 7.2
2 g7a 70SQL> select * from bb; PA NAME SCORE
---------- -------- ----------
1 aa 7.2
2 g7a 80
3 ff 90SQL> select bb.* from bb,aa where aa.pa=bb.pa
2 minus select aa.* from aa,bb where aa.pa=bb.pa; PA NAME SCORE
---------- -------- ----------
2 g7a 80
还可以同时查出,相同的和不一样的记录
SQL> select pa,count(pa) from
2 (
3 select bb.* from bb,aa where aa.pa=bb.pa
4 union
5 select aa.* from aa,bb where aa.pa=bb.pa
6 )
7 group by pa
8 / PA COUNT(PA)
---------- ----------
1 1
2 2
COUNT(PA)等于1,表示两张表的记录一样
等于2,表示PA一样,但是记录内容有不同的地方不过我的程序可以找出那个字段不同,用一句SQL语句恐怕比较困难。。
不过我觉得还是SQL用起来简单