ChinaHost

 找回密码
 用户.注册
查看: 9845|回复: 0

[服务器经验交流] sqlserver中的存储过程注释后创建到mysql中

[复制链接]
发表于 2014-5-15 16:02:03 | 显示全部楼层 |阅读模式

 把sqlserver中的存储过程注释后创建到mysql中
  #!/usr/bin/perl
  use DBI;
  use Switch;
  use Encode;
  use Encode::CN;
  my $source_name = "sqldb";
  my $source_user_name = "sa";
  my $source_user_psd = "123";
  my $db_name="mysqldb";
  my $location="192.168.0.208";
  my $port="3306";
  my $db_user="zoe";
  my $db_pass="123";
  my $dbh=DBI->connect("dbi:ODBCsource_name",$source_user_name,$source_user_psd);
  #获取所有的用户表
  my $sth=$dbh->prepare("select name from sys.objects where type='P'");
  $sth->execute();
  my $n=0;
  my $ok=0;
  my  $sort_column="";
  while (@data=$sth->fetchrow_array())
  {
  #print $data[0];
  print '正在查询表'.$data[0]."的存储过程\n";
  my  $sql_create="EXEC Sp_HelpText '$data[0]'";
  my $dbh_mssql=DBI->connect("dbi:ODBCsource_name",$source_user_name,$source_user_psd,{RaiseError =>1});
  $dbh_mssql->{LongTruncOk}=1;
  $dbh_mssql->{LongReadLen}=1048576;
  my $sth_select=$dbh_mssql->prepare($sql_create);
  $sth_select->execute() or die 'Cannot execute: '. $sth_select->errstr();
  my $select_col;
  my $select_data;
  while($select_data=$sth_select->fetchrow_array())
  {
  $select_col.=$select_data;
  }
  do_sql($data[0],$select_col);
  }
  $sth->finish;
  $dbh ->disconnect;
  print '所有表的存储过程创建结束'."\n";
  sub do_sql
  {
  print '开始创建'.$_[0].'表的存储过程'."\n";
  my $sql=$_[1];
  open(FILE,"》createtableallproduce.txt");
  syswrite(FILE,"$n\n");
  syswrite(FILE,"$data[0]\n");
  syswrite(FILE,"$sql\n");
  close(FILE);
  $sql=~s/\/\*/------注释开始--------/g;
  $sql=~s/\*\//------注释结束--------/g;
  $sql='CREATE PROCEDURE '.$_[0].'()'." \n".'  BEGIN '."\n".' /* '.$sql.'*/'."\n".'END; ';
  my $data_base = "DBI:mysqldb_namelocationport";
  my $dbh3=DBI -> connect($data_base,$db_user,$db_pass);
  $dbh3->do("SET character_set_client = 'utf8'");
  $dbh3->do("SET character_set_connection = 'utf8'");
  my $data_str=encode("utf-8",decode("gbk",$sql));
  my $sth=$dbh3->prepare($sql);
  open(FILE,"》myproduce.txt");
  syswrite(FILE,"$n\n");
  syswrite(FILE,"$data[0]\n");
  syswrite(FILE,"$sql\n");
  close(FILE);
  $sth->execute() or die "$data_str----ERROR:data_str:dbh3->errstr";
  $dbh3->disconnect;
  print '创建'.$_[0].'表的存储过程结束'."\n";
  }
您需要登录后才可以回帖 登录 | 用户.注册

本版积分规则

手机版|小黑屋|

Processed in 0.125961 second(s), 21 queries , Gzip On.

Powered by Comsenz Discuz! platform.

© 2012-2016 中国主机联盟 专业IDC交流平台,提供服务器、VPS云主机、虚拟主机、域名技术交流及优惠信息

快速回复 返回顶部 返回列表