pivot_ext_pkg-oracle
项目地址:
https://github.com/Dark-Athena/pivot_ext_pkg-oracle
背景
众所周知,oracle的原生pivot功能,无法在"for ... in ()" 的括号中使用子查询(不使用xml的情况下), 比如,如果执行
select *
from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME)
for COUNTRY_ID in (select distinct COUNTRY_ID from HR.COUNTRIES))
那么会收到报错 ORA-00936 ,必须将对应的值手动枚举出来,比如
select *
from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME)
for COUNTRY_ID in ('AR','AU','BE','BR','CA','CH','CN','DE','DK',
'EG','FR','IL','IN','IT','JP','KW','ML','MX','NG','NL',
'SG','UK','US','ZM','ZW'))
如果枚举值是动态变化的,那么sql将无法固定,这对数据报表的导出是个很麻烦的问题。一般稍微聪明一点的开发者,会选择在程序中使用动态sql来拼接sql,但很难做出通用的拼接程序,并且就算通用,也要传入很多参数,还必须先创建视图,再查询视图。
所以我写了这个pivot的增强包,你可以直接在pivot"for ... in ()"的括号中写子查询,以这个"错误的"sql作为参数,查询此增强包中的函数,即可直接获得数据结果的展现。
程序功能用例
例1:最简单的用法
SELECT pivot_ext_pkg.get_cursor(Q'{select * from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in(
select distinct COUNTRY_ID from HR.COUNTRIES ))}')
FROM DUAL;
例2:将一个oracle 无法执行的 "pivot... in(select )" sql,转换成Oracle可直接执行的sql
此功能方便开发者使用输出的sql发邮件、导出数据等
SELECT pivot_ext_pkg.convert_sql(Q'{select * from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in(
select distinct COUNTRY_ID from HR.COUNTRIES ))}') a
FROM DUAL;
例3:输出逗号分割的数据(CSV格式)
select *
from pivot_ext_pkg.GET_DATA(i_sql => Q'{select * from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in(
select distinct COUNTRY_ID from HR.COUNTRIES ))}',
format => 'CSV',
field_delimiter => ',',
skip_header => 'N');
例4:输出json格式数据
select *
from pivot_ext_pkg.GET_DATA(i_sql => Q'{select * from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in(
select distinct COUNTRY_ID from HR.COUNTRIES ))}',
format => 'JSON');
例5:输出xml格式数据
select *
from pivot_ext_pkg.GET_DATA(i_sql => Q'{select * from HR.COUNTRIES
pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in(
select distinct COUNTRY_ID from HR.COUNTRIES ))}',
format => 'XML');
其他
由于oracle的sql语法解析相当复杂,本功能只是对关键字' for '来对字符串进行识别,检索出对应的子查询并将子查询的结果替换掉原有的子查询sql,如果sql中有使用unpivot,或者存在和关键字一样的字符串常量,此功能会报错,以后会再看unpivot的for是否也可以进行类似的转换。除了上面提到的问题,如有用户测试报错,请联系作者或者在github上发issue
• 本文作者： DarkAthena[1]• 本文链接： https://www.darkathena.top/archives/oraclepivot-ext-pkg-20211225• 版权声明： 本博客所有文章除特别声明外，均采用CC BY-NC-SA 3.0[2] 许可协议。转载请注明出处！
References
[1]
DarkAthena: https://www.darkathena.top[2]
CC BY-NC-SA 3.0: https://creativecommons.org/licenses/by-nc-sa/3.0/
