| 标题 | 把sqlserver中的存储过程注释后创建到mysql中 |
| 范文 | 把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"; } |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。