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.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;
    }
}