查看: 269|回复: 2

[每日一题] PL/SQL Challenge 每日一题:2019-11-28 JSON_ARRAY (12.2)

[复制链接]
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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
发表于 2019-12-3 04:59 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:Kim Berg Hansen

运行环境:SQLPLUS, SERVEROUTPUT已打开, 最低版本要求:12.2

注:本题给出答案时候要求给予简要说明才能得到奖品

我有一张书籍表:


create table qz_books (
   id       integer        primary key
, author   varchar2(20)   not null
, title    varchar2(20)   not null
, pages    integer        not null
);

insert into qz_books values (
   123, 'Patrick O''Malley' , 'Danny, Oh Danny'  , 242
);
insert into qz_books values (
   124, 'Janice E. Whalton' , 'Girl Named "Bill"',  93
);
insert into qz_books values (
   125, 'Petra Malthof, Dr.', 'Streets of Berlin', 717
);
insert into qz_books values (
   126, 'Bob "Bomber" Duffy', 'Bomber \VS\ Hulk' , 381
);
commit;

为了创建一个CSV文件,我想要把列数据用逗号隔开的字符串形式进行输出。正如许多典型的CSV文件一样,每个字符串列的值必须用双引号包含起来,字符串内部的双引号以及反斜杠字符必须用反斜杠做前缀进行转义。

哪些选项包含了一个查询,可以执行不出错,产生这个正确转义并且加了引号的用逗号隔开的字符串输出:

CSV
----------------------------------------------------------------------
123,"Patrick O'Malley","Danny, Oh Danny",242
124,"Janice E. Whalton","Girl Named \"Bill\"",93
125,"Petra Malthof, Dr.","Streets of Berlin",717
126,"Bob \"Bomber\" Duffy","Bomber \\VS\\ Hulk",381

(A)
select
   id
   || ',' || author
   || ',' || title
   || ',' || pages
      as csv
from qz_books
order by id;

(B)
select
   id
   || ',"' || replace(replace(author, '\', '\\'), '"', '\"') || '"'
   || ',"' || replace(replace(title , '\', '\\'), '"', '\"') || '"'
   || ',' || pages
      as csv
from qz_books
order by id;

(C)
select
   id
   || ',"' || replace(replace(author, '"', '\"'), '\', '\\') || '"'
   || ',"' || replace(replace(title , '"', '\"'), '\', '\\') || '"'
   || ',' || pages
      as csv
from qz_books
order by id;

(D)
select
   ltrim(
      rtrim(
         json_arrayagg(id, author, title, pages)
       , ']'
      )
    , '['
   )
      as csv
from qz_books
order by id;

(E)
select
   ltrim(
      rtrim(
         json_array(id, author, title, pages)
       , ']'
      )
    , '['
   )
      as csv
from qz_books
order by id;

(F)
select
   ltrim(
      rtrim(
         json_array(
            id
          , '"'||replace(replace(author, '\', '\\'), '"', '\"')||'"'
          , '"'||replace(replace(title , '\', '\\'), '"', '\"')||'"'
          , pages
         )
       , ']'
      )
    , '['
   )
      as csv
from qz_books
order by id;

论坛徽章:
533
生肖徽章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
发表于 2019-12-3 11:24 | 显示全部楼层

答案:BCE
A: 直接拼接,没有加引号,没有出来转义字符
B: 对双引号和反斜杠作了替换,字符串加了双引号
C: 与B不同的只是替换顺序改变了
D: json_arrayagg 是一个聚合函数,将多个行的字段值聚合为一个JSON文档
select json_arrayagg(id) from qz_books;

JSON_ARRAYAGG(ID)
--------------------------------------------------------------------------------
[123,124,125,126]

E: json_array,相对于D,这个就用对了,将一个或多个列组成JSON文档输出,再左右TRIM掉JSON文档的外围括号
F: 将要处理的内容 加引号,转义字符,搞了两遍

使用道具 举报

回复
论坛徽章:
526
奥运会纪念徽章:垒球
日期: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
 楼主| 发表于 2019-12-4 04:46 | 显示全部楼层
答案BE, 本期无人得奖。

A: 我们只是得到了逗号隔开的值,但是没有双引号,也没有转义。我们只会得到这个错误输出:

CSV
----------------------------------------------------------------------
123,Patrick O'Malley,Danny, Oh Danny,242
124,Janice E. Whalton,Girl Named "Bill",93
125,Petra Malthof, Dr.,Streets of Berlin,717
126,Bob "Bomber" Duffy,Bomber \VS\ Hulk,381

B:
此处我们记得在两个字符串放上双引号,并且用反斜杠对双引号和反斜杠做了转义。这会给我们正确的输出。

C:
和前一选项相比,我们对调了两个REPLACE调用,所以我们线对双引号进行转义,然后在对反斜杠进行转义。这就意味着第一次REPLACE的结果中的反斜杠被错误地转义了。我们会得到这个错误输出:

CSV
----------------------------------------------------------------------
123,"Patrick O'Malley","Danny, Oh Danny",242
124,"Janice E. Whalton","Girl Named \\"Bill\\"",93
125,"Petra Malthof, Dr.","Streets of Berlin",717
126,"Bob \\"Bomber\\" Duffy","Bomber \\VS\\ Hulk",381

D:
JSON_ARRAYAGG 是一个聚合函数,它接受一个元素作为参数,来自每一行的那个元素会被聚合成一个JSON数组。我们不能传递四个参数,这是错误的语法。解析器期待在ID之后出现右括号而不是逗号,所以它会报错:
ORA-02000: missing ) keyword.

E:
但是 JSON_ARRAY 确实接受参数列表来创建一个JSON数组,每个参数包含在一个元素里。因为JSON数组是逗号隔开的值列表,两边带方括号,我们就可以简单地将方括号去掉来得到所需的输出,因为JSON_ARRAY会自动处理双引号和转义。

F: 既然JSON_ARRAY已经自动处理了双引号和转义,这个选项就会加上不必要的转义,得到这个错误输出:
CSV
----------------------------------------------------------------------
123,"\"Patrick O'Malley\"","\"Danny, Oh Danny\"",242
124,"\"Janice E. Whalton\"","\"Girl Named \\\"Bill\\\"\"",93
125,"\"Petra Malthof, Dr.\"","\"Streets of Berlin\"",717
126,"\"Bob \\\"Bomber\\\" Duffy\"","\"Bomber \\\\VS\\\\ Hulk\"",381

使用道具 举报

回复

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

本版积分规则 发表回复

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