Oracle OCP笔记(34)Oracle的特殊功能函数

Oracle有一些特殊功能函数，不太常用，但有时候很有用. 一.列转行 1.多笔数据分组横向显示(使用wmsys.wm_concat) with test as（ select 1 a,'XXX' b from dual union select 1 a,'YYY' b from dual union select 2 a,'ZZZ' b from dual ） select a,to_char(wmsys.wm_concat(b)) list_value from test group by a; 显示结果如下: LIST_VALUE -------------------------------------------------------------------------------- 1XXX1YYY 2ZZZ wmsys.wm_concat的返回值是CLOB. wmsys.wm_concat是一个内部使用的函数，Oracle随时可以取消此函数，Oracle 12已不包含此函数. 2.多笔数据分组横向显示(使用listagg) with test as（ select 1 a,'ZZZ' b from dual ） select listagg(a||b) within group(order by a) list_value from test group by a; 显示结果如下: LIST_VALUE -------------------------------------------------------------------------------- 1XXX1YYY 2ZZZ listagg的返回值是VARCHAR2，有长度限制，最长32767. 二.行转列 1.行转列(使用sys.odcivarchar2list) select column_value from table(sys.odcivarchar2list('1','2','3','4','5')); 显示结果如下: COLUMN_VALUE -------------------------------------------------------------------------------- 1 2 3 4 5 2.行转列（使用CONNECT和正则表达式） select regexp_substr('1,2,3,4,5','[^,]+',1,rownum) list_value from dual connect by rownum <= length('1,5') - length(replace('1,',')) +1; 显示结果如下: LIST_VALUE ------------------ 1 2 3 4 5 三.正则表达式替换 select regexp_replace('123456789','678','WXYZ',lower('I')) replace_value from dual; 显示结果如下: REPLACE_VALUE ------------- 12345WXYZ9 替换搜索字符串有单边括号等特殊字符会报错，需要用斜杠转义. select regexp_replace('123456789','678(','890',lower('I')) from dual; --报错 select regexp_replace('12345678(9','678\(','890(',lower('I')) from dual; --用斜杠转义后没有错误 需要转义的符号\/|().?*+[]{}^$等等.