基于ORACLE、SQLSERVER、POSTGRESQL數(shù)據(jù)庫(kù)的跨庫(kù)查詢(xún)數(shù)據(jù)研究與應(yīng)用

李磊 宋子龍 張騫

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

關(guān)鍵詞：ORACLE;SQLSERVER;POSTGRESQL

1、概述

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

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

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

2、需求分析

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

ORACLE數(shù)據(jù)庫(kù)：?jiǎn)T工基礎(chǔ)信息存儲(chǔ)在ORACLE數(shù)據(jù)庫(kù)中，每月ERP數(shù)據(jù)導(dǎo)入確保信息的及時(shí)性、準(zhǔn)確性，對(duì)人員變化的動(dòng)態(tài)信息及賬號(hào)數(shù)據(jù)查詢(xún)進(jìn)行擴(kuò)建，方便各應(yīng)用系統(tǒng)管理員變動(dòng)人員權(quán)限調(diào)整。

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

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

3、跨庫(kù)查詢(xún)方法及應(yīng)用

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

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

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

完成后，點(diǎn)擊鏈接服務(wù)器進(jìn)行測(cè)試，成功后即可查詢(xún)數(shù)據(jù)。查詢(xún)時(shí)，使用openQuery方法，參數(shù)為鏈接服務(wù)器對(duì)象和SQL語(yǔ)句，例如：

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

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

查詢(xún)語(yǔ)句繁瑣，可以建立相關(guān)視圖，之后查詢(xún)時(shí)從視圖查詢(xún)即可。

該方法適用于ORACLE、POSTGRESQL、Mysql等不同種類(lèi)的數(shù)據(jù)庫(kù)，只需要在SQL SERVER服務(wù)器上安裝相應(yīng)的數(shù)據(jù)庫(kù)ODBC驅(qū)動(dòng)，并建立數(shù)據(jù)源即可，同樣也適用于SQL SERVER本地的跨庫(kù)查詢(xún)。[2]

（2）基于POSTGRESQL的DBLINK實(shí)現(xiàn)跨庫(kù)查詢(xún)

POSTGRESQL結(jié)構(gòu)和SQL SERVER基本相同，在同一個(gè)端口5432下的一個(gè)實(shí)例中可能存在多個(gè)數(shù)據(jù)庫(kù)，這些數(shù)據(jù)庫(kù)之間的數(shù)據(jù)需要相互訪問(wèn)時(shí)，就需要進(jìn)行跨庫(kù)查詢(xún)。

在POSTGRESQL數(shù)據(jù)庫(kù)中，需要使用DBLINK來(lái)進(jìn)行跨庫(kù)查詢(xún)。首先使用create extention dblink語(yǔ)句啟用dblink功能擴(kuò)展。

和SQL SERVER相同，每次查詢(xún)時(shí)都輸入這么長(zhǎng)一串字符非常不便，可以建立相關(guān)視圖，之后查詢(xún)時(shí)從視圖查詢(xún)即可。[3]

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

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

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

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

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

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

此處跨庫(kù)操作不同于之前的跨庫(kù)查詢(xún)，對(duì)遠(yuǎn)程數(shù)據(jù)庫(kù)鏈接的數(shù)據(jù)無(wú)影響，此項(xiàng)操作將對(duì)遠(yuǎn)程數(shù)據(jù)庫(kù)進(jìn)行刪除和插入操作，一定要謹(jǐn)慎。

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

4、結(jié)語(yǔ)

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

參考文獻(xiàn)

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

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

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