[font=Tahoma,][size=26px]一个远程引发的Oracle Update思考[font=Tahoma,]今天远程帮一同事研究了一个update语句,同事原本的语句这样写的：
UPDATE PAYSYSTEM_2
SET C21_c21_ysgz=(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where gz_ym=C21_c21e7)
了解了需求后，知道同事要的效果是， 用上个月的各项工资算出来本月的应税工资即C21_c21_ysgz，PAYSYSTEM_2是本月工资的临时表，C21_c21e7是上月月份。
很显然， PAYSYSTEM_2，c21没有关联，没有一对一，会报返回值返回多个的错误。
如果是sql server很简单：
UPDATE PAYSYSTEM_2
SET C21_c21_ysgz= a.s (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as s
from c21 where gz_ym=C21_c21e7) a,PAYSYSTEM_2
where a.a01_key= PAYSYSTEM_2.a01_key
就ok了，但是oracle 没有update from语法。
oracle 则要如下：
主要方式有4种
1、子查询方式
UPDATE PAYSYSTEM_2 p
SET p.C21_c21_ysgz=
(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where p.a01_key=c21.a01_key
and gz_ym=C21_c21e7);

UPDATE PAYSYSTEM_2 p
SET p.C21_c21_ysgz=
(select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0)
from c21 where gz_ym=C21.c21e7
and p.a01_key=c21.a01_key)
where exists(select 1 from c21
where p.a01_key=c21.a01_key
and gz_ym=C21_c21e7 );
以上2种写法区别就是:
第二条，更无严谨，不满足条件的不去动；
第一条，不满足条件的会被替换成null；
这里不满足条件指的是子查询里有，被更新表里没有。
根据业务可以知道，初始表为空，所以它们都能实现业务需求，我们当然选择第一条，因为它只有一个子查询，只需查询一次c21表，效率会好些。
tips：先过滤，再关联，会缩小结果集，所以where后的条件不要搞反。

2、游标更新
begin
cr in (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,a01_key
from c21 where gz_ym=C21_c21e7)
loop
update PAYSYSTEM_2 p set p.C21_c21_ysgz=cr.a
where cr.a01_key=p.a01_key;
end loop;
end
此种方式充分利用了PAYSYSTEM_2这张临时表特性，效率比子查询要好些。

3、inline view更新
update
(select select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,p.C21_c21_ysgz b
from c21,PAYSYSTEM_2 p where p.a01_key=c21.a01_key and c21.gz_ym=c21.C21_c21e7)
set b=a
如果视图基于多个表的连接，那么用户更新视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键，否则不能更新视图的基表。

4、merge更新法
merge into PAYSYSTEM_2 p
using (select nvl(c21.c21_yfgz,0)-nvl(c21.c2120,0)-nvl(c21.c211c,0)-nvl(c21.c211e,0)-nvl(c21.c211f,0)-vl(c21.c2131,0) as a,a01_key from c21 where gz_ym=C21_c21e7) c
on (p.a01_key=a.a01_key)
when matched then
update p set p.C21_c21_ysgz=c.a

5、结论
我自己用7万多条数据简单测试了一下：
1、子查询方式：分别用了8s多，6s多；
2、游标方式：3s多
3、inline view方式：1s多
4、merge方式：1s多
5、标准update语法，单表更新或较简单的语句采用使用此方案更优。
inline view更新法，两表关联且被更新表通过关联表主键关联的，采用此方案更优。
merge更新法，两表关联且被更新表不是通过关联表主键关联的，采用此方案更优。
游标更新法 ，多表关联且逻辑复杂的，采用此方案更优。
6、以上只是基于个人简单测试，不一定准，还是根据执行计划去选择最优方式，提高语句效率。

完工
休息了。