Perl实现不同数据库结果比较 (mysql, oracle, sqlserver)

# 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.txtn";
    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') || 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;",'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 "$outnamen" 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;
    }
}