两个不同数据库中的表之间的操作(包含sql server /oracle)

### 不在同一个数据库中的两张表之间的操作（含SQL Server与Oracle） #### 概述 本文主要探讨了在不同数据库环境中如何实现数据交互的问题，重点对比分析了SQL Server和Oracle这两种主流数据库系统之间的表操作方法。当面对不同数据库环境时，进行表间的数据交换或联合查询等操作通常会变得更加复杂。本文将详细介绍两种主要的技术方案：链接服务器（Linked Servers）以及开放式数据源（Open Data Source, ODS）。 #### 链接服务器（Linked Servers） 链接服务器是一种在SQL Server中定义远程数据源的方式，通过这种方式可以在一个数据库中访问另一个数据库的数据，无需显式地编写分布式查询或使用导入导出工具。 **创建链接服务器** 1. **注册Oracle数据库作为链接服务器** - 使用`sp_addlinkedserver`存储过程来注册Oracle数据库为链接服务器。 ```sql EXEC sp_addlinkedserver @server = '所需链接的名称', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = 'Oracle服务器的IP地址'; ``` - 使用`sp_addlinkedsrvlogin`存储过程来添加登录信息。 ```sql EXEC sp_addlinkedsrvlogin @rmtsrvname = '所需链接的名称', @useself = 'false', @locallogin = NULL, @rmtuser = 'Oracle用户名', @rmtpassword = ''; ``` 2. **查询示例** - 查询链接服务器上的表数据。 ```sql SELECT * FROM [链接服务器名].[目标数据库名].dbo.[表名]; ``` - 使用`OPENQUERY`函数查询链接服务器上的数据。 ```sql SELECT * FROM OPENQUERY([链接服务器名], 'SELECT * FROM [表名]'); ``` 3. **删除链接服务器** - 当不再需要链接服务器时，可以使用`sp_dropserver`存储过程删除它。 ```sql EXEC sp_dropserver @server = '所需链接的名称', @droplogins = 'droplogins'; ``` #### 开放式数据源（Open Data Source, ODS） ODS是另一种允许SQL Server从其他数据源获取数据的方法，特别适用于跨数据库查询。其中，`OPENROWSET`和`OPENDATASOURCE`是最常用的功能。 **使用OPENROWSET** 1. **基本语法** - `OPENROWSET`可以通过指定的提供程序连接到远程数据库并执行查询。 ```sql SELECT * FROM OPENROWSET('提供程序名称', '连接字符串', '查询语句'); ``` 2. **示例** - 假设需要从SQL Server中的Northwind数据库查询Customers表，并将其与Access数据库中的Orders表进行联接。 ```sql USE pubs; GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o ON c.CustomerID = o.CustomerID; GO ``` **使用OPENDATASOURCE** 1. **基本语法** - `OPENDATASOURCE`用于指定数据源并执行查询。 ```sql SELECT * FROM OPENDATASOURCE('提供程序名称', '连接字符串').database_name.schema_name.object_name; ``` 2. **示例** - 连接到Oracle数据库并查询表。 ```sql SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'DataSource=Oracle服务器IP地址;UserID=用户名;Password=密码').database_name.schema_name.object_name; ``` #### Oracle数据库中的DBLINK技术 Oracle数据库中也有类似的技术——DBLINK，用于在两个Oracle数据库之间建立连接。 **创建DBLINK** 1. **已授权方式** - 如果用户已被授权，则可以使用以下命令： ```sql CREATE DATABASE LINK database_link_name CONNECT TO username IDENTIFIED BY password USING '数据库连接字符串'; ``` - 示例： ```sql CREATE PUBLIC DATABASE LINK test CONNECT TO userInformation IDENTIFIED BY ***** USING 'orcl'; ``` 2. **未授权方式** - 如果用户未被授权，则需要指定完整的连接字符串： ```sql CREATE DATABASE LINK database_link_name CONNECT TO username IDENTIFIED BY password USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=服务器IP地址)(PORT=端口号))) (CONNECT_DATA=(SERVICE_NAME=服务名称)))'; ``` - 示例： ```sql CREATE DATABASE LINK test CONNECT TO userInformation IDENTIFIED BY ***** USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.142.202.12)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=fjept)))'; ``` 3. **查询示例** - 查询另一个Oracle数据库中的表。 ```sql SELECT * FROM @database_link_name.表名; ``` 在不同的数据库环境之间进行表操作时，SQL Server提供了链接服务器和开放式数据源两种方法，而Oracle则提供了DBLINK机制。这些技术都可以有效地帮助我们在不同数据库之间共享数据，提高数据处理的灵活性和效率。