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

 找回密码
 注册
查看: 4635|回复: 6

[每日一题] PL/SQL Challenge 每日一题:2017-3-27 文本解析

[复制链接]
论坛徽章:
479
状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
发表于 2017-4-12 04:55 | 显示全部楼层 |阅读模式
最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。

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

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

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

作者:        Kim Berg Hansen

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

注:本题要求执行环境为12.1.0.2.0或更高

我有一个基于浏览器的应用,他使用了MOD_PLSQL,这意味着一个诸如此类的URL:
http://myhost.com/myproc?parm1=1&parm2=abc
会被解读为数据库中的一个过程调用:

begin
   myproc(parm1 => 1, parm2 => 'abc');
end;
/

过程会输出HTML然后被送到浏览器。

为了调试我的过程,我想要得到URL中的查询字串部分(就是?字符后面的名称/值对name/value pair),生成我的过程调用可以使用的参数列表。

谓词我写了这个不完整的查询,我可以简单地将查询字符串粘贴进来:

with query as (
   select
      'p_num1=42&p_num2=&p_num3=4.2&p_txt1=Four2&p_txt2=4Two&p_txt3='
         as string
     from dual
)
##REPLACE##
select name || ' => '
       || case
             when value is null then 'null'
             when regexp_like(value, '^((\d|\.)+)$') then value
             else '''' || value || ''''
          end
       || case
             when pair_number < max(pair_number) over () then ','
          end
       as output
  from pairs
order by pair_number
/

每个选项包含了一个或多个子查询,其最后一个名为pairs。

那些选项可用来取代##REPLACE##使得查询返回这个所需的输出:

OUTPUT
------------------------------------------------------------
p_num1 => 42,
p_num2 => null,
p_num3 => 4.2,
p_txt1 => 'Four2',
p_txt2 => '4Two',
p_txt3 => null

(A)
, pairs as (
   select substr(pair, 1, instr(pair, '=') - 1) as name
        , substr(pair, instr(pair, '=') + 1) as value
        , pair_number
     from (
      select regexp_substr(
                query.string
              , '(.*?)(&|$)'
              , 1
              , level
              , null
              , 1
             ) as pair
           , level as pair_number
        from query
      connect by level <= regexp_count(query.string, '&') + 1
   )
)

(B)
, pairs as (
   select substr(pair, 1, instr(pair, '=') - 1) as name
        , substr(pair, instr(pair, '=') + 1) as value
        , pair_number
     from (
      select substr(
                qs
              , case
                   when level = 1 then 0
                   else instr(qs, '&', 1, level - 1)
                end + 1
              , case
                   when level = no_of_pairs then length(qs) + 1
                   else instr(qs, '&', 1, level)
                end -
                case
                   when level = 1 then 0
                   else instr(qs, '&', 1, level - 1)
                end - 1
             ) as pair
           , level as pair_number
        from (
         select query.string as qs
              , length(string) - nvl(length(replace(string, '&')), 0)
                               + 1  as no_of_pairs
           from query
      )
      connect by level <= no_of_pairs
   )
)


(C)
, items as
  ( select level as pair_number
         , regexp_substr(string, '[^&]+', 1, level) as item
      from query
    connect by regexp_substr(string, '[^&]+', 1, level) is not null
  )
, pairs as
  ( select pair_number
         , regexp_substr(item, '^[^=]+') as name
         , regexp_substr(item, '[^=]*$') as value
      from items
  )

(D)
, pairs as (
   select j.*
     from query
        , json_table(
             '[{"n":"' ||
             replace(
                replace(
                   query.string
                 , '&', '"},{"n":"'
                )
              , '=', '","v":"'
             ) || '"}]'
           , '$
  • ' columns (
                    pair_number for ordinality
                  , name        varchar2(100) path '$.n'
                  , value       varchar2(100) path '$.v'
                 )
              ) j
    )

    (E)
    , pairs as (
       select x.*
         from query
            , xmltable(
                '/r/p'
                passing xmltype(
                          '<r><p><n>'
                        ||replace(replace(string, '=', '</n><v>'),
                                                  '&', '</v></p><p><n>')
                        ||'</v></p></r>'
                        )
                columns
                    pair_number for ordinality
                  , name        varchar2(100) path 'n'
                  , value       varchar2(100) path 'v'
              ) x
    )

    (F)
    , parser(pair_number, name, value, next_from, next_eq, next_to) as (
       select 0 as pair_number
            , cast(null as varchar2(100)) as name
            , cast(null as varchar2(100)) as value
            , 0 as next_from
            , instr(string, '=') as next_eq
            , case instr(string, '&')
                 when 0 then length(string) + 1
                        else instr(string, '&')
              end as next_to
         from query
        union all
       select pair_number + 1 as pair_number
            , substr(string, next_from + 1, next_eq - next_from - 1) as name
            , substr(string, next_eq + 1, next_to - next_eq - 1) as value
            , next_to as next_from
            , instr(string, '=', next_to + 1) as next_eq
            , case instr(string, '&', next_to + 1)
                 when 0 then length(string) + 1
                        else instr(string, '&', next_to + 1)
              end as next_to
         from parser cross join query
        where parser.next_eq > 0
    )
       cycle next_eq set is_cycle to 'Y' default 'N'
    , pairs as (
       select pair_number, name, value
         from parser
        where pair_number > 0
    )

    (G)
    , equals(eq_pos) as (
       select instr(string, '=') as eq_pos
         from query
        union all
       select instr(string, '=', eq_pos + 1) as eq_pos
         from equals cross join query
        where equals.eq_pos > 0
    )
       cycle eq_pos set is_cycle to 'Y' default 'N'
    , positions as (
       select instr(string, '&', eq_pos - length(string) - 1) as from_pos
            , eq_pos
            , case instr(string, '&', eq_pos)
                 when 0 then length(string) + 1
                        else instr(string, '&', eq_pos)
              end as to_pos
         from equals cross join query
        where equals.eq_pos > 0
    )
    , pairs as (
       select row_number() over (order by eq_pos) as pair_number
            , substr(string, from_pos + 1, eq_pos - from_pos - 1) as name
            , substr(string, eq_pos + 1, to_pos - eq_pos - 1) as value
         from positions cross join query
    )
  • 论坛徽章:
    395
    阿斯顿马丁
日期:2014-01-03 13:53:52马上有对象
日期: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喜羊羊
日期:2015-02-20 22:26:07懒羊羊
日期:2015-02-21 22:03:31
    发表于 2017-4-12 16:16 | 显示全部楼层
    这个代码很实用,11.2不知道能否运行

    使用道具 举报

    回复
    论坛徽章:
    479
    状元
日期:2015-09-09 10:34:21秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12秀才
日期:2015-11-23 10:03:12状元
日期:2015-11-23 10:04:09
     楼主| 发表于 2017-4-13 04:40 | 显示全部楼层
    答案ABCDEFG(全对), 本期无人得奖。

    A:CONNECT BY生成了和 名称/值对 一样多的行。REGEXP_SUBSTR取出了每个"name=value"子串。然后普通的SUBSTR把这两个分成NAME和VALUE
    B:基本上和前面一样,只是不用规则表达式函数。如果你有一个很老的数据库比如版本7,这个方法仍然可用。
    C: A选项是基于规则表达式。这个方法也是基于规则表达式,只是把所有工作都在REGEXP_SUBSTR 中完成而不是混合了REGEXP_COUNT 和SUBSTR。逻辑是清晰可理解的,但是如同往常一样,你必须用大量规则表达式进行测试,它们可能很耗CPU。好处是这个方法从10G开始就可以用。
    D: 通过几个REPLACE调用,URL风格的字符串就被变成了JSON字符串,然后用JSON_TABLE就可以轻易解析成名称和值。
    E: 前一选项把字符串变成JSON并且用 JSON_TABLE解析,这是 12.1.0.2.0 开始有的功能。同样的逻辑也可以通过变成XML,然后用XMLTABLE,这从10G开始就可以用。
    F: 此处我们用了递归分解子查询,对名称和值的分隔符 (& 以及 =)进行递归的搜索,然后解析出来。为了不在UNION ALL之前之后重复同样的逻辑,我们在UNION ALL之前选择了一个“基础”行。然后我们在PAIRS中把这个“基础”行过滤掉。
    G: 另外一种递归子查询的方法,仅仅用递归来查找 "=" 字符的位置。下一个子查询POSITIONS通过"=" 字符的位置向后和向前搜索来查出名称和值的FROM和TO位置。最后PAIRS子查询用了SUBSTR和前面计算出的位置来解析。

    使用道具 举报

    回复
    论坛徽章:
    263
    乌索普
日期:2016-07-29 01:46:29射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25
    发表于 2017-4-13 21:47 | 显示全部楼层
    真是一个好题!

    使用道具 举报

    回复
    招聘 : 系统分析师
    论坛徽章:
    477
    本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29
    发表于 2017-4-14 17:40 | 显示全部楼层
    没xml的写法

    使用道具 举报

    回复
    论坛徽章:
    263
    乌索普
日期:2016-07-29 01:46:29射手座
日期:2016-05-26 14:02:50双子座
日期:2016-05-25 16:05:44白羊座
日期:2016-05-23 11:49:19双鱼座
日期:2016-04-29 17:13:05秀才
日期:2016-04-29 15:03:39秀才
日期:2016-04-29 15:04:10技术图书徽章
日期:2016-04-29 15:04:10秀才
日期:2016-03-28 10:21:13巨蟹座
日期:2016-03-26 21:14:25
    发表于 2017-4-14 18:55 | 显示全部楼层

    E 就是啊

    使用道具 举报

    回复
    招聘 : 系统分析师
    论坛徽章:
    477
    本田
日期:2014-01-05 16:51:44技术图书徽章
日期:2014-04-21 10:26:402014年世界杯参赛球队: 伊朗
日期:2014-05-23 10:41:312014年世界杯参赛球队: 比利时
日期:2014-06-17 12:09:43itpub13周年纪念徽章
日期:2014-09-28 10:55:55itpub13周年纪念徽章
日期:2014-09-29 01:14:14itpub13周年纪念徽章
日期:2014-10-08 15:15:25itpub13周年纪念徽章
日期:2014-10-08 15:15:25马上有对象
日期:2014-10-12 11:58:40马上有车
日期:2014-11-16 17:11:29
    发表于 2017-4-15 22:22 | 显示全部楼层

    翻太快了没注意到

    使用道具 举报

    回复

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

    本版积分规则

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