欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-01 12:05:10
...

把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";

  }

上一篇:

下一篇: