查看: 558|回复: 3

[每日一题] PL/SQL Challenge 每日一题:2020-7-14 XMLTABLE

[复制链接]
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2020-7-24 01:49 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

原始出处:
https://devgym.oracle.com/

作者:Kim Berg Hansen

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

我有一张表,里面有一行,以XML格式保存着每个啤酒厂的库存:

create table qz_inventory (
   the_data xmltype
);

insert into qz_inventory values ('
<inventory>
   <brewery name="Balthazar Brauerei">
      <beer name="Der Helle Kumpel" qty="42" />
      <beer name="Monks and Nuns" qty="30" />
   </brewery>
   <brewery name="Happy Hoppy Hippo">
      <beer name="Summer in India" qty="50" />
      <beer name="Hazy Pink Cloud" qty="24" />
      <beer name="Ghost of Hops" qty="18" />
   </brewery>
</inventory>
');

commit;

我想要查看啤酒的库存,以列的形式显示啤酒厂名称,啤酒名称,库存量。

哪些选项可以执行不出错,产生这个所需的输出:

BREWERY_NAME         BEER_NAME              BEER_QTY
-------------------- -------------------- ----------
Balthazar Brauerei   Der Helle Kumpel             42
Balthazar Brauerei   Monks and Nuns               30
Happy Hoppy Hippo    Ghost of Hops                18
Happy Hoppy Hippo    Hazy Pink Cloud              24
Happy Hoppy Hippo    Summer in India              50

(A)
select br.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/' passing td.the_data
   columns
      inventory xmltype path '/inventory'
) i
, xmltable(
   '/' passing i.inventory
   columns
      brewery_name varchar2(20) path '/inventory/brewery/@name'
    , brewery      xmltype      path '/inventory/brewery'
) br
, xmltable(
   '/' passing br.brewery
   columns
      beer_name varchar2(20) path '/inventory/brewery/beer/@name'
    , beer_qty  integer      path '/inventory/brewery/beer/@qty'
) be
order by br.brewery_name, be.beer_name;

(B)
select br.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/inventory' passing td.the_data
   columns
      inventory xmltype path '*'
) i
, xmltable(
   '/brewery' passing i.inventory
   columns
      brewery_name varchar2(20) path '@name'
    , brewery      xmltype      path '*'
) br
, xmltable(
   '/beer' passing br.brewery
   columns
      beer_name varchar2(20) path '@name'
    , beer_qty  integer      path '@qty'
) be
order by br.brewery_name, be.beer_name;

(C)
select br.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/inventory/brewery' passing td.the_data
   columns
      brewery_name varchar2(20) path '@name'
    , brewery      xmltype      path '*'
) br
, xmltable(
   '/beer' passing br.brewery
   columns
      beer_name varchar2(20) path '@name'
    , beer_qty  integer      path '@qty'
) be
order by br.brewery_name, be.beer_name;

(D)
select be.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/inventory/brewery' passing td.the_data
   columns
      brewery_name varchar2(20) path '@name'
    , nested path '/beer'
         columns
            beer_name varchar2(20) path '@name'
          , beer_qty  integer      path '@qty'
) be
order by be.brewery_name, be.beer_name;

(E)
select be.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/inventory/brewery' passing td.the_data
   columns
      brewery_name varchar2(20) path '@name'
    , beer_name varchar2(20) path 'beer/@name'
    , beer_qty  integer      path 'beer/@qty'
) be
order by be.brewery_name, be.beer_name;


(F)
select be.brewery_name, be.beer_name, be.beer_qty
from qz_inventory td
, xmltable(
   '/inventory/brewery/beer' passing td.the_data
   columns
      brewery_name varchar2(20) path 'PARENT/@name'
    , beer_name varchar2(20) path '@name'
    , beer_qty  integer      path '@qty'
) be
order by be.brewery_name, be.beer_name;

论坛徽章:
16
山治
日期:2017-01-11 16:13:26技术图书徽章
日期:2017-08-23 14:17:00技术图书徽章
日期:2017-08-23 14:17:00秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05秀才
日期:2017-08-11 15:30:05
发表于 2020-7-24 09:25 | 显示全部楼层
答案  BC


A)  错误,运行报错 ORA-19279: XPTY0004 - XQuery 动态类型不匹配: 需要单例序列 - 但却得到多例序列

B)  正确,修复了A的问题,用/inventory、/brewery、/beer  指定了输出

C)  正确,调整了B的代码,/inventory、/brewery 调整为/inventory/brewery

D)  错误,运行报错  ORA-00907: 缺失右括号

E)  错误,运行报错  ORA-19279: XPTY0004 - XQuery 动态类型不匹配: 需要单例序列 - 但却得到多例序列

F)  错误,输出的 BREWERY_NAME一列为空,用/inventory/brewery/beer 替代了 /inventory、/brewery、/beer

使用道具 举报

回复
论坛徽章:
536
生肖徽章2007版:猴
日期:2008-05-16 11:28:59生肖徽章2007版:马
日期:2008-10-08 17:01:01SQL大赛参与纪念
日期:2011-04-13 12:08:17授权会员
日期:2011-06-17 16:14:53ITPUB元老
日期:2011-06-21 11:47:01ITPUB官方微博粉丝徽章
日期:2011-07-01 09:45:27ITPUB十周年纪念徽章
日期:2011-09-27 16:30:472012新春纪念徽章
日期:2012-01-04 11:51:22海蓝宝石
日期:2012-02-20 19:24:27铁扇公主
日期:2012-02-21 15:03:13
发表于 2020-7-24 10:26 | 显示全部楼层

答案:BC
A: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
B: 带根节点,XML 文档分3层,每层用xmltable独立解析,PATH路径查询中,XML标签属性要在前面加“@”符号,子XML文档,解析时仍然保留 xmltype 类型
C: 简化了B,从第二层开始,直接查询路径 /inventory/brewery
D: ORA-00907: missing right parenthesis,这个选项 nested path 看上去不错的想法,但11g的xmldb 文档中没有找到对应的语法
E: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
F: 用一次 XMLTABLE 就解决了,妙!, 可惜 path 'PARENT/@name' 解析为 NULL

使用道具 举报

回复
论坛徽章:
527
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
 楼主| 发表于 2020-7-25 00:17 | 显示全部楼层
答案BC, 2楼得奖。

A:
此处我们有一个问题,我们在每次XMLTABLE调用时,基本上都是传递根库存元素,也就是说不会为每个啤酒厂和啤酒生成一行,而只是单行。当单行中包含了一个列(如啤酒厂和啤酒名称以及数量)的路径的多个元素时,我们会得到这个错误:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence.

B:
此处我们在PASSING子句之前选出了路径中的单个元素,也就是说子查询BR为每个啤酒厂产生一条记录,子查询BE为每个啤酒产生一条记录。这样我们就避免了前一选项的错误,因为这里每一行都会有一个单元素供列路径查找。

C:
由于我们没有从库存节点中查询除了啤酒厂子节点之外的任何东西,我们可以减少之前的选项,只需两个XMLTABLE调用就能创建这个选项,而不是三个。

D:
NESTED PATH不是XMLTABLE中支持的语法,只有JSON_TABLE中才有。所以这里我们得到了错误:
ORA-00907: missing right parenthesis.


E:
在PASSING之前的路径中,我们只指定了brewery,所以XMLTABLE将为每个brewery生成一行。在COLUMNS中,我们再寻找BEER_NAME和BEER_QTY的路径,这些路径产生了多种啤酒----因此我们得到了与选项A相同的错误:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence.


F:
这个试图修复前一选项的错误,方法是在PASSING之前,通过在路径中添加/beer,使得XMLTABLE为每个啤酒生成一行。这样做是很好的,但是我们就会遇到一个问题,那就是要得到啤酒厂的名称,我们需要访问啤酒节点的父节点。这个选项试图用关键字PARENT来实现这个目标,但这是虚构的语法,所以它试图寻找一个名为PARENT的子元素,而这个子元素并不存在,所以在这个错误的输出中,啤酒厂名称变成了NULL:

BREWERY_NAME         BEER_NAME              BEER_QTY
-------------------- -------------------- ----------
                     Der Helle Kumpel             42
                     Ghost of Hops                18
                     Hazy Pink Cloud              24
                     Monks and Nuns               30
                     Summer in India              50

请注意,存在一些XPATH结构来访问父节点,例如'./.../@name'或'./parent::node()/@name'可能是BREWERY_NAME的有效路径,但它们是否有效取决于实现。这些构造在旧版本中似乎可以工作(已经观察到在11.2.0.4和12.1.0.2中可以工作),但在新版本中返回NULL。可能可以说,在旧版本中,这是一个无意的副作用,因为在PASSING子句中,我们只传递啤酒节点,所以它不应该工作,然后这可能在新版本中被修正了。

使用道具 举报

回复

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

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表