数据仓库之历史数据缺少字段脚本

脚本使用perl DBI，获取相关错误信息再把错误中的字段提取出来判断是某字段少后直接添加该字段，然后再select 相关数据出来，有朋友建议采用数组的方式处理，不过目前还不太清楚怎么倒腾。呵呵

use DBI;
my $driver="DBI:mysql";
my $database="sql";
my $user="root";
my $password="pass";
my $host="localhost";
my $str;
my $datapath="/sqldb/setup/data/";
my $file="$datapath/tbl_sch_charactor$ARGV[0].txt";
my $accountfile="$datapath/tbl_sch_account$ARGV[0].txt";
my $sql="select id,guid,name,gender,tg,`to`,classId,exp,expSkill,exPoint from tbl_sch_charactor order by id;";
my $account="select id,last,lastIp from tbl_sch_account;";

my $dbh=DBI->connect("$driver:database=$database;host=$host;user=$user;password=$password") ;
$dbh->do("set names utf8;") or die "Can't set names". dbh->errstr;
my $stha=$dbh->prepare($account) or die "Can't connect: ". stha->err;
$stha->execute() or die stha->err;
print "#"x20,"\n","deal with account table\n";
my($id,$last,$lastIP);
$stha->bind_columns(\$id,\$last,\$lastIP);
open (HEAD,">$accountfile")|| die "Can't open $accountfile:$!\n";
printf HEAD "%s\t%-s\t%-s\n","id","last","lastIP";

while($stha->fetch()){
open (HEAD,">>$accountfile")|| die "Can't open newfile:$!\n";
printf HEAD "%s\t%-s\t%-s\n",$id,$last,$lastIP;
}

 

#tbl_sch_charactor 

print "\ndeal with tbl_sch_charactor\n\n";
my $sth=$dbh->prepare($sql) or die "Can't connect: ". sth->errstr ;
$sth->execute() or  $str=$sth->errstr;
print "*"x20,"\n";
printf "\nselected data for sql.tbl_sch_charactor.\n";
if(!$str){#判断str是否为空，为空执行里面语句!str为空
 #提取数据
print "The data is normal!\n";
my($id,$guid,$name,$gender,$tg,$to,$classId,$exp,$expSkill,$exPoint);
$sth->bind_columns(\$id,\$guid,\$name,\$gender,\$tg,\$to,\$classId,\$exp,\$expSkill,\$exPoint);
open (SELECTOUT,">$file")|| die "Can't open newfile:$!\n";
printf SELECTOUT "%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\n","id","guid","name","gender","tg","to","classId","exp","expSkill","exPoint";

while($sth->fetch()){
open (SELECTOUT,">>$file")|| die "Can't open newfile:$!\n";
printf SELECTOUT "%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\n",$id,$guid,$name,$gender,$tg,$to,$classId,$exp,$expSkill,$exPoint;
} }else{  
print "The data have some problem !deal with it now\n";
@error=split ("'",$str);
print "$error[1]","\n";
print "#"x100,"\n";
 if ($error[1] eq exPoint){
 $dbh->do(qq/alter table tbl_sch_charactor add column exPoint  bigint(20) default 0;/) or die "alter table erorr";
 $dbh->do(qq/insert into tbl_sch_charactor($error[1]) values(NULL)/);
 }elsif($error[1] eq expSkill ){
  $dbh->do(qq/alter table tbl_sch_charactor add column expSkill bigint(20) not null;/) or die "alter table erorr";
         $dbh->do(qq/insert into tbl_sch_charactor($error[1]) values('0')/);
  }
my $sth=$dbh->prepare($sql) or die sth->errstr ;
$sth->execute() or die sth->errstr;
#提取数据
my($id,$guid,$name,$tg,$to,$exp,$expSkill,$exPoint);
$sth->bind_columns(\$id,\$guid,\$name,\$tg,\$to,\$exp,\$expSkill,\$exPoint);
open (SELECTOUT,">$file")|| die "Can't open newfile:$!\n";
printf SELECTOUT "%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\n","id","guid","name","gender","tg","to","classId","exp","expSkill","exPoint";

while($sth->fetch()){
open (SELECTOUT,">>$file")|| die "Can't open newfile:$!\n";
printf SELECTOUT "%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\t%-s\n",$id,$guid,$name,$gender,$tg,$to,$classId,$exp,$expSkill,$exPoint;
}

}

 

$stha->finish();
$sth->finish();
$dbh->disconnect();
print "deal with the tables over!\n\n";
print "*"x20,"\n";

本文转自 qwjhq 51CTO博客，原文链接:http://blog.51cto.com/bingdian/270341