基于ORACLE、SQLSERVER、POSTGRESQL数据库的跨库查询数据研究与应用

李磊 宋子龙 张骞

摘要：企业的应用系统逐年的建设，由于技术架构不断更新，开发平台和语言的不同，呈现出各自分散、独立运行的特点，多种数据库需要整合应用进行集成，给予大数据进行查询和分析时，带来诸多不便。本文结合实际问题，研究跨库查询数据的方法及应用。

关键词：ORACLE;SQLSERVER;POSTGRESQL

1、概述

作者所属企业，应用系统逐年建设中，采用了不同的架构及开发语言，在后台数据库上，也使用了ORACLE等多种数据库。这些应用系统在建设时由于当时的技术及条件限制，仅考虑自身的功能实现，没有统筹规划，呈现出各自分散、独立运行的现状。

近年，应用系统集成呈主流趋势，大数据技术飞速发展，如何整合应用系统及结合不同数据库之间的数据进行查询分析成为了一个新的课题。

考虑到投资成本以及充分利用原系统和数据，作者结合企业的一个具体问题-人员考勤及行为跟踪，来探讨研究跨库查询数据的方法及应用。

2、需求分析

作者所属企业，通过员工脸部识别、乘车、门禁、就餐等打卡方式采集数据，将员工的行动轨迹进行初步分析，作为员工考勤佐证。三个平台展示采集结果：员工综合信息系统、综合安防管理平台、职工证管理平台，分别使用ORACLE、SQLSERVER和POSTGRESQL数据库：

ORACLE数据库：员工基础信息存储在ORACLE数据库中，每月ERP数据导入确保信息的及时性、准确性，对人员变化的动态信息及账号数据查询进行扩建，方便各应用系统管理员变动人员权限调整。

POSTGRESQL数据库：员工人脸识别进行考勤打卡，进出入办公场所采取门禁刷卡进出。该过程采集的信息使用POSTGRESQL数据库，为管理人员提供重要的分析依据。各单位快捷准确了解员工动向，方便掌握分析决策单位员工情况，有效提升管理水平。[1]

SQLSERVER数据库：员工乘车刷卡、食堂就餐通过SQLSERVER数据库存储。通过刷卡识别身份，进行乘车、门禁等业务，满足了身份识别需求，同时跟踪掌握人员流动，为安保维稳提供了技术支撑。通过对刷卡信息的综合查询及分析，为考勤管理和餐厅管理提供参考依据，进一步做好成本控制，提高企业精细化管理水平。

3、跨库查询方法及应用

（1）基于SQL SERVER数据库链接实现跨库查询

SQL SERVER为中心，作为连接其他数据库的中心， SQL SERVER数据库中自带的数据库链接来实现。

在服务器对象中，选择链接服务器，新建链接服务器科自行命名，主要设置：访问接口，选择Microsoft OLE DB Provider for ODBC Drivers，使用ODBC数据源，可兼容其他种类数据库;数据源，这里需要先在服务器的ODBC数据源中建立好，填写同名数据源即可;安全性，设置远程登录用户名和密码。

完成后，点击链接服务器进行测试，成功后即可查询数据。查询时，使用openQuery方法，参数为链接服务器对象和SQL语句，例如：

Select * from openquery（zhaf，'selectperson_id，person_name，

org_id，org_name，attribute1，org_code，job_no from view_user'）

查询语句繁琐，可以建立相关视图，之后查询时从视图查询即可。

该方法适用于ORACLE、POSTGRESQL、Mysql等不同种类的数据库，只需要在SQL SERVER服务器上安装相应的数据库ODBC驱动，并建立数据源即可，同样也适用于SQL SERVER本地的跨库查询。[2]

（2）基于POSTGRESQL的DBLINK实现跨库查询

POSTGRESQL结构和SQL SERVER基本相同，在同一个端口5432下的一个实例中可能存在多个数据库，这些数据库之间的数据需要相互访问时，就需要进行跨库查询。

在POSTGRESQL数据库中，需要使用DBLINK来进行跨库查询。首先使用create extention dblink语句启用dblink功能扩展。

和SQL SERVER相同，每次查询时都输入这么长一串字符非常不便，可以建立相关视图，之后查询时从视图查询即可。[3]

（3）基于ORACLE数据库不同用户名之间的数据查询

ORACLE数据库在自身库内查询比较容易实现，数据表存放在不同的用户名下，本质上是同一个库，只需查询其他用户名下的数据表或视图。

为满足此种需求，只需在对相应的数据表或视图进行授权即可。使用GRANT语句将SELECT权限授予需要使用的用户即可。例如：GRANT SELECT ON Plan_Data TO Accounting。將Plan_Data表的查询权限授予Accounting用户，需要查询时，登录Accounting用户，查询时使用如下语句Select * from XXX.Plan_Data，XXX为授权原始用户。[4]

（4）基于SQL SERVER代理作业实现的跨库数据同步

跨库查询终究影响使用效率，当数据量较大时，查询速度慢非常影响用户体验。建议采用SQL SERVER代理作业，实现跨库数据同步，即设定好定时任务，定时触发，将其他数据库的的数据定期同步到SQL SERVER的本地表中，在SQL SERVER中进行查询，避免跨库时的查询等待。[5]

由于设定同步时间为15分钟一次，全表同步数据量太大，时间耗费长，因此采用增量同步方式，为避免同步时间和设备上传时间因网络延迟而造成的差异，同步当日数据时，采用了先清除SQL SERVER中本日刷卡数据，再跨库查询POSTGRESQL中当日门禁刷卡数据，插入到SQL SERVER本地表中。

此处跨库操作不同于之前的跨库查询，对远程数据库链接的数据无影响，此项操作将对远程数据库进行删除和插入操作，一定要谨慎。

此方法的缺点在于不能实时同步数据，作业间隔期间数据存在不一致的情况，只有对应用实时性要求不高时才可使用。同时代理作业影响SQL SERVER运行，需要合理的设置同步周期和同步内容，避免过度占用系统资源。频繁的操作数据表进行删除和插入操作，将导致运行效率降低，因此需要定期的刷新数据表索引，提高查询效率。

4、结语

文中探讨了一些如何利用技术手段实现跨不同数据库实现相关数据关联查询的方法，但本质上只是一种应急解决方案，依然存在很多问题和不便，建议开发者在做应用系统前，制定统一的框架和标准，使用统一的数据库，避免发生此种情况。

参考文献

[1] 张皓.基于主动数据库技术的人力资源管理系统设计与实现.电子技术与软件工程2021年7月刊：164-165.

[2] 陈剑.基于区块链的企业管理系统框架设计探索.中国商论，2021年（14）：140-142.

[3]黄义妨.面向不同传感器与复杂场景的人脸识别系统防伪方法综述.计算机工程.DOI：10.19678/j.issn.1000-3428.0061168.