ITPUB论坛-中国最专业的IT技术社区

 找回密码
 注册
查看: 1696|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2017-3-26 UPDATE语句

[复制链接]
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
发表于 2017-4-10 22:45 | 显示全部楼层 |阅读模式

最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

每两周的优胜者可获得itpub奖励的技术图书一本。

以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808

原始出处:
http://www.plsqlchallenge.com/

作者:        Kim Berg Hanse

运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品

我有一张产品表,显示每个产品的价格和成本。我还有一张表,可用来准备下次修改产品价格的数据,说明到时候可以对价格和成本增加或者减少多少。

create table qz_product (
   id          integer primary key
, name        varchar2(20)
, price       number  not null
, cost        number  not null
)
/

create table qz_product_update (
   id          integer not null unique references qz_product
, price_add   number  not null
, cost_add    number  not null
)
/

insert into qz_product values (1, 'T-shirt, Black', 32, 12)
/
insert into qz_product values (2, 'Jeans, Blue'   , 66, 24)
/
insert into qz_product values (3, 'Shorts, Green' , 37, 18)
/
insert into qz_product values (4, 'Shirt, White'  , 42, 16)
/

insert into qz_product_update values (1,  3, -1)
/
insert into qz_product_update values (3,  2,  0)
/
insert into qz_product_update values (4, -1,  1)
/

commit
/

在新价格生效那天,我们需要把产品表的PRICE 和 COST的值分别加上PRICE_ADD 和 COST_ADD 。

哪些选项成功执行不出错并且对数据做了如此修改,使得选项执行之后这个查询会返回所示的输出:

select id, name, price, cost
  from qz_product
order by id
/

        ID NAME                      PRICE       COST
---------- -------------------- ---------- ----------
         1 T-shirt, Black               35         11
         2 Jeans, Blue                  66         24
         3 Shorts, Green                39         18
         4 Shirt, White                 41         17
         
         
(A)
update qz_product p
   set (p.price, p.cost) = (
          select p.price + u.price_add
               , p.cost  + u.cost_add
            from qz_product_update u
           where u.id = p.id
       )
/

(B)
update qz_product p
   set (p.price, p.cost) = (
          select p.price + u.price_add
               , p.cost  + u.cost_add
            from qz_product_update u
           where u.id = p.id
       )
where exists (
   select null
    from qz_product_update u
   where u.id = p.id
)
/


(C)
update qz_product p
   set p.price = (
          select p.price + u.price_add
            from qz_product_update u
           where u.id = p.id
       )
     , p.cost = (
          select p.cost  + u.cost_add
            from qz_product_update u
           where u.id = p.id
       )
where exists (
   select null
    from qz_product_update u
   where u.id = p.id
)
/

(D)
update qz_product p
   set (p.price, p.cost) = (
          (
             select p.price + u.price_add
               from qz_product_update u
              where u.id = p.id
          )
        , (
             select p.cost  + u.cost_add
               from qz_product_update u
              where u.id = p.id
          )
       )
where exists (
   select null
    from qz_product_update u
   where u.id = p.id
)
/

(E)
update qz_product p
   set (p.price, p.cost) = coalesce(
          (
             select p.price + u.price_add
                  , p.cost  + u.cost_add
               from qz_product_update u
              where u.id = p.id
          )
        , (p.price, p.cost)
       )
/

(F)
update qz_product p
   set p.price = coalesce(
          (
             select p.price + u.price_add
               from qz_product_update u
              where u.id = p.id
          )
        , p.price
       )
     , p.cost = coalesce(
          (
             select p.cost  + u.cost_add
               from qz_product_update u
              where u.id = p.id
          )
        , p.cost
       )
/

(G)
update qz_product p
   set (p.price, p.cost) = (
          select p.price + coalesce(max(u.price_add), 0)
               , p.cost  + coalesce(max(u.cost_add ), 0)
            from qz_product_update u
           where u.id = p.id
       )
/

(H)
merge into qz_product p
using qz_product_update u
   on (p.id = u.id)
when matched then update
   set p.price = p.price + u.price_add
     , p.cost  = p.cost  + u.cost_add
/

(I)
merge into qz_product p
using qz_product_update u
   on (p.id = u.id)
when matched then update
   set (p.price, p.cost) =
          (p.price + u.price_add, p.cost  + u.cost_add)
/

(J)
update (
   select p.price, p.cost, u.price_add, u.cost_add
     from qz_product p
     join qz_product_update u
         on u.id = p.id
)
   set price = price + price_add
     , cost  = cost  + cost_add
/

(K)
update (
   select p.price, p.cost, u.price_add, u.cost_add
     from qz_product p
     join qz_product_update u
         on u.id = p.id
)
   set (price, cost) = (price + price_add, cost  + cost_add)
/

论坛徽章:
393
雪佛兰
日期:2013-12-04 20:30:02马上有钱
日期:2014-03-11 11:59:122014年世界杯参赛球队:喀麦隆
日期:2014-07-11 12:10:53马上有对象
日期:2014-04-09 16:19:542014年世界杯参赛球队: 洪都拉斯
日期:2014-06-25 08:25:55itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-10-01 15:27:22itpub13周年纪念徽章
日期:2014-10-09 12:04:18马上有钱
日期:2014-10-14 21:37:37马上有钱
日期:2015-01-22 00:39:13
发表于 2017-4-11 05:48 来自手机 | 显示全部楼层
主要是考外框where和更新视图,突然发现这个系列是正确提问的模板

使用道具 举报

回复
论坛徽章:
233
双鱼座
日期:2016-04-29 17:13:05处女座
日期:2016-10-27 22:16:58天枰座
日期:2015-12-28 11:03:38巨蟹座
日期:2015-12-20 15:00:56巨蟹座
日期:2015-12-14 21:46:03天枰座
日期:2015-11-30 15:57:24天蝎座
日期:2016-08-16 09:49:11秀才
日期:2015-11-23 10:00:44白羊座
日期:2015-11-29 10:44:09红宝石
日期:2015-11-18 17:14:00
发表于 2017-4-11 18:09 | 显示全部楼层
答案:BCFGHJ

A: 没有匹配的项会更新为NULL
B: 纠正A,where条件限定符合条件的行,常规的写法
C: 把更新的字段分开逐个地写,也OK
D:  (p.price, p.cost) 这种更新右边必须是子查询,不能为表达式
E:  与D同原因
F:  分开字段更新,可以是子查询,也可以为表达式
G: 利用coalesce函数将不匹配更新为null的值做了原字段值的替换
H: merge 的典型写法
I:  merge 语句中不支持组合式更新
J:  更新视图的写法
K: 与D同

使用道具 举报

回复
论坛徽章:
454
秀才
日期:2015-08-18 09:49:27秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01状元
日期:2015-09-09 10:34:21榜眼
日期:2015-09-09 10:34:21秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01秀才
日期:2015-09-09 10:33:01
 楼主| 发表于 2017-4-12 04:54 | 显示全部楼层
答案BCFGHJ, 3楼得奖。

A: 牛仔裤(Jeans)没有一个要修改的记录,所以对于Jeans子查询会返回两个NULL,并且我们会得到错误:
ORA-01407: cannot update ("SCOTT"."QZ_PRODUCT"."PRICE") to NULL.

B: 用了EXISTS谓词就能确保我们仅仅在修改数据存在的时候才进行修改,所以这方法可行,我们不会得到前一选项的错误。

C:(不推荐)
如果不像前一选项那样用一个标量子查询,我们当然也可以用两个标量子查询,每个返回一个值。

D: 一次性修改两个值不能够使用这样的表达式列表,所以我们会得到这个错误:
ORA-00907: missing right parenthesis.

E: 当我们设置多个值的列表,我们必须将它设置为子查询,而不是函数调用的结果。这会报错:
ORA-01767: UPDATE ... SET expression must be a subquery.

F:(不推荐)
如果我们分开设置每个值,我们就可以使用函数调用,所以这是可行的。此处我们修改了所有的产品,但是那些没有修改记录的会被改成它们本来的值。

G:
子查询中带了MAX, 我们就能确保它会返回一行(而不是像A选项有时会返回零行),当修改记录不存在,这一行就会是NULL值,然后我们可以用COALESCE变成0,这样实际上就会把Jeans改成本来的价格和成本。

H:(推荐)
MERGE语句不用检查修改记录是否存在再修改,它就是对付这种情况的,它只会修改需要修改的行,而不必多次访问修改记录表。

I: 此处我们不允许在SET子句使用多个列的列表,会报错:
ORA-01747: invalid user.table.column, table.column, or column specification.

J:(推荐)
修改一个连接结果也是可以的,因为QZ_PRODUCT_UPDATE中的ID列是唯一的。

K: 但是表达式列表的语法在此处也是不允许的,会报错:
ORA-01767: UPDATE ... SET expression must be a subquery.

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表