1. sql.txt中有多条sql语句,如下:
-
SELECT * FROM TESTA
-
SELECT * FROM TESTB
-
SELECT * FROM TESTC
-
SELECT * FROM TESTD
-
SELECT * FROM TESTE
-
SELECT * FROM TESTF
2. perl脚本如下,执行的结果会放在与表对应的csv文件中
-
use strict;
-
use warnings;
-
use DBI;
-
-
main();
-
-
sub main
-
{
-
my $dbh_oracle;
-
my $dbh_mysql;
-
open (IN, "sql.txt") or die "$!, opening sql.txt\n";
-
open (FAIL, ">00fail.csv") or die 'Unable to create diff file for 00fail.csv $!';
-
while (<IN>)
-
{
-
chomp;
-
# Oracle
-
$dbh_oracle=DBI->connect("DBI:Oracle:host=xxx.xx.xx.xxx;service_name=xxx;",'xxx','xxx') || die "cannot connect to Oracle:$!\n";
-
my @oracle = getResult($dbh_oracle, $_);
-
-
# Mysql
-
$dbh_mysql=DBI->connect('DBI:mysql:database=xxxx;host=xxx.xx.xx.xxx','xxx','xxx') || die "cannot connect to mysql:$!\n";
-
my @mysql = getResult($dbh_mysql, $_);
-
-
# SqlServer
-
# my $dbh_sqlServr=DBI->connect("DBI:ADO:driver={SQL Server};Server=localhost; database=xxxx;", 'xxx', 'xxxx') || die "cannot connect to mysql:$!\n";
-
# my @sqlserver = getResult($dbh_sqlServr, $_);
-
-
my @output = split/ /;
-
my $outputname = $output[-1];
-
compare(\@oracle, \@mysql, $outputname);
-
}
-
-
$dbh_oracle->disconnect();
-
$dbh_mysql->disconnect();
-
-
close IN;
-
close FAIL;
-
}
-
-
sub getResult
-
{
-
my $dbh = shift;
-
my $sql = shift;
-
-
my @A;
-
-
my $sth = $dbh->prepare($sql);
-
$sth->execute;
-
my $numFields = $sth->{'NUM_OF_FIELDS'};
-
-
while (my $ref = $sth->fetchrow_arrayref) {
-
my $line = "";
-
for (my $i = 0; $i < $numFields; $i++) {
-
$line .= $$ref[$i] if(defined $$ref[$i]);
-
$line .= ",";
-
}
-
# print OUT "\n";
-
push @A, $line;
-
}
-
$sth->finish;
-
return @A;
-
}
-
-
sub compare
-
{
-
my ($A_ref, $B_ref, $outname) = @_;
-
-
my %ta;
-
my @onlyA;
-
my @onlyB;
-
my @same;
-
-
foreach (@$A_ref)
-
{
-
chomp;
-
$ta{$_} += 1;
-
}
-
-
my %count = %ta;
-
-
foreach(@$B_ref){
-
chomp;
-
if (exists($ta{$_}) && $ta{$_} > 0){
-
$ta{$_} -= 1;
-
}else
-
{
-
push @onlyB,$_;
-
}
-
}
-
-
my $countA;
-
-
foreach (keys %ta) {
-
my $tmp = $_;
-
if ($ta{$_} >= 0)
-
{
-
#Only A
-
for(1..$ta{$_})
-
{
-
push @onlyA, $tmp;
-
}
-
#Same
-
for(1..($count{$_} - $ta{$_}))
-
{
-
push @same, $tmp;
-
}
-
}
-
}
-
-
if ($#onlyB > 0 or $#onlyA > 0)
-
{
-
print FAIL "$outname\n" if ($#onlyB > 0 or $#onlyA > 0);
-
-
open (OUT, ">$outname.csv") or die 'Unable to create diff file for $outname.csv $!';
-
print OUT "only in Oracle:\n";
-
print OUT $_."\n" foreach @onlyA;
-
-
print OUT "only in mysql:\n";
-
print OUT $_."\n" foreach @onlyB;
-
-
print OUT "SAME:\n";
-
print OUT $_."\n" foreach @same;
-
close OUT;
-
}
-
}
阅读(1134) | 评论(0) | 转发(0) |