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

1. sql.txt中有多条sql语句，如下：

点击(此处)折叠或打开

1. SELECT * FROM TESTA
   
2. SELECT * FROM TESTB
   
3. SELECT * FROM TESTC
   
4. SELECT * FROM TESTD
   
5. SELECT * FROM TESTE
   
6. SELECT * FROM TESTF

2. perl脚本如下，执行的结果会放在与表对应的csv文件中

点击(此处)折叠或打开

1. use strict;
   
2. use warnings;
   
3. use DBI;
   
4. 
   
5. main();
   
6. 
   
7. sub main
   
8. {
   
9.     my $dbh_oracle;
   
10.     my $dbh_mysql;
    
11.     open (IN, "sql.txt") or die "$!, opening sql.txt\n";
    
12.     open (FAIL, ">00fail.csv") or die 'Unable to create diff file for 00fail.csv $!';
    
13.     while (<IN>)
    
14.     {
    
15.         chomp;
    
16.         # Oracle
    
17.         $dbh_oracle=DBI->connect("DBI:Oracle:host=xxx.xx.xx.xxx;service_name=xxx;",'xxx','xxx') || die "cannot connect to Oracle:$!\n";
    
18.         my @oracle = getResult($dbh_oracle, $_);
    
19.                 
    
20.         # Mysql
    
21.         $dbh_mysql=DBI->connect('DBI:mysql:database=xxxx;host=xxx.xx.xx.xxx','xxx','xxx') || die "cannot connect to mysql:$!\n";
    
22.         my @mysql = getResult($dbh_mysql, $_);
    
23.                 
    
24.         # SqlServer
    
25.         # my $dbh_sqlServr=DBI->connect("DBI:ADO:driver={SQL Server};Server=localhost; database=xxxx;", 'xxx', 'xxxx') || die "cannot connect to mysql:$!\n";
    
26.         # my @sqlserver = getResult($dbh_sqlServr, $_);
    
27.         
    
28.         my @output = split/ /;
    
29.         my $outputname = $output[-1];
    
30.         compare(\@oracle, \@mysql, $outputname);
    
31.     }
    
32.     
    
33.     $dbh_oracle->disconnect();
    
34.     $dbh_mysql->disconnect();
    
35.     
    
36.     close IN;
    
37.     close FAIL;
    
38. }
    
39. 
    
40. sub getResult
    
41. {
    
42.     my $dbh = shift;
    
43.     my $sql = shift;
    
44.     
    
45.     my @A;
    
46.     
    
47.     my $sth = $dbh->prepare($sql);
    
48.     $sth->execute;
    
49.     my $numFields = $sth->{'NUM_OF_FIELDS'};
    
50.     
    
51.     while (my $ref = $sth->fetchrow_arrayref) {
    
52.     my $line = "";
    
53.         for (my $i = 0; $i < $numFields; $i++) {
    
54.             $line .= $$ref[$i] if(defined $$ref[$i]);
    
55.             $line .= ",";
    
56.         }
    
57.         # print OUT "\n";
    
58.         push @A, $line;
    
59.     }
    
60.     $sth->finish;
    
61.     return @A;
    
62. }
    
63. 
    
64. sub compare
    
65. {
    
66.     my ($A_ref, $B_ref, $outname) = @_;
    
67.     
    
68.     my %ta;
    
69.     my @onlyA;
    
70.     my @onlyB;
    
71.     my @same;
    
72.     
    
73.     foreach (@$A_ref)
    
74.     {
    
75.         chomp;
    
76.         $ta{$_} += 1;
    
77.     }
    
78. 
    
79.     my %count = %ta;
    
80. 
    
81.     foreach(@$B_ref){
    
82.         chomp;
    
83.         if (exists($ta{$_}) && $ta{$_} > 0){
    
84.             $ta{$_} -= 1;
    
85.         }else
    
86.         {
    
87.             push @onlyB,$_;
    
88.         }
    
89.     }    
    
90.     
    
91.     my $countA;
    
92.     
    
93.     foreach (keys %ta) {
    
94.         my $tmp = $_;
    
95.         if ($ta{$_} >= 0)
    
96.         {
    
97.             #Only A
    
98.             for(1..$ta{$_})
    
99.             {
    
100.                 push @onlyA, $tmp;
     
101.             }
     
102.             #Same
     
103.             for(1..($count{$_} - $ta{$_}))
     
104.             {
     
105.                 push @same, $tmp;
     
106.             }
     
107.         }
     
108.     }
     
109.     
     
110.     if ($#onlyB > 0 or $#onlyA > 0)
     
111.     {
     
112.         print FAIL "$outname\n" if ($#onlyB > 0 or $#onlyA > 0);
     
113.         
     
114.         open (OUT, ">$outname.csv") or die 'Unable to create diff file for $outname.csv $!';
     
115.         print OUT "only in Oracle:\n";
     
116.         print OUT $_."\n" foreach @onlyA;
     
117.         
     
118.         print OUT "only in mysql:\n";
     
119.         print OUT $_."\n" foreach @onlyB;        
     
120.         
     
121.         print OUT "SAME:\n";
     
122.         print OUT $_."\n" foreach @same;
     
123.         close OUT;
     
124.     }
     
125. }