只要网通就可以实现异构数据库交互（oracle,mysql 等数据交互）

潇湘秦 11-27 32

前言：

当前你们对“oracle透明网关访问sqlserver”大概比较注意，咱们都想要知道一些“oracle透明网关访问sqlserver”的相关文章。那么小编同时在网上搜集了一些对于“oracle透明网关访问sqlserver””的相关文章，希望兄弟们能喜欢，朋友们快快来了解一下吧！

前两天在DBA群里有大佬分享了利用Oracle Database Gateway（透明网关）实现sqlserver和oracle 的数据交互，这里让我想到前些年写的一些powershell脚本用来做sqlserver和oracle的数据交互，powershell是windows自带的一个脚本工具，常见的是处理一些系统管理等工作，其实powershell也是可以作为DBA的好帮手，实现异构数据库的交互，数据库监控，发送邮件等等工作，本文将引用几个范例来介绍powershell在数据库上的用法。

以下是 PowerShell 的一些主要特点和功能（GPT）：

命令行 Shell：PowerShell 提供了一个交互式的命令行 Shell，用户可以在其中执行各种命令和操作系统任务。它包含了丰富的命令集，可以进行文件操作、进程管理、网络配置等各种操作。脚本语言：PowerShell 是一种完整的脚本语言，它具有变量、循环、条件语句等常见编程语言的特性。用户可以编写 PowerShell 脚本来自动化重复性的任务，简化管理工作。对象导向：PowerShell 是基于对象的，它处理的不是简单的文本流，而是.NET 对象。这使得在 PowerShell 中进行数据处理更为直观和灵活。模块化：PowerShell 支持模块化，可以通过安装和导入模块来扩展功能。Microsoft 和第三方开发者提供了大量的模块，用于管理 Azure、Active Directory、Exchange 等 Microsoft 产品，以及其他常用任务的自动化。管道（Pipeline）：PowerShell 的管道功能允许将多个命令连接起来，其中一个命令的输出可以作为另一个命令的输入。这种管道功能使得命令的组合和操作变得更加灵活。远程管理：PowerShell 支持远程管理，可以通过 PowerShell Remoting 在远程计算机上执行命令和脚本，管理远程计算机。自动完成：PowerShell 提供了自动完成功能，可以通过按下 Tab 键来补全命令、参数、变量等，提高了命令行的使用效率。跨平台：除了 Windows，PowerShell 也可以在 Linux 和 macOS 上运行。Microsoft 提供了 PowerShell Core 版本，可以在非 Windows 平台上使用。

PowerShell 是一种功能强大且灵活的工具，适用于系统管理员、开发人员和 DevOps 工程师等各种角色，用于管理、自动化和配置 Windows 和其他操作系统。

如何使用powershell来实现异构数据库的交互容我喝杯茶慢慢道来

1.Powershell操作sqlserver数据库

$SQLServer			    = '10.x.x.xx'  ##sqlsever ip$SQLDatabase			    = 'master'      ##database name$SQLUserID			    = 'sa'          ##user$SQLPassword			    = 'xxxxxx'      ##password$SQLPort                            = '1433'        ##port number  if default port can ignorefunction sqlConn() ##connect to db{	[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")	$ConnectionString				= "Server=$SQLServer;Database=$SQLDatabase;User Id=$SQLUserID;Password=$SQLPassword;Integrated Security=no"	$SqlConnection					= New-Object System.Data.SqlClient.SqlConnection	$SqlConnection.ConnectionString	= $ConnectionString	return $SqlConnection}function SqlQuery ($SqlLine) ##select {	$SqlConnection					= sqlConn	$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlDataAdapter					= New-Object system.Data.SqlClient.SqlDataAdapter	$SqlDataAdapter.SelectCommand	= $SqlCommand	$DataSet						= New-Object system.Data.DataSet	$SqlConnection.Open()	$SqlDataAdapter.Fill($DataSet) | Out-Null	$SqlDataAdapter.Dispose()	$SqlConnection.Close()	return $DataSet.Tables[0]}function SqlCommand ($SqlLine)  ##dml sql{	$SqlConnection					= sqlConn	$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlConnection.Open()	return $SqlCommand.ExecuteNonQuery()	$SqlConnection.Close()}################上面部分为sqlserver连接通用模板###############################  SqlQuery('SELECT * FROM [master].[INFORMATION_SCHEMA].[TABLES]')   ##查询系统表 #######查询[INFORMATION_SCHEMA].[TABLES]记录循环插入到tempdb.dbo.test表中foreach ($invalue in SqlQuery('SELECT table_name,table_type FROM [master].[INFORMATION_SCHEMA].[TABLES]'))  ###将查询的结果暂存为$invalue{    $tablename = $invalue.table_name   ##分别定义需要插入的值    $tabletype = $invalue.table_type      $SqlLine = "INSERT INTO tempdb.dbo.test (table_name, table_type) VALUES ('$tablename', '$tabletype')"   ###insert 语句    SqlCommand $SqlLine    ###执行  }

执行结果如下

2.Powershell操作oracle数据库

场景利用powershell 查询出远端oracle数据库并将查询的结果写入到本地文件，如果有数据写入则发用邮件并将文件作为附件

$OracleDatabase                        = 'mtrpdb1'   ##tns name  当前环境安装了oracle client$OracleUserID			       = 'mtr'       ##username$OraclePassword			       = 'xxxxx'     ##password$OraclePort1                           = '1521'      ##port number  if use default port can ignorefunction OracleConn(){	[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")	$ConnectionString				= "Data Source=$OracleDatabase1;User Id=$OracleUserID1;Password=$OraclePassword1;Integrated Security=no"	$SqlConnection					= New-Object System.Data.OracleClient.OracleConnection	$SqlConnection.ConnectionString	= $ConnectionString	return $SqlConnection}function OracleQuery ($SqlLine){	$SqlConnection					= OracleConn	$SqlDataAdapter					= New-Object System.Data.OracleClient.OracleDataAdapter	$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand	$DataSet						= New-Object system.Data.DataSet	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlDataAdapter.SelectCommand	= $SqlCommand	$SqlConnection.Open()	$SqlDataAdapter.Fill($DataSet) | Out-Null	$SqlDataAdapter.Dispose()	$SqlConnection.Close()	return $DataSet.Tables[0]}function OracleCommand ($SqlLine){	$SqlConnection					= OracleConn	$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlConnection.Open()	return $SqlCommand.ExecuteNonQuery()	$SqlConnection.Close()}################上面部分为oracle连接通用模板############################### function sendmail ($body,$toname,$file)  #######带附件邮件函数模板{	$today = Get-Date	$SmtpClient = new-object system.net.mail.smtpClient 	$mailmessage = New-Object system.net.mail.mailmessage 	$attachment = new-Object System.Net.Mail.Attachment("$file")	$SmtpClient.Host = "10.0.x.xx"   ##smtp server ip	$mailmessage.from = ("norton.fan@mail.com") ##发件人地址	$mailmessage.To.add($toname)	#$mailmessage.cc.add(" ") 	$mailmessage.Subject = "DB Alert Test!" 	$mailmessage.IsBodyHtml = $true	$mailmessage.Attachments.add($attachment)	$mailmessage.Body = "<h4>$today `n</h4>" + $body	$smtpclient.Send($mailmessage) }##############################上面为邮件通用模板#########################################	$mailname = "xiaofan23z@163.com"   ##收件人mail	#$mailname1 = "xiaofan23z@163.com"	$LOGFILE = "C:\dbeaver\test\logfile.txt"  ##create a file store information OracleQuery "SELECT * FROM DB_TOTAL dt WHERE CREATION_DATE >sysdate -10"  ##查询数据量统计信息#############将查询结果写入到logfile 变发送邮件###############################	  foreach ($Info in (OracleQuery "SELECT total_gb,creation_date,site FROM DB_TOTAL dt WHERE CREATION_DATE >sysdate -10"))  {    $total_gb = $Info.total_gb.ToString()    $creation_date = $Info.creation_date.ToString()    $site = $Info.site.ToString()    $content = "$total_gb $creation_date $site"   ##if have new information insert into this logfile   out-file -FilePath $LOGFILE -InputObject $content -Append  ##写入到logfile  }   $mass = type $LOGFILE ##$l = $mass.Lengthif($mass.Length -ge 0 ) {	sendmail "$mass" "$mailname" "$LOGFILE" ##发送邮件 }

邮件结果

附件如图

3.Powershell操作mysql数据库

需要下载MySQL Connector/NET，下载地址如下

$MySQLServer            = '10.0.x.x'$MySQLDatabase		= 'dbname'$MySQLUserID		= 'user'$MySQLPassword		= 'password'$MySQLPort          = '3306'  ##port number if use default port can ignorefunction mysqlConn(){    Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\v4.5.2\MySql.Data.dll"  ###这个默认的安装目录，根据个人环境变更    $ConnectionString = "server=$MySQLServer;port=$MySQLPort;database=$MySQLDatabase;uid=$MySQLUserID;password=$MySQLPassword"    $SqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection    $SqlConnection.ConnectionString = $ConnectionString    return $SqlConnection}function mysqlQuery($SqlLine){    $SqlConnection = mysqlConn    $SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter    $SqlDataAdapter.SelectCommand = $SqlCommand    $DataSet = New-Object system.Data.DataSet    $SqlConnection.Open()    $SqlDataAdapter.Fill($DataSet) | Out-Null    $SqlDataAdapter.Dispose()    $SqlConnection.Close()    return $DataSet.Tables[0]}function mysqlCommand($SqlLine){    $SqlConnection = mysqlConn    $SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlConnection.Open()    $result = $SqlCommand.ExecuteNonQuery()    $SqlConnection.Close()    return $result}##################上面为mysql通用连接模板####################################### mysqlquery('select * from `table1`  where `id` =14')

上面为最简单的范例查询一个表的信息并返回

4.Powershell操作postgresql数据库

pg也是类似，因为手头没有可用的pg测试库就没有加范例，但是这个标准模板是可以用的

$PostgreSQLServer    = 'hostip'$PostgreSQLDatabase  = 'mydatabase'$PostgreSQLUserID    = 'username'$PostgreSQLPassword  = 'password'$PostgreSQLPort      = '5432'  # 默认 PostgreSQL 端口为 5432function pgConn(){    Add-Type -Path "C:\Program Files\PostgreSQL\13\bin\Npgsql.dll"  # 这里的路径需要根据你的实际安装路径进行修改    $ConnectionString = "Host=$PostgreSQLServer;Port=$PostgreSQLPort;Database=$PostgreSQLDatabase;Username=$PostgreSQLUserID;Password=$PostgreSQLPassword"    $SqlConnection = New-Object Npgsql.NpgsqlConnection    $SqlConnection.ConnectionString = $ConnectionString    return $SqlConnection}function pgsqlQuery($SqlLine){    $SqlConnection = pgConn    $SqlCommand = New-Object Npgsql.NpgsqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlDataAdapter = New-Object Npgsql.NpgsqlDataAdapter    $SqlDataAdapter.SelectCommand = $SqlCommand    $DataSet = New-Object system.Data.DataSet    $SqlConnection.Open()    $SqlDataAdapter.Fill($DataSet) | Out-Null    $SqlDataAdapter.Dispose()    $SqlConnection.Close()    return $DataSet.Tables[0]}function pgsqlCommand($SqlLine){    $SqlConnection = pgConn    $SqlCommand = New-Object Npgsql.NpgsqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlConnection.Open()    $result = $SqlCommand.ExecuteNonQuery()    $SqlConnection.Close()    return $result}

5.Powershell 实现异构数据交互

这里示范一个例子，查询oracle数据库中一个表的数据，并将查询结果插入到mysql和sqlserver数据库中

####################mysql###########################################$MySQLServer		= '10.x.x.x'$MySQLDatabase		= 'dbname'$MySQLUserID		= 'user'$MySQLPassword		= 'password'$MySQLPort          = '3306'function mysqlConn(){    Add-Type -Path "C:\Program Files (x86)\MySQL\MySQL Connector Net 8.0.28\Assemblies\v4.5.2\MySql.Data.dll"    $ConnectionString = "server=$MySQLServer;port=$MySQLPort;database=$MySQLDatabase;uid=$MySQLUserID;password=$MySQLPassword"    $SqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection    $SqlConnection.ConnectionString = $ConnectionString    return $SqlConnection}function mysqlQuery($SqlLine){    $SqlConnection = mysqlConn    $SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter    $SqlDataAdapter.SelectCommand = $SqlCommand    $DataSet = New-Object system.Data.DataSet    $SqlConnection.Open()    $SqlDataAdapter.Fill($DataSet) | Out-Null    $SqlDataAdapter.Dispose()    $SqlConnection.Close()    return $DataSet.Tables[0]}function mysqlCommand($SqlLine){    $SqlConnection = mysqlConn    $SqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand    $SqlCommand.CommandText = $SqlLine    $SqlCommand.Connection = $SqlConnection    $SqlConnection.Open()    $result = $SqlCommand.ExecuteNonQuery()    $SqlConnection.Close()    return $result}##########################sqlserver############################################$SQLServer						= '10.x.x.x'$SQLDatabase						= 'dbname'$SQLUserID						= 'user'$SQLPassword						= 'password'$SQLPort                                                = '1433'function sqlConn(){	[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")	$ConnectionString				= "Server=$SQLServer;Database=$SQLDatabase;User Id=$SQLUserID;Password=$SQLPassword;Integrated Security=no"	$SqlConnection					= New-Object System.Data.SqlClient.SqlConnection	$SqlConnection.ConnectionString	= $ConnectionString	return $SqlConnection}function SqlQuery ($SqlLine){	$SqlConnection					= sqlConn	$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlDataAdapter					= New-Object system.Data.SqlClient.SqlDataAdapter	$SqlDataAdapter.SelectCommand	= $SqlCommand	$DataSet						= New-Object system.Data.DataSet	$SqlConnection.Open()	$SqlDataAdapter.Fill($DataSet) | Out-Null	$SqlDataAdapter.Dispose()	$SqlConnection.Close()	return $DataSet.Tables[0]}function SqlCommand ($SqlLine){	$SqlConnection					= sqlConn	$SqlCommand						= New-Object System.Data.SqlClient.SqlCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlConnection.Open()	return $SqlCommand.ExecuteNonQuery()	$SqlConnection.Close()}#############################oracle#############################################$OracleDatabase						= 'tnsname'$OracleUserID						= 'user'$OraclePassword						= 'password'$OraclePort                                             = '1521'function OracleConn(){	[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")	$ConnectionString				= "Data Source=$OracleDatabase;User Id=$OracleUserID;Password=$OraclePassword;Integrated Security=no"	$SqlConnection					= New-Object System.Data.OracleClient.OracleConnection	$SqlConnection.ConnectionString	= $ConnectionString	return $SqlConnection}function OracleQuery ($SqlLine){	$SqlConnection					= OracleConn	$SqlDataAdapter					= New-Object System.Data.OracleClient.OracleDataAdapter	$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand	$DataSet						= New-Object system.Data.DataSet	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlDataAdapter.SelectCommand	= $SqlCommand	$SqlConnection.Open()	$SqlDataAdapter.Fill($DataSet) | Out-Null	$SqlDataAdapter.Dispose()	$SqlConnection.Close()	return $DataSet.Tables[0]}function OracleCommand ($SqlLine){	$SqlConnection					= OracleConn	$SqlCommand						= New-Object System.Data.OracleClient.OracleCommand	$SqlCommand.CommandText			= $SqlLine	$SqlCommand.Connection			= $SqlConnection	$SqlConnection.Open()	return $SqlCommand.ExecuteNonQuery()	$SqlConnection.Close()}######################################################################foreach ($Info in (OracleQuery "SELECT total_gb,site FROM DB_TOTAL WHERE CREATION_DATE >sysdate -10"))  {    $total_gb = $Info.total_gb    $site = $Info.site    # 在 SQL Server 中插入数据    $SqlLine = "INSERT INTO tempdb.dbo.db_total (total_gb, site) VALUES ('$total_gb', '$site')"    SqlCommand $SqlLine    # 在 MySQL 中插入数据    $MysqlLine = "INSERT INTO db_total (total_gb, site) VALUES ('$total_gb', '$site')"    mysqlCommand $MysqlLine  }

返回结果1 即表示插入成功

oracle 原始数据

插入到mysql 数据

插入到sqlserver 的数据

6.后记

看了一下自己的powershell脚本记录，十几年前就曾经使用powershell来做oracle的一些例如tablespace的监控，partition table的监控，做的更多的是一些本地文件和数据库直接的交互，在这个方面powershell拥有得天独厚的优势，比如抓取某个目录下的所有文件名，插入到数据库中等等；虽然这种方式并不太适合大数据量的交互和迁移，但是在一些无需大数据量的应用场景下，还是很有用的，比如设置个定时任务就可以将oracle数据库中的数据，定时抛送到其他异构数据库中（mysql，sqlserver，postgresql等），而这一切都只需要window自带的powershell工具既可实现！希望给大家在日后的数据交互和迁移上提供一点点思路！

本文地址：http://www.longkongtuishu.com/cadccBAZsB1IGCVFd.html

标签： #oracle透明网关访问sqlserver