對O racl e多表關(guān)聯(lián)更新的應用研究

許俊

（四川職業(yè)技術(shù)學院，四川 遂寧　629000）

對O racl e多表關(guān)聯(lián)更新的應用研究

許俊

（四川職業(yè)技術(shù)學院，四川 遂寧629000）

摘要：本文針對eY LX Z電商項目數(shù)據(jù)庫多表關(guān)聯(lián)的顯式與自動更新數(shù)據(jù)的需求，詳細分析設計了數(shù)據(jù)庫的E-R圖、項目表，前臺手動顯式初始化更新及O racle后臺自動執(zhí)行程序動態(tài)更新數(shù)據(jù)的算法，編寫PL/SQ L語句、程序?qū)崿F(xiàn)需求，并分析update、merge兩種語句的執(zhí)行成本.

關(guān)鍵詞：O racle數(shù)據(jù)庫；多表關(guān)聯(lián)；更新

儀劉鑫梓貿(mào)易公司是一家大型百貨連鎖代理銷售公司，近年來積累了大量的客戶資源，一直采用傳統(tǒng)的實體店鋪面對面銷售模式，為擴大規(guī)模，提升市場競爭力，儀劉鑫梓決定搭建電子商務銷售平臺eYLXZ，同時開拓線下線上營銷市場.鑒于eYLXZ電商平臺頻繁的數(shù)據(jù)讀寫操作，各連鎖店地理位置分散，客戶資源及商品信息共享及安全性，決定使用穩(wěn)定性優(yōu)，可移植的Oracle數(shù)據(jù)庫作后臺存儲.

eYLXZ平臺業(yè)務處理模塊涉及的簡化實體有客戶（客戶編號，客戶名稱），商品（商品編號，商品名稱，單價），銷售（訂單編號，客戶編號，商品編號，購買數(shù)量）.則得到關(guān)系：客戶 customers（cid，cname），商品goods（gid，gname，gprice），銷售sales（sid，cid，gid，samounts）.

最近公司為激勵老客戶，吸引發(fā)展新客戶，實行購買商品積分制的策略.客戶實體增加總積分屬性，商品實體添加積分屬性.客戶、商品實體之間的E-R聯(lián)系如下圖：

根據(jù)以上三個實體的E-R聯(lián)系轉(zhuǎn)換為關(guān)系，修改上述關(guān)系，得到新的客戶、商品關(guān)系，客戶customers（cid，cname，credits），商品goods（gid， gname，gprice，gpoint），銷售sales（sid，cid，gid，samounts）.設計表如下：

客戶表customers

商品表goods

銷售表sales

eYLXZ平臺的銷售規(guī)則約定：（1）一條定單某種商品如果只購買單件則不積分，換句話說，一條定單里的某種商品必須購買2件或更多才得積分；（2）某種商品若購買時掙到了積分，部分退貨退款后客戶實際購買數(shù)量低于2件，則要從該客戶總積分中扣除相應商品積分，若未掙到積分，則部分退貨退款后該客戶總積分不變；（3）若一條訂單內(nèi)所有商品全部退貨退款，則銷售表將刪除此訂單信息，并調(diào)整積分，調(diào)整的辦法是，若此訂單內(nèi)商品低于2件，購買時并沒有掙到積分，則不減少客戶總積分，若此訂單內(nèi)商品等于或大于2件，購買時掙到了積分，則要從客戶總積分中扣減退貨商品的相應積分.

1　手動更新關(guān)聯(lián)表

修改表結(jié)構(gòu)之后，從商品表、銷售表關(guān)聯(lián)計算出每位客戶總積分，并用它初始化客戶表的總積分.初始化只需要執(zhí)行一次就計算出當前總積分，并填充到客戶表總積分credits列.執(zhí)行查詢語句或PL/SQL程序?qū)崿F(xiàn)手動初始化積分.

1.1初始化積分的算法

首先連接goods表和salse表，分組求和計算出每位客戶的總積分，然后關(guān)聯(lián)更新到customers.

1.2初始化積分的實現(xiàn)

1.2.1純查詢的實現(xiàn)

實現(xiàn)初始化有兩種方法，其一是使用update語句關(guān)聯(lián)三張表，update中包含了連接goods表和sales表的子查詢.其二是使用merge語句合并總積分到customers表.

1）使用update的PL/SLQ語句：

首先創(chuàng)建視圖vw_xj_totaljf，連接goods表和salse表，計算出每位客戶的總積分total_jf.

createviewvw_xj_totaljf

as

selectsales.cid，sum（goods.gpoint）as total_jf

from sales inner join goods on sales.gid= goods.gid

wheresamounts＞=2 groupbysales.cid；

然后使用update語句關(guān)聯(lián)視圖vw_xj_totaljf，更新customers表對應客戶的總積分.

update customers setcredits=

（

select total_jf

fromvw_xj_totaljfwherevw_xj_totaljf.cid =customers.cid

）where exists （select*from vw_xj_totaljf where customers.cid=vw_xj_totaljf.cid）；

update語句的credits列的值設置成單行子查詢返回的結(jié)果，即total_jf，注意此處的子查詢返回結(jié)果一定是單行不能是多行，否則會出現(xiàn)SQL錯誤ORA-01427:單行子查詢返回多個行，導致更新失敗.

Update更新 customers時，取出每行的vw_xj_totaljf.cid值，去匹配customers.cid，故多次掃描customers表，效率稍低，但兼容oracle的低版本，在數(shù)據(jù)量少時可以考慮這種方式.

2）使用merge的PL/SLQ語句：

mergeintocustomers

using（

selectsales.cid，sum（goods.gpoint）to tal_points fromsales

innerjoingoodsongoods.gid=sales.gid wheresales.samounts＞=2

groupby sales.cid ）jfb

on（customers.cid=jfb.cid）

whenmatchedthen

update setcredits=jfb.total_points；

merge方法是最簡潔，效率最高的方式，在大數(shù)據(jù)量更新時優(yōu)先使用這種方式.僅需要一次全表掃描就完成了全部工作，執(zhí)行效率要高于update.

純查詢語句的實現(xiàn)方式不帶參數(shù)，適合于初始化全體客戶的積分.

1.2.2編寫程序?qū)崿F(xiàn)

除了上述的語句實現(xiàn)方法，還可以編寫PL/SQL游標程序，用存儲過程進行封裝，經(jīng)過編譯的存儲過程，其執(zhí)行效率、安全性、模塊化調(diào)用都得到了加強.由于游標允許帶參數(shù)，用客戶編號作為參數(shù)，初始化某個客戶的積分，或用表達式作參數(shù)，更新符合某種條件的部分客戶積分.

下面存儲過程中的游標未帶參數(shù)，更新全部客戶的積分.

create or replace procedure update_sales_credits

as

v_cid customers.cid%type；

v_totaljf number；

cursorcur_UpdateCreditsis

select sales.cid，sum（goods.gpoint）to-tal_jffromsales

inner join goods on goods.gid=sales.gid wheresales.samounts＞=2

groupbysales.cid；

begin

opencur_UpdateCredits；loop

fetch cur_UpdateCredits into v_cid，v_totaljf；

exitwhencur_UpdateEdits%notfound；

update customers set credits=v_totaljf wherecid=v_cid；

endloop；

closecur_UpdateCredits；

end；

下面存儲過程中的游標帶參數(shù)客戶編號p_cid，根據(jù)客戶編號更新總積分.

createorreplaceprocedureupdate_sales_credits（p_cidinchar）

as

v_cid customers.cid%type；

v_totaljf number；cursorcur_UpdateCredits（p_cidchar）is select sales.cid，sum（goods.gpoint）total_jffromsales

inner join goods on goods.gid=sales.gid andcid=p_cidwheresales.samounts＞=2

groupbysales.cid；begin

opencur_UpdateCredits（P_cid）；loop

fetch cur_UpdateCredits into v_cid，v_totaljf；

exitwhencur_UpdateEdits%notfound；

update customers set credits=v_totaljfwherecid=v_cid；

endloop；

closecur_UpdateCredits；end；

2　自動更新關(guān)聯(lián)表

客戶的積分會隨客戶的消費行為變動，積分值應該自動實時計算并反映到customers表，根據(jù)sales表客戶訂單動態(tài)變化情況，實時計算總積分credits并更新到customers表中.對sales表編寫觸發(fā)器實現(xiàn)自動實時更新積分.

2.1積分的動態(tài)計算算法

2.1.1購買商品，即增加銷售表sales的記錄，算法見如下流程圖：

如果數(shù)量 samounts＞=2，則該客戶總積分credits加上該商品的積分，即customers.credits=customers.credits+goods.gpoint， 條 件sales.gid=goods.gid；

如果數(shù)量samounts＜2，則該客戶總積分credits不變.

2.1.2部分商品退貨退款，即修改銷售表sales的記錄，算法見如下流程圖.

如果原數(shù)量＞=2時：新數(shù)量samounts＜2，則該客戶總積分 credits減去該商品的積分，即customers.credits=customers.credits-goods. gpoint，條 件 sales.gid=goods.gid； 新 數(shù) 量samounts＞=2，則該客戶總積分credits不變.

如果原數(shù)量＜2:新數(shù)量samounts＜2，則該客戶總積分credits不變.

2.1.3全部退貨退款，即刪除銷售表sales的記錄，算法見如下流程圖：

如果數(shù)量 samounts＞=2，則該客戶總積分credits減去該商品的積分，即customers.cred its=customers.credits-goods.gpoint，條件sales. gid=goods.gid；

如果數(shù)量samounts＜2，則該客戶總積分credits不變.

2.2積分動態(tài)計算實現(xiàn)

在銷售表sales編寫觸發(fā)器，對sales表insert、update、delete記錄時，customers的總積分credits會被實時地自動計算，新的積分值填充到credits列.代碼如下：

createorreplacetriggertr_cal_credits afterinsertorupdateordeleteonsales

——sales增加、修改、刪除記錄之后foreach row declare

goods_gpointgoods.gpoint%type；

begin

select gpoint into goods_gpoint from goods wheregid=:new.gid；

if:old.samounts is null then--沒有原值（數(shù)量），表示是insert

if:new.samounts＞=2 then--增加的數(shù)量＞=2，加積分

update customers set credits=credits+goods_gpointwherecid=:new.cid；

endif；

else——有原值（數(shù)量）、新值，表示是update.

——修改數(shù)量時，重新計算客戶的總積分

——當原數(shù)量＞=2且新的數(shù)量小于2時，減對應商品的積分.

if（:old.samounts＞=2）and（:new.samounts＜2）then

update customers set credits=creditsgoods_gpointwherecid=:new.cid；

endif；

——當原數(shù)量＜2并且新的數(shù)量＞=2分時，加對應商品的積分.

if（:old.samounts＜2）and（:new.samounts ＞=2）then

update customers set credits=credits+ goods_gpointwhere cid=:new.cid；

endif；

if:old.samounts is not null and:new. samountsisnullthen--有原值沒有新值，表示是delete，減對應商品的積分.

select gpoint into goods_gpoint from goods wheregid=:old.gid；

if:old.samounts＞=2then

update customers set credits=credits-goods_gpointwherecid=:old.cid；

endif；endif；endif；end；

此觸發(fā)器在銷售表sales增加、修改、刪除記錄之后就自動觸發(fā)，判斷old.samounts和new. samounts值的情況，分別為 insert、update、delete操作，執(zhí)行update語句增加或減少積分credits.

3　執(zhí)行更新的成本優(yōu)化分析

下表是使用Update更新customers時生成的解釋計劃，它反映了訪問相關(guān)表數(shù)據(jù)、視圖、約束的最有效路徑，通過它可以判斷update操作的成本.

表中的ID列和Parent_ID列確定了執(zhí)行查詢時優(yōu)化將遵循的步驟，即Operations的層次結(jié)構(gòu). 此update操作的執(zhí)行步驟順序是16、15、17、18、14、13、8、7、9、10、6、5、4、3、2、11、12、1、0，共19個操作步驟.可以看出對sales全表掃描了2次，update的總成本是10個工作單元。

下表是使用merge更新customers時生成的解釋計劃，它反映訪問相關(guān)表數(shù)據(jù)、約束的最有效路徑.

此merge操作的執(zhí)行步驟順序是9、8、10、11、7、6、5、4、12、13、3、2、1、0，共14個操作步驟.可以看出只對sales全表掃描了1次，merge語句的總成本是5個工作單元.

比較上述兩個執(zhí)行計劃，當更新關(guān)聯(lián)的多表時，merge語句的總成本比update語句的總成本減少了5個工作單元，減少比例100%，根據(jù)基于成本的優(yōu)化比較，建議在數(shù)據(jù)量特別大時，使用merge，但需要高版本的支持，而數(shù)據(jù)量少時，在低版本中使用update.但綜合考慮軟硬件、代碼移植性等其它因素，除了I/O、CPU的成本消耗，并不意味著merge在任何運行環(huán)境下總是首選于merge，須視具體應用作選擇.

4　結(jié)語

Oracle多表關(guān)聯(lián)更新數(shù)據(jù)時，如果要由應用程序前端手動操作更新，可用初始化積分的update、merge語句方式；如果由Oracle后臺動態(tài)更新表數(shù)據(jù)，在目標表創(chuàng)建觸發(fā)器實現(xiàn).并綜合衡量運行環(huán)境各要素，選擇相對優(yōu)化的方案。

參考文獻：

［1］許俊.對非1NF關(guān)系查詢的探討［J］.四川職業(yè)技術(shù)學院學報，2013，（5）.

［2］徐卓.關(guān)于Oracle數(shù)據(jù)庫設計、開發(fā)、應用的探討［J］.鐵路計算機應用，2014，（9）.

［3］陳建云.Oracle應用系統(tǒng)數(shù)據(jù)庫的優(yōu)化探討［J］.信息安全與技術(shù)，2014，（12）.

責任編輯：張隆輝

中圖分類號：TP392

文獻標識碼：B

文章編號：1672-2094（2015）02-0159-05

收稿日期:2015-02-27

基金項目：四川省教育廳重點科研項目《基于NoSQL大數(shù)據(jù)技術(shù)的信息搜索方案研究》（15ZA0348）成果之一。

作者簡介：許?。?969-），男，四川蓬溪人，四川職業(yè)技術(shù)學院計算機科學系副教授，碩士。

Applied Research on Oracle Multi-table Related Update

XUJun
（Sichuan Vocational and Technical College， Suining Sichuan 629000）

Abstract:Aiming at the demand of eYLXZ electricity supplier project database multi table connection explicit and automatically update data， this paper analyzes the design of database E-R chart，table， the algorithm of procedure manual explicit initialization and Oracle auto update， writes a PL/SQL statement， analyzes the statement execution cost of update and merge.

Keywords:Oracle Multi Table Database； Multi-table Association； Update