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

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, 'XXX' b from dual
      union
      select 1 a, 'YYY' b from dual
      union
      select 2 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,2,3,4,5') - length(replace('1,2,3,4,5',',')) +1;


    显示结果如下:
    LIST_VALUE
    ------------------
    1
    2
    3
    4
    5


三.正则表达式替换
    select regexp_replace('123456789', '678', 'WXYZ', 1 , 0, lower('I')) replace_value from dual;


    显示结果如下:
    REPLACE_VALUE
    -------------
    12345WXYZ9


    替换搜索字符串有单边括号等特殊字符会报错，需要用斜杠转义.
    select regexp_replace('123456789', '678(', '890', 1 , 0, lower('I')) from dual;    --报错
    select regexp_replace('12345678(9', '678\(', '890(', 1 , 0, lower('I')) from dual; --用斜杠转义后没有错误
    需要转义的符号\/|().?*+[]{}^$等等.