批量执行sql

#!/usr/bin/perl -w

#Description：load data

#Auther：suzm

#Date  :2014-11-10

use DBI;

use strict;

push(@INC,'/home/tpms_dev/catpm/current/batch/lib/perl');

require public_pg;

 

my $path_log=$ENV{TPMS_EOD_LOGPATH};

unless(defined($ARGV[0])){

     $ARGV[0]="error";

}

 

my $log_name=CITIC::getscript_name($0)."_".CITIC::getscript_name($ARGV[0]);

my $log_file=CITIC::create_logfile($log_name,$path_log);

my $sqlfile=undef;

my $sqlpath=$ENV{TPMS_EOD_SQLPATH};

my $TPMS_EOD_SID=$ENV{TPMS_EOD_SID};

select $log_file;

$|=1;

 

#解析SQL数据文件，把sql语句解析出来,return arraylist;

sub analyze_sqlfile{

     my ($filename)=shift;   

     my $sql;

     my @batch_sql;

     eval{

          #print "${sqlpath}${sqlfile}\n";

          open(SQLFILE,"<:encoding(utf8)","${sqlpath}${sqlfile}") or die "Can't open file :$!\n";

          };

     if($@){

          CITIC::showtime(); print "An error occurred ($@)\n" ;

          return 1;

     }

     while(<SQLFILE>){

          if($_ =~ /;$/ ){

               $sql .=$_;

               push(@batch_sql,$sql);

               $sql=undef;

       

     # print $_;

          }else{

               $sql .=$_;

          }

          # print;

     }

     close SQLFILE;

      

return @batch_sql;

}

 

#sql excute method

sub excute_sql{

     my ($dbh,@sql_sth)=@_;

     foreach my $str (@sql_sth){

          $str=~ s/;$//g;

    #     my $rec=$dbh->do($sth);

          my $sth=$dbh->prepare($str);

          my $rec=$sth->execute();

          if( $str =~ /^\s+select/i){

                

               my $table=$sth->fetchall_arrayref();

               my($i,$j);

               if($str =~ /(is\s+null)$/i){     

                          

                    if($#{$table}>=0){

                         CITIC::showtime();print "主键含有null值,下面是SQL语句：$str\n以下是含有null值的数据：\n";

                         print "#" x 200 ."\n";

                         my $fields = $sth->{NUM_OF_FIELDS}; #获得当前表的字段数

                         for(my $nu=0;$nu<$fields;$nu++){

                              my $col_name=$sth->{NAME}->[$nu]; #字段名称

                               #my $col_type=$sth->{TYPE}->[$i]; #字段类型

                               printf "%-30.35s","$col_name";

                         }

                         print "\n";

                         for $i (0..$#{$table}){

                              for $j (0..$#{$table->[$i]}){

                                   unless( defined($table->[$i][$j])){

                                        $table->[$i][$j]="null" 

                                   }

                                   printf "%-30.35s","$table->[$i][$j]";

                              }

                              print "\n";

                              print "#" x 200 ."\n";

                         }

                         $sth->finish();

                         return 1;

                    }else{

                         CITIC::showtime();print "主键没有null值\n";

                    }

               }else{

                    if($#{$table}>=0){

                         CITIC::showtime();print "有重复数据,下面是SQL语句：$str\n以下是主键重复的数据\n";

                         print "#" x 200 ."\n";

                         my $fields = $sth->{NUM_OF_FIELDS}; #获得当前表的字段数

                         for(my $nu=0;$nu<$fields;$nu++){

                              my $col_name=$sth->{NAME}->[$nu]; #字段名称

                               #my $col_type=$sth->{TYPE}->[$i]; #字段类型

                               printf "%-30.35s","$col_name";

                         }

                         print "\n";

                         for $i (0..$#{$table}){

                              for $j (0..$#{$table->[$i]}){

                                   unless( defined($table->[$i][$j])){

                                        $table->[$i][$j]="null" 

                                   }

                                   printf "%-30.35s","$table->[$i][$j]";

                              }

                              print "\n";

                              print "#" x 200 ."\n";

                         }

                         $sth->finish();

                         return 1;

                    }else{

                         CITIC::showtime();print "没有重复数据\n";

                    }

               }

               $sth->finish();

          }

          unless($rec){

               #$dbh->rollback;

               CITIC::showtime(); print "excute error! \n".$dbh->errstr."\n";

               #CITIC::showtime(); print "-" x 15 . "语句执行失败！" ."-" x 15 ."\n"; 

               #CITIC::showtime(); print "$str\n" ;

               #CITIC::showtime(); print "-" x 15 . "SQL END" ."-" x 15 . "\n";

               $dbh->disconnect();

               return 1;

          }

          #$dbh->commit;

          #CITIC::showtime(); print "-" x 15 . "语句执行成功！" ."-" x 15 ."\n"; 

          CITIC::showtime(); print "$str\n";

          if($rec eq "0E0"){

               $rec=0;

          }

          CITIC::showtime(); print "#" x 15 ." is complete, 受影响的行数:$rec行"."#" x 15 . "\n";

          #CITIC::showtime(); print "#" x 15 . "受影响的行数:$rec行" ."#" x 15 . "\n"; 

          #CITIC::showtime(); print "-" x 15 . "SQL END" ."-" x 15 . "\n";

           

     }

 

     $dbh->disconnect();

     return 0;

}

 

#程序入口

sub main{

      

     my $ret;

     my $dbh;

     if (analyze_sqlfile($sqlfile)==1) {

          $ret = 1;

     }else{

   

     my %dbc_info=CITIC::get_dbc_info($TPMS_EOD_SID);

   

     unless (%dbc_info) {

               CITIC::showtime();print "Failed to get database information!\n";

   

     }else{

   

          $dbh=CITIC::connect_db($dbc_info{"ip"},$dbc_info{"port"},$dbc_info{"sid"},$dbc_info{"user"},$dbc_info{"pwd"});

   

     }

   

     unless($dbh){

   

          $ret = 1;

   

     }else{

   

          my @sql_queue=analyze_sqlfile($sqlfile);

   

          $ret=excute_sql($dbh,@sql_queue);

   

     }

     }

     print "return code is $ret\n";

     CITIC::close_logfile($log_file);

     return $ret;     

}

 

open(STDERR,">&STDOUT");

 

#if($ARGV[0] eq "error"){

#     $ARGV[0]=undef;

#}

if($#ARGV<0){

     print "Please input parameters,for example:\n1.tablename.sql\n";

     CITIC::close_logfile($log_file);

     exit(1);

}

 $sqlfile=$ARGV[0];

 

my $ret=main();

if($ret == 0){

     print STDOUT "complete";

}else{

     print STDOUT "fail";

}

exit($ret);