perl实现数据的导出

写了一个导出用户下表的数据的工具，

 

使用方法如下

./expdp.pl

 hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0

 This script will auxiliary data and struct.
 -h                - Give help screen
 -E                - for export dict
 -D  export        - for export dict's data
 -T  drop          - for export table's data
 Examples:
   auxiliary.pl -h/H
   auxiliary.pl -e/E
   auxiliary.pl -d/D
   auxiliary.pl -t/T

还需要一个配置文件

 

[oracle@localhost DBD]$ cat exp.dict
dsg.test #owner  这里可以是用户也可以是用户.表的格式存在。

 

 

#!/usr/bin/perl

# dbusers - manage MySQL user table

use ExtUtils::testlib;

use Getopt::Long;
use Config;
use Getopt::Std;
use Oraperl;
$dbname="dsg";
$dbuser="dsg";
$dbpasswd="dsg";
format HEADER =

 hbjxzl.cublog.cn auxiliary Data Utility Version 1.0.0

 This script will auxiliary data and struct.
 -h - Give help screen
 -E - for export dict
 -D export - for export dict's data
 -T drop - for export table's data
 Examples:
   auxiliary.pl -h/H
   auxiliary.pl -e/E
   auxiliary.pl -d/D
   auxiliary.pl -t/T
.

sub do_help {
  # Give help screen and exit

  $~ = "HEADER";
  write;
  exit();
}

sub replace_null_to_{
  $str=shift;
  if($str){
   $str =~ s/ /_/g;
  }
  return $str;
}

sub replace_enter_to_{
  $str=shift;
  if($str){
   $str =~ s/\n//g;
  }
  return $str;
}

sub write_file{
  $put_name=shift;
  $put_trunc_name=shift;
  $put_drop_name=shift;
  open(OUTPUT, "> $put_name ")
    or die "Couldn't open $put_name for write: $!\n";
  if ($put_trunc_name){
    open(TRUNCATE_OUTPUT, "> $put_trunc_name ")
      or die "Couldn't open $put_trunc_name for write: $!\n";
  }
  if ($put_drop_name){
    open(DROP_OUTPUT, "> $put_drop_name ")
      or die "Couldn't open $put_drop_name for write: $!\n";
  }
}
sub close_write_file{
  close(OUTPUT);
  close(TRUNCATE_OUTPUT);
  close(DROP_OUTPUT);
}

sub read_file{
  $get_name=shift;
  open(INPUT, "< $get_name ")
    or die "Couldn't open $put_name for read: $!\n";
}

sub close_read_file{
  close(INPUT);
}

sub connect_db{
    my $dbname=shift;
    my $dbuser=shift;
    my $dbpasswd=shift;
    #print " test dbname is ",$dbname," user is ", $dbuser," passwd is ", $dbpasswd,"\n";

    $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser,$dbpasswd,{ RaiseError=>1 });
    $dbh->{LongReadLen} = 512 * 1024;
    return $dbh;
}

sub dis_connect_db{
    $dbh->disconnect;
}
sub print_array{
   my @cfg_lines=split(" ",$_);
   foreach (@cfg_lines){
      print $_,"\t";
   }
   print "\n";
   return @cfg_lines;
}

sub obj_sql{
  my $sql_code=shift;
  my $get_sql="";
  if($sql_code ==1){
    $get_sql="select owner,table_name,column_name,data_type,data_length from dba_tab_columns where owner = ? and table_name = ? ";
 #for all table's column

  }elsif($sql_code==2){
    $get_sql="select owner,table_name from dba_tables where owner like ? and cluster_name is null and iot_type is null";
  }elsif($sql_code==3){
  }
  return $get_sql;
}

sub tab_col_sql{
  my $exp_owner=shift;
  my $exp_tab =shift;
  my $exp_dict_file=lc shift;
  my $select_col="";
  #print ">>test exp_owner is ",uc $exp_owner,"\n";

  #print ">>test exp_tab is ",$exp_tab,"\n";

  #print ">>test exp_dict_file is ",$exp_dict_file,"\n";

  open(INPUT_COL,"< $exp_dict_file") or die "Couldn't open $exp_dict_file for read: $!\n";
    while(<INPUT_COL>){
      chomp;
      @line_raw=split(" ",$_);
      $exp_tab =~ s/\$/\_/g;
      $exp_owner =~ s/\$/\_/g;
      #print "exp_tab is ",$exp_tab,"\n";

      $match_var=$line_raw[0];
      $match_var =~ s/\$/\_/g;
      if( $match_var =~ m/\"$exp_owner\"\.\"$exp_tab\"/ ){
         $select_col=join(",",$select_col,$line_raw[1]);
      # print "Test select col ",$select_col,"\n";
      }
    }
    $select_col=substr($select_col,1,length($select_col)-1);
   #print "test select col ",$select_col,"\n";
  close(INPUT_COL);
  return $select_col;
}

sub tab_dict{
    my $dbh = shift;
    my $tab_owner= shift;
    my $tab_name = shift;
    my $obj_type= shift;
    my $get_obj_sql;
    $put_file_name=lc join(".",$tab_owner,$tab_name,"dmp");
    if($obj_type==1){
      $get_obj_sql=&obj_sql($obj_type);
    }elsif($obj_type==2){
      my $read_file=join(".",$tab_owner,"dict");
      $get_obj_sql=&tab_col_sql(uc $tab_owner,uc $tab_name,$read_file);
      $get_obj_sql=join(" ","select",$get_obj_sql,"from",$tab_owner);
      $get_obj_sql=join(".",$get_obj_sql,$tab_name);
      #print $get_obj_sql,"\n";
    }
    my $sth = $dbh->prepare($get_obj_sql);
    if($obj_type==1){
       $sth->execute($tab_owner,$tab_name);
    }elsif($obj_type==2){
       $sth->execute;
    }
    $tab_text="";
    
    if($obj_type==2){
      open(OUT_PUT, "> $put_file_name ")
        or die "Couldn't open $put_file_name for write: $!\n";
    }
    while (my @row= $sth->fetchrow){
       my $arr_len=scalar @row;
       if($obj_type==2){
           for($i = 0 ; $i < $arr_len ; $i ++){
             print OUT_PUT $row[$i],"\t";
           }
         print OUT_PUT "\n";
       }else{
         $tab_text=join("\"",$tab_text,$row[0]);
         $tab_text=join("\".\"",$tab_text,$row[1]);
         $tab_text=join("\"\t\"",$tab_text,$row[2]);
         $tab_text=join("\"\t",$tab_text,$row[3]);
         if ($row[3] =~ m/NUMBER/ || $row[3] =~ m/CHAR/ || $row[3] =~ m/ROW/){
           $tab_text=join("(",$tab_text,$row[4]);
           $tab_text=join(")",$tab_text,"");
         }
             $tab_text=join("\n",$tab_text,"");
       }
     }
     $sth->finish();
    if($obj_type==1){
      print OUTPUT "="x60,"\n";
      print OUTPUT $tab_text;
    }elsif($obj_type==2){
      close(OUT_PUT);
    }
}

sub owner_all_tab{
    my $dbh =shift;
    my $exp_own =shift;
    my $oper_p =shift;
    my $get_obj_sql=&obj_sql(2);
    #print "test exp_owner is ",uc $exp_own,"\n";
    #print "test get_obj_sql is ",$get_obj_sql,"\n";
    my $sth = $dbh->prepare($get_obj_sql);
       $sth->execute($exp_own);
    my $exp_process_num=0;
    while (@row= $sth->fetchrow){
       print $row[0],"\t",$row[1]," Please waiting....";
       &tab_dict($dbh,$row[0],$row[1],$oper_p);
       $exp_process_num++;
       print " over \n";
    }
       $sth->finish();
    print "\n";
}

sub oper_from_file{
 $oper_p =shift;
 &connect_db($dbname,$dbuser,$dbpasswd);
  &read_file("exp.dict") or die "Cannot get exp.dict for reading !";
    while(){
     chomp;
     if(/^\s*#/ or /^\s*$/){
        next;
      }
     s/\s*#.*//;
     tr/\t //d;
    my @exp_dict= split /\./;
    my $exp_owner=$exp_dict[0];
    my $exp_tab=$exp_dict[1];
    my $exp_dict_file =join(".",$exp_owner,"dict");
    #print "oper is ",$oper_p," exp_owner is ",$exp_owner," exp_dict_file is ",$exp_dict_file,"\n";
     if ($oper_p==1){
       &write_file($exp_dict_file);
       &owner_all_tab($dbh,uc $exp_owner,$oper_p);
       &close_write_file;
     }elsif($oper_p == 2){
       &owner_all_tab($dbh,uc $exp_owner,$oper_p);
     }elsif($oper_p==3){
       #my $put_file_name=join(".",$exp_owner,$exp_tab,"dat");
       #print "exp_owner is $exp_dict[0]\t exp_tab is ",$exp_tab,"\t",$put_file_name,"\n" ,
       #print "Exp table's data!\n";
       &tab_dict($dbh,$exp_owner,$exp_tab,2);
     }
   }
  &close_read_file;
  &dis_connect_db();
}

sub do_opts{
  %options=();
  getopts("eEdDtThH",\%options);
  &do_help if ($options{h} or $options{H});
  # like the shell getopt, "d:" means d takes an argument
  if (defined $options{e}){
     &oper_from_file(1);
  }elsif(defined $options{E}){
     &oper_from_file(1);
  }elsif( defined $options{d}){
     &oper_from_file(2);
  }elsif( defined $options{D}){
     &oper_from_file(2);
  }elsif( defined $options{t}){
     &oper_from_file(3);
  }elsif( defined $options{T}){
     &oper_from_file(3);
  }elsif( defined $options{h}){
     $do_help;
  }elsif( defined $options{H}){
     $do_help;
  }else{
    print "test opts Err:\n";
  }
}

$argv=scalar @ARGV;
#print "test argv is :",$argv,"\n