对O racl e多表关联更新的应用研究

许俊

（四川职业技术学院，四川 遂宁　629000）

对O racl e多表关联更新的应用研究

许俊

（四川职业技术学院，四川 遂宁629000）

摘要：本文针对eY LX Z电商项目数据库多表关联的显式与自动更新数据的需求，详细分析设计了数据库的E-R图、项目表，前台手动显式初始化更新及O racle后台自动执行程序动态更新数据的算法，编写PL/SQ L语句、程序实现需求，并分析update、merge两种语句的执行成本.

关键词：O racle数据库；多表关联；更新

仪刘鑫梓贸易公司是一家大型百货连锁代理销售公司，近年来积累了大量的客户资源，一直采用传统的实体店铺面对面销售模式，为扩大规模，提升市场竞争力，仪刘鑫梓决定搭建电子商务销售平台eYLXZ，同时开拓线下线上营销市场.鉴于eYLXZ电商平台频繁的数据读写操作，各连锁店地理位置分散，客户资源及商品信息共享及安全性，决定使用稳定性优，可移植的Oracle数据库作后台存储.

eYLXZ平台业务处理模块涉及的简化实体有客户（客户编号，客户名称），商品（商品编号，商品名称，单价），销售（订单编号，客户编号，商品编号，购买数量）.则得到关系：客户 customers（cid，cname），商品goods（gid，gname，gprice），销售sales（sid，cid，gid，samounts）.

最近公司为激励老客户，吸引发展新客户，实行购买商品积分制的策略.客户实体增加总积分属性，商品实体添加积分属性.客户、商品实体之间的E-R联系如下图：

根据以上三个实体的E-R联系转换为关系，修改上述关系，得到新的客户、商品关系，客户customers（cid，cname，credits），商品goods（gid， gname，gprice，gpoint），销售sales（sid，cid，gid，samounts）.设计表如下：

客户表customers

商品表goods

销售表sales

eYLXZ平台的销售规则约定：（1）一条定单某种商品如果只购买单件则不积分，换句话说，一条定单里的某种商品必须购买2件或更多才得积分；（2）某种商品若购买时挣到了积分，部分退货退款后客户实际购买数量低于2件，则要从该客户总积分中扣除相应商品积分，若未挣到积分，则部分退货退款后该客户总积分不变；（3）若一条订单内所有商品全部退货退款，则销售表将删除此订单信息，并调整积分，调整的办法是，若此订单内商品低于2件，购买时并没有挣到积分，则不减少客户总积分，若此订单内商品等于或大于2件，购买时挣到了积分，则要从客户总积分中扣减退货商品的相应积分.

1　手动更新关联表

修改表结构之后，从商品表、销售表关联计算出每位客户总积分，并用它初始化客户表的总积分.初始化只需要执行一次就计算出当前总积分，并填充到客户表总积分credits列.执行查询语句或PL/SQL程序实现手动初始化积分.

1.1初始化积分的算法

首先连接goods表和salse表，分组求和计算出每位客户的总积分，然后关联更新到customers.

1.2初始化积分的实现

1.2.1纯查询的实现

实现初始化有两种方法，其一是使用update语句关联三张表，update中包含了连接goods表和sales表的子查询.其二是使用merge语句合并总积分到customers表.

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

首先创建视图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语句关联视图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列的值设置成单行子查询返回的结果，即total_jf，注意此处的子查询返回结果一定是单行不能是多行，否则会出现SQL错误ORA-01427:单行子查询返回多个行，导致更新失败.

Update更新 customers时，取出每行的vw_xj_totaljf.cid值，去匹配customers.cid，故多次扫描customers表，效率稍低，但兼容oracle的低版本，在数据量少时可以考虑这种方式.

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方法是最简洁，效率最高的方式，在大数据量更新时优先使用这种方式.仅需要一次全表扫描就完成了全部工作，执行效率要高于update.

纯查询语句的实现方式不带参数，适合于初始化全体客户的积分.

1.2.2编写程序实现

除了上述的语句实现方法，还可以编写PL/SQL游标程序，用存储过程进行封装，经过编译的存储过程，其执行效率、安全性、模块化调用都得到了加强.由于游标允许带参数，用客户编号作为参数，初始化某个客户的积分，或用表达式作参数，更新符合某种条件的部分客户积分.

下面存储过程中的游标未带参数，更新全部客户的积分.

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；

下面存储过程中的游标带参数客户编号p_cid，根据客户编号更新总积分.

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　自动更新关联表

客户的积分会随客户的消费行为变动，积分值应该自动实时计算并反映到customers表，根据sales表客户订单动态变化情况，实时计算总积分credits并更新到customers表中.对sales表编写触发器实现自动实时更新积分.

2.1积分的动态计算算法

2.1.1购买商品，即增加销售表sales的记录，算法见如下流程图：

如果数量 samounts＞=2，则该客户总积分credits加上该商品的积分，即customers.credits=customers.credits+goods.gpoint， 条 件sales.gid=goods.gid；

如果数量samounts＜2，则该客户总积分credits不变.

2.1.2部分商品退货退款，即修改销售表sales的记录，算法见如下流程图.

如果原数量＞=2时：新数量samounts＜2，则该客户总积分 credits减去该商品的积分，即customers.credits=customers.credits-goods. gpoint，条 件 sales.gid=goods.gid； 新 数 量samounts＞=2，则该客户总积分credits不变.

如果原数量＜2:新数量samounts＜2，则该客户总积分credits不变.

2.1.3全部退货退款，即删除销售表sales的记录，算法见如下流程图：

如果数量 samounts＞=2，则该客户总积分credits减去该商品的积分，即customers.cred its=customers.credits-goods.gpoint，条件sales. gid=goods.gid；

如果数量samounts＜2，则该客户总积分credits不变.

2.2积分动态计算实现

在销售表sales编写触发器，对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--没有原值（数量），表示是insert

if:new.samounts＞=2 then--增加的数量＞=2，加积分

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

endif；

else——有原值（数量）、新值，表示是update.

——修改数量时，重新计算客户的总积分

——当原数量＞=2且新的数量小于2时，减对应商品的积分.

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

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

endif；

——当原数量＜2并且新的数量＞=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；

此触发器在销售表sales增加、修改、删除记录之后就自动触发，判断old.samounts和new. samounts值的情况，分别为 insert、update、delete操作，执行update语句增加或减少积分credits.

3　执行更新的成本优化分析

下表是使用Update更新customers时生成的解释计划，它反映了访问相关表数据、视图、约束的最有效路径，通过它可以判断update操作的成本.

表中的ID列和Parent_ID列确定了执行查询时优化将遵循的步骤，即Operations的层次结构. 此update操作的执行步骤顺序是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时生成的解释计划，它反映访问相关表数据、约束的最有效路径.

此merge操作的执行步骤顺序是9、8、10、11、7、6、5、4、12、13、3、2、1、0，共14个操作步骤.可以看出只对sales全表扫描了1次，merge语句的总成本是5个工作单元.

比较上述两个执行计划，当更新关联的多表时，merge语句的总成本比update语句的总成本减少了5个工作单元，减少比例100%，根据基于成本的优化比较，建议在数据量特别大时，使用merge，但需要高版本的支持，而数据量少时，在低版本中使用update.但综合考虑软硬件、代码移植性等其它因素，除了I/O、CPU的成本消耗，并不意味着merge在任何运行环境下总是首选于merge，须视具体应用作选择.

4　结语

Oracle多表关联更新数据时，如果要由应用程序前端手动操作更新，可用初始化积分的update、merge语句方式；如果由Oracle后台动态更新表数据，在目标表创建触发器实现.并综合衡量运行环境各要素，选择相对优化的方案。

参考文献：

［1］许俊.对非1NF关系查询的探讨［J］.四川职业技术学院学报，2013，（5）.

［2］徐卓.关于Oracle数据库设计、开发、应用的探讨［J］.铁路计算机应用，2014，（9）.

［3］陈建云.Oracle应用系统数据库的优化探讨［J］.信息安全与技术，2014，（12）.

责任编辑：张隆辉

中图分类号：TP392

文献标识码：B

文章编号：1672-2094（2015）02-0159-05

收稿日期:2015-02-27

基金项目：四川省教育厅重点科研项目《基于NoSQL大数据技术的信息搜索方案研究》（15ZA0348）成果之一。

作者简介：许俊（1969-），男，四川蓬溪人，四川职业技术学院计算机科学系副教授，硕士。

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