当前位置:Gxlcms > mysql > 把sqlserver中的存储过程注释后创建到mysql中

把sqlserver中的存储过程注释后创建到mysql中

时间:2021-07-01 10:21:17 帮助过:2人阅读

把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

  把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:ODBC:$source_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:ODBC:$source_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,"$nn");

  syswrite(FILE,"$data[0]n");

  syswrite(FILE,"$sqln");

  close(FILE);

  $sql=~s//*/------注释开始--------/g;

  $sql=~s/*//------注释结束--------/g;

  $sql='CREATE PROCEDURE '.$_[0].'()'." n".' BEGIN '."n".' /* '.$sql.'*/'."n".'END; ';

  my $data_base = "DBI:mysql:$db_name:$location:$port";

  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,"$nn");

  syswrite(FILE,"$data[0]n");

  syswrite(FILE,"$sqln");

  close(FILE);

  $sth->execute() or die "$data_str----ERROR::$data_str::$dbh3->errstr";

  $dbh3->disconnect;

  print '创建'.$_[0].'表的存储过程结束'."n";

  }

人气教程排行