ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库备份恢复高级培训视频(案例、实战、深入、全面)
Oracle数据库DBA高级工程师培训视频(集群容灾,核心深入,备份恢复)

巧用Oracle正则表达式解决查询问题

内容发布:清晨| 发布时间:2016-9-21 11:38:25
业务人员需要对一张表中的交易数据,按照月份,统计2015年6月到2016年4月分月的交易笔数和总的交易金额。需求很简单,也非常明确,sql实现也非常简单。考虑到涉及的表比较大,有好几十个G,因此查询时采用了并行处理:
select /*+ parallel(t 18) */
substr(t.XXX_DATE, 1, 6), sum(t.XXX_TRANS), count(*)
  from xxx_yyyy_txn_his t
where to_date(t.XXX_DATE, 'yyyymmdd') >= date
'2015-06-01't.XXX_TRANS
   and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'
group by substr(t.XXX_DATE, 1, 6);
    实际运行上述sql,但是在运行10多秒后,却出现了如下报错:

    报错很明确,ORA-01722表示有无效数字。看到该报错后,立即检查了表结构。出乎意料,交易金额字段XXX_TRANS确实使用的是VARCHAR2类型。至此,可以比较肯定的是,上述的sql运行时,sum(t.XXX_TRANS)时,oracle进行了隐式转换,将字符转为了数字进行数学运算。但是由于交易金额字段可能存在了非数字字符,因此隐式转换时报了ORA-01722错误。如果要使得上述sql能够正常运行,则必须找出有问题的记录。但是由于表中的记录数目非常多,如果逐一排查记录,找出有问题的记录,则需要耗费太多的时间。
    由于业务人员只需要大概的数字,不需要完全精确的结果,因此考虑使用case进行判断后,然后取值。但是由于该字段包含的字符不确定,所以使用普通的like判断无法实现需求。好在Oracle提供了正则表达式,可以对XXX_TRANS进行基于正则表达式的判断,然后再确定其值,即:如果此字段包含非数字字符(不包括小数点),则将结果记为0,否则使用原来的值。按照该想法,sql如下:
select /*+ parallel(t 18) */
substr(t.XXX_DATE, 1, 6),
sum(case
       when regexp_like(t.XXX_TRANS, '[^0-9\.]+') then
        '0'
       else
       t.XXX_TRANS
     end),
count(*)
  from xxx_yyyy_txn_his t
where to_date(t.XXX_DATE, 'yyyymmdd') >= date
'2015-06-01'
   and to_date(t.XXX_DATE, 'yyyymmdd') < date '2016-04-13'
group by substr(t.XXX_DATE, 1, 6) order by 1 asc
    其中红色部分即为正则表达式,各项解释如下:
1)[]表示一个集合,
2)^则表示对后面的字符集合取反,
3)0-9表示数字0到9
4)\.表示小数点
5)+则表示至少匹配一次。
    综合在一起,该正则表达式就是表示,如果不是数字或小数点的字符出现,则结果匹配,也就是case表达式为真,此时将XXX_TRANS的值记为0。再次运行该sql,获得查询结果。






上一篇:绑定执行计划案例
下一篇:ORACLE PL/SQL开发之游标(Cursor)使用
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表