perl oracle 批量运行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);

转载于:https://my.oschina.net/suzm/blog/342737