sql语句大全之MSSQL->serverlink[Oracle]

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之MSSQL->serverlink[Oracle]

需求描述：

SQL Server数据库连接Oracle数据库

条件准备：

SQL Server数据库，SQL Server 2008R2

Oracle数据库，Oracle 12.2.0.1.0

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

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

连接方式

使用 linked server 或 rowset function

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

 
语法 
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

举例：

--创建链接别名
exec master.dbo.sp_addlinkedserver
@server = 'Oraclelink',--指定链接数据库的别名
@srvproduct = 'oracle',--链接对象数据库的产品
@provider = 'OraOLEDB.Oracle',--驱动器
@datasrc = 'HMDB0'--要访问的服务器，oracle需提供tnsnames.ora配置的别名
go
 
 
--登录链接数据库
exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = 'Oraclelink',--指定需登录的数据库链接别名
@locallogin = NULL,--本地服务器的登录名，直接访问Windows Server组成员
@useself = 'false',--关闭本地登录，true值使用本地账户认证，会忽略rmtuser和rmtpassword参数
@rmtuser = 'dbmonitor',--登录数据库用户名
@rmtpassword = 'xxxxxx'--登录密码
go
 
--查询oracle数据库测试
--数据库的格式必须是 链接别名..ORACLE用户名.表名
select * from Oraclelink..DBMONITOR.UNIQUE_T;
go
 
 
--查看当前所有链接列表
exec sp_linkedservers
 
 
--删除linked server
Exec sp_droplinkedsrvlogin Oraclelink,NULL --删除链接服务器的登陆帐户
Exec sp_dropserver Oraclelink --删除链接数据库别名

rowset function方式

首先图形界面创建链接数据库别名

测试成功

行集函数返回一个可用替代Transact-SQL语句中表引用的对象

OPENQUERY

语法： OPENQUERY ( linked_server ,'query' )

SELECT * FROM OPENQUERY ( ORACLELINK,'SELECT * FROM UNIQUE_T');

INSERT
   INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')  VALUES ('NewTitle');
UPDATE
   UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')   SET name = 'ADifferentName';
DELETE
   DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

栏目列表