基于ORACLE、SQLSERVER、POSTGRESQL數(shù)據(jù)庫的跨庫查詢數(shù)據(jù)研究與應用

李磊 宋子龍 張騫

摘要：企業(yè)的應用系統(tǒng)逐年的建設，由于技術架構不斷更新，開發(fā)平臺和語言的不同，呈現(xiàn)出各自分散、獨立運行的特點，多種數(shù)據(jù)庫需要整合應用進行集成，給予大數(shù)據(jù)進行查詢和分析時，帶來諸多不便。本文結合實際問題，研究跨庫查詢數(shù)據(jù)的方法及應用。

關鍵詞：ORACLE;SQLSERVER;POSTGRESQL

1、概述

作者所屬企業(yè)，應用系統(tǒng)逐年建設中，采用了不同的架構及開發(fā)語言，在后臺數(shù)據(jù)庫上，也使用了ORACLE等多種數(shù)據(jù)庫。這些應用系統(tǒng)在建設時由于當時的技術及條件限制，僅考慮自身的功能實現(xiàn)，沒有統(tǒng)籌規(guī)劃，呈現(xiàn)出各自分散、獨立運行的現(xiàn)狀。

近年，應用系統(tǒng)集成呈主流趨勢，大數(shù)據(jù)技術飛速發(fā)展，如何整合應用系統(tǒng)及結合不同數(shù)據(jù)庫之間的數(shù)據(jù)進行查詢分析成為了一個新的課題。

考慮到投資成本以及充分利用原系統(tǒng)和數(shù)據(jù)，作者結合企業(yè)的一個具體問題-人員考勤及行為跟蹤，來探討研究跨庫查詢數(shù)據(jù)的方法及應用。

2、需求分析

作者所屬企業(yè)，通過員工臉部識別、乘車、門禁、就餐等打卡方式采集數(shù)據(jù)，將員工的行動軌跡進行初步分析，作為員工考勤佐證。三個平臺展示采集結果：員工綜合信息系統(tǒng)、綜合安防管理平臺、職工證管理平臺，分別使用ORACLE、SQLSERVER和POSTGRESQL數(shù)據(jù)庫：

ORACLE數(shù)據(jù)庫：員工基礎信息存儲在ORACLE數(shù)據(jù)庫中，每月ERP數(shù)據(jù)導入確保信息的及時性、準確性，對人員變化的動態(tài)信息及賬號數(shù)據(jù)查詢進行擴建，方便各應用系統(tǒng)管理員變動人員權限調整。

POSTGRESQL數(shù)據(jù)庫：員工人臉識別進行考勤打卡，進出入辦公場所采取門禁刷卡進出。該過程采集的信息使用POSTGRESQL數(shù)據(jù)庫，為管理人員提供重要的分析依據(jù)。各單位快捷準確了解員工動向，方便掌握分析決策單位員工情況，有效提升管理水平。[1]

SQLSERVER數(shù)據(jù)庫：員工乘車刷卡、食堂就餐通過SQLSERVER數(shù)據(jù)庫存儲。通過刷卡識別身份，進行乘車、門禁等業(yè)務，滿足了身份識別需求，同時跟蹤掌握人員流動，為安保維穩(wěn)提供了技術支撐。通過對刷卡信息的綜合查詢及分析，為考勤管理和餐廳管理提供參考依據(jù)，進一步做好成本控制，提高企業(yè)精細化管理水平。

3、跨庫查詢方法及應用

（1）基于SQL SERVER數(shù)據(jù)庫鏈接實現(xiàn)跨庫查詢

SQL SERVER為中心，作為連接其他數(shù)據(jù)庫的中心， SQL SERVER數(shù)據(jù)庫中自帶的數(shù)據(jù)庫鏈接來實現(xiàn)。

在服務器對象中，選擇鏈接服務器，新建鏈接服務器科自行命名，主要設置：訪問接口，選擇Microsoft OLE DB Provider for ODBC Drivers，使用ODBC數(shù)據(jù)源，可兼容其他種類數(shù)據(jù)庫;數(shù)據(jù)源，這里需要先在服務器的ODBC數(shù)據(jù)源中建立好，填寫同名數(shù)據(jù)源即可;安全性，設置遠程登錄用戶名和密碼。

完成后，點擊鏈接服務器進行測試，成功后即可查詢數(shù)據(jù)。查詢時，使用openQuery方法，參數(shù)為鏈接服務器對象和SQL語句，例如：

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

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

查詢語句繁瑣，可以建立相關視圖，之后查詢時從視圖查詢即可。

該方法適用于ORACLE、POSTGRESQL、Mysql等不同種類的數(shù)據(jù)庫，只需要在SQL SERVER服務器上安裝相應的數(shù)據(jù)庫ODBC驅動，并建立數(shù)據(jù)源即可，同樣也適用于SQL SERVER本地的跨庫查詢。[2]

（2）基于POSTGRESQL的DBLINK實現(xiàn)跨庫查詢

POSTGRESQL結構和SQL SERVER基本相同，在同一個端口5432下的一個實例中可能存在多個數(shù)據(jù)庫，這些數(shù)據(jù)庫之間的數(shù)據(jù)需要相互訪問時，就需要進行跨庫查詢。

在POSTGRESQL數(shù)據(jù)庫中，需要使用DBLINK來進行跨庫查詢。首先使用create extention dblink語句啟用dblink功能擴展。

和SQL SERVER相同，每次查詢時都輸入這么長一串字符非常不便，可以建立相關視圖，之后查詢時從視圖查詢即可。[3]

（3）基于ORACLE數(shù)據(jù)庫不同用戶名之間的數(shù)據(jù)查詢

ORACLE數(shù)據(jù)庫在自身庫內查詢比較容易實現(xiàn)，數(shù)據(jù)表存放在不同的用戶名下，本質上是同一個庫，只需查詢其他用戶名下的數(shù)據(jù)表或視圖。

為滿足此種需求，只需在對相應的數(shù)據(jù)表或視圖進行授權即可。使用GRANT語句將SELECT權限授予需要使用的用戶即可。例如：GRANT SELECT ON Plan_Data TO Accounting。將Plan_Data表的查詢權限授予Accounting用戶，需要查詢時，登錄Accounting用戶，查詢時使用如下語句Select * from XXX.Plan_Data，XXX為授權原始用戶。[4]

（4）基于SQL SERVER代理作業(yè)實現(xiàn)的跨庫數(shù)據(jù)同步

跨庫查詢終究影響使用效率，當數(shù)據(jù)量較大時，查詢速度慢非常影響用戶體驗。建議采用SQL SERVER代理作業(yè)，實現(xiàn)跨庫數(shù)據(jù)同步，即設定好定時任務，定時觸發(fā)，將其他數(shù)據(jù)庫的的數(shù)據(jù)定期同步到SQL SERVER的本地表中，在SQL SERVER中進行查詢，避免跨庫時的查詢等待。[5]

由于設定同步時間為15分鐘一次，全表同步數(shù)據(jù)量太大，時間耗費長，因此采用增量同步方式，為避免同步時間和設備上傳時間因網(wǎng)絡延遲而造成的差異，同步當日數(shù)據(jù)時，采用了先清除SQL SERVER中本日刷卡數(shù)據(jù)，再跨庫查詢POSTGRESQL中當日門禁刷卡數(shù)據(jù)，插入到SQL SERVER本地表中。

此處跨庫操作不同于之前的跨庫查詢，對遠程數(shù)據(jù)庫鏈接的數(shù)據(jù)無影響，此項操作將對遠程數(shù)據(jù)庫進行刪除和插入操作，一定要謹慎。

此方法的缺點在于不能實時同步數(shù)據(jù)，作業(yè)間隔期間數(shù)據(jù)存在不一致的情況，只有對應用實時性要求不高時才可使用。同時代理作業(yè)影響SQL SERVER運行，需要合理的設置同步周期和同步內容，避免過度占用系統(tǒng)資源。頻繁的操作數(shù)據(jù)表進行刪除和插入操作，將導致運行效率降低，因此需要定期的刷新數(shù)據(jù)表索引，提高查詢效率。

4、結語

文中探討了一些如何利用技術手段實現(xiàn)跨不同數(shù)據(jù)庫實現(xiàn)相關數(shù)據(jù)關聯(lián)查詢的方法，但本質上只是一種應急解決方案，依然存在很多問題和不便，建議開發(fā)者在做應用系統(tǒng)前，制定統(tǒng)一的框架和標準，使用統(tǒng)一的數(shù)據(jù)庫，避免發(fā)生此種情況。

參考文獻

[1] 張皓.基于主動數(shù)據(jù)庫技術的人力資源管理系統(tǒng)設計與實現(xiàn).電子技術與軟件工程2021年7月刊：164-165.

[2] 陳劍.基于區(qū)塊鏈的企業(yè)管理系統(tǒng)框架設計探索.中國商論，2021年（14）：140-142.

[3]黃義妨.面向不同傳感器與復雜場景的人臉識別系統(tǒng)防偽方法綜述.計算機工程.DOI：10.19678/j.issn.1000-3428.0061168.