MSSQL->serverlink[Oracle]

图片 6

需求描述:

  1. 连接方法

    通过SQL server 的
    linkedserver的功能连接

  2. 实际操作

SQL
Server提供了链接服务器用于分布式查询异构数据库。通过链接服务器可以链接到Oracle、Sybase、DB2、SQL
Server等大型关系数据库,也可以连接到Access、Excel等文件数据库,甚至可以连接到目录服务(AD)、索引服务等。要链接到一种数据库需要使用相应的接口。微软为很多数据库提供了驱动接口,所以可以直接使用,但是对于没有提供驱动的数据库比如Sybase,则需要在服务器上安装对应数据库厂商提供的驱动。

    SQL Server数据库连接Oracle数据库

准备工作

使用SSMS或者使用T-SQL语句配置成功链接服务器后便可通过:

 

事先在需要访问Oracle
数据库的主机上完成以下工作:

[服务器名].[数据库名].[架构名].[对象名]

条件准备:

  1. 安装SQL Server 数据库:SQL Server 2008
    R2 Express

  2. 安装Oracle 客户端访问程序:Oracle 11g
    Client

  3. 配置Oracle 客户端tnsnames.ora
    文件,其中需要注意文件中的数据库别名”ORADB”,该名称在下文创建Linked
    Server 时将会使用到。

的形式来访问数据库。例如要访问Oracle数据库中的一个表,则对应的查询语句是:

    SQL Server数据库,SQL Server 2008R2

ORADB =  

SELECT *
FROM ORA..MARY.ORDERS
WHERE ORDERCODE=’20080808008′

    Oracle数据库,Oracle 12.2.0.1.0

 (DESCRIPTION =  

其中ORA是链接服务器名,MARY是用户架构名,ORDERS是表或视图名。运行查询SQL
Server将返回查询的结果。

    Oracle客户端,Oracle 11g Client或Oracle 12c Client

 (ADDRESS = (PROTOCOL = TCP)(HOST = HOSTIP)(PORT = 1521))
  (CONNECT_DATA = (SID = DBNAME)) ) 

但是当Oracle中的这个表数据量较大,比如有几十万行或者几百万行时,这个查询将会耗费很长时间。在SQL
Server中运行该脚本可能要等上10秒、20秒或者1分钟、5分钟才可能查询出结果。但是如果将脚本在Oracle服务器上直接运行,则1秒钟不到就查询出结果了。造成这种情况的是SQL
Server查询链接服务器的机制。

 

  1. 确保主机可以通过SQLPlus 访问远程Oracle
    数据库。

不同的数据库对应的SQL语言是有所不同的。而对于Oracle数据库,通过链接服务器查询数据时,SQL
Server为了保证T-SQL语句能够正常使用,但是Oracle数据库可能不认识这些T-SQL语句,所以SQL
Server将会把查询中所用到的Oracle表数据从Oracle数据库读出来,一直到满足查询条件为止。对于代码16.18中的查询,SQL
Server会将Oracle数据库中的ORDERS表全部读取到SQL
Server数据库中,一边读取一边查找ORDERCODE =
‘20080808008’的数据,直到全部数据读取完为止。对于上十万百万级的数据表来说,全部读取数据当然会造成系统缓慢。如果将上面的查询修改为如下的方式,则可能速度会快上很多。

    必须在SQL
Server数据库主机安装Oracle客户端(ODBC驱动),并配置tnsnames.ora

图片 1

图片 2SELECT TOP 1 *
图片 3FROM ORA..MARY.ORDERS
图片 4WHERE ORDERCODE=’20080808008′

 

创建Linked Server

这是因为SQL Server从Oracle中顺序的读取ORDERS
表,一边读取一边比较ORDERCODE=’20080808008’一旦查找到了结果,由于我们写了TOP
1,所以系统判断满足条件了,就将结果返回,而不需要再继续向Oracle读取剩下的数据。但是这样做按照概率来说也要查一半的数据才能找到结果,仍然很慢。

连接方式

 

SQL
Server为了解决这个问题,提供了OPENQUERY函数用于将查询语句直接送到链接服务器中,由链接服务器的数据库引擎负责查询,而不是由SQL
Server将全部数据读取到本地来查询。OPENQUERY函数的语法格式为:

    使用  linked
server 或
rowset
function

配置OraOLEDB.Oracle 勾选箭头方向

OPENQUERY ( linked_server ,’query’ )

 

图片 5

其中linked_server表示链接服务器名称的标识符。’query’在链接服务器中执行的查询字符串。该字符串的最大长度为8KB。于是上面的查询我们可以改写为如下的形式:

    linked
server方式,主要是采用sp函数包进行配置和连接Oracle数据库(其他数据库也同样适用)

打开SQL Server Management Studio
进入服务器对象 列表,右键链接服务器,点击”新建链接服务器…”

SELECT *
FROM OPENQUERY(ORA,
‘SELECT * FROM MARY.ORDERS WHERE ORDERCODE=”20080808008”’)

语法 
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] [ , [ @provider= ] 'provider_name' ] [ , [ @datasrc= ] 'data_source' ] [ , [ @location= ] 'location' ] [ , [ @provstr= ] 'provider_string' ] [ , [ @catalog= ] 'catalog' ]

 provider可选值【SQLNCLI | SQLOLEDB】->SQL Server、【OraOLEDB.Oracle】->Oracle、【MSDASQL】->POSTGRESQL、【 Microsoft.Jet.OLEDB.4.0】->EXCEL

图片 6

这样条件查询将会在Oracle数据库中运行,Oracle将查询的结果返回给SQL
Server,然后SQL Server再将结果返回给用户。

 

在General 界面中填写下面几项内容:

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图