【函数】wm_concat包的订制

 

1  BLOG文档结构图

 

 

2  前言部分

 

2.1  导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 利用系统包创建WM_CONCAT函数（重点）

② ORA-00904: "wm_concat":invalid identifier错误解决

③ 订制自己的WM_CONCAT函数

④ listagg分析函数的使用

⑤ ORA-01489: result of string concatenation is too long的错误解决

 

  Tips：

① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）有同步更新。

② 文章中用到的所有代码，相关软件，相关资料请前往小麦苗的云盘下载（http://blog.itpub.net/26736162/viewspace-1624453/）。

③ 若网页文章代码格式有错乱，推荐使用360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://blog.itpub.net/26736162/viewspace-1624453/，另外itpub格式显示有问题，也可以去博客园地址阅读。

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

[ZHLHRDB1:root]:/>lsvg -o

T_XLHRD_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

====》2097152*512/1024/1024/1024=1G

本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。

 

2.2  相关参考文章链接

行转列参考文章：http://blog.itpub.net/26736162/viewspace-1272538/

 

2.3  本文简介

WMSYS用户下的WM_CONCAT函数有很重要的用途，比如行转列（http://blog.itpub.net/26736162/viewspace-1272538/），但是该函数不稳定，在10G和11GR2上返回值不同，一个是字符串一个是CLOB，而且12C上已经摒弃了WM_CONCAT函数，但是我们很多程序员在程序中使用了该函数，若是系统升级就会导致程序出现错误，为了减轻程序员修改程序的工作量，只有创建这个WM_CONCAT函数来解决该问题。

一般情况下报错信息，ORA-00904: "wm_concat":invalid identifier，查询DBA_OBJECTS视图，也未发现wm_concat的相关信息。正常情况下查询，

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE 'WM_CONCAT%';

应如下所示：

 

解决办法有2种，一种是采用Oracle本身的脚本来创建WM_CONCAT函数，一种是采用自己创建的函数来完成这个功能。

3  用Oracle自带脚本重建WMSYS用户的WMSYS.WM_CONCAT函数

运行如下脚本卸载WMSYS：

@$ORACLE_HOME/rdbms/admin/owmuinst.plb

运行如下脚本执行安装WMSYS：

@$ORACLE_HOME/rdbms/admin/owminst.plb

 

解锁wmsys用户：

ALTER USER WMSYS ACCOUNT UNLOCK;

 

4  自己创建wmsys

4.1  订制脚本

若只是某个用户使用，那么我们可以不用刻意去创建wmsys用户，可以在当前用户下运行脚本，生成WM_CONCAT函数，为了和系统的函数名区别开来，我们也可以修改函数名称，订制自己的脚本。

4.1.1  无分隔符，返回CLOB

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_NULL_LHR AUTHID CURRENT_USER AS OBJECT

(

  CURR_STR CLOB,

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                       P1   IN CLOB) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                         RETURNVALUE OUT CLOB,

                                         FLAGS       IN NUMBER)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)

    RETURN NUMBER

);

/

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_NULL_LHR IS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR)

    RETURN NUMBER IS

  BEGIN

    SCTX := WM_CONCAT_IMPL_CLOB_NULL_LHR(NULL);

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                       P1   IN CLOB) RETURN NUMBER IS

  BEGIN

    IF (CURR_STR IS NOT NULL) THEN

      CURR_STR := CURR_STR ||  P1;

    ELSE

      CURR_STR := P1;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                         RETURNVALUE OUT CLOB,

                                         FLAGS       IN NUMBER) RETURN NUMBER IS

  BEGIN

    RETURNVALUE := CURR_STR;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_NULL_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_NULL_LHR)

    RETURN NUMBER IS

  BEGIN

    IF (SCTX2.CURR_STR IS NOT NULL) THEN

      SELF.CURR_STR := SELF.CURR_STR ||  SCTX2.CURR_STR;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

END;

/

 

CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_NULL_LHR(P1 VARCHAR2) RETURN CLOB

  AGGREGATE USING WM_CONCAT_IMPL_CLOB_NULL_LHR;

/

 

CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_NULL_LHR FOR WM_CONCAT_CLOB_NULL_LHR;

GRANT EXECUTE ON WM_CONCAT_CLOB_NULL_LHR TO PUBLIC;

 

 

 

测试案例，注意函数的返回值是无分隔符的CLOB，在PL/SQL中注意使用to_char进行转换：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

   USER_ID

----------

         0

         5

 

SYS@lhrdb21> SELECT WM_CONCAT_CLOB_NULL_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

WM_CONCAT_CLOB_LHR_NULL(D.USER_ID)

--------------------------------------------------------------------------------

05

 

SYS@lhrdb21>

 

4.1.2  逗号分隔符，返回CLOB

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_CLOB_LHR AUTHID CURRENT_USER AS OBJECT

(

  CURR_STR CLOB,

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,

                                       P1   IN CLOB) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,

                                         RETURNVALUE OUT CLOB,

                                         FLAGS       IN NUMBER)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)

    RETURN NUMBER

);

/

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_CLOB_LHR IS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_CLOB_LHR)

    RETURN NUMBER IS

  BEGIN

    SCTX := WM_CONCAT_IMPL_CLOB_LHR(NULL);

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_CLOB_LHR,

                                       P1   IN CLOB) RETURN NUMBER IS

  BEGIN

    IF (CURR_STR IS NOT NULL) THEN

      CURR_STR := CURR_STR || ',' || P1;

    ELSE

      CURR_STR := P1;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_CLOB_LHR,

                                         RETURNVALUE OUT CLOB,

                                         FLAGS       IN NUMBER) RETURN NUMBER IS

  BEGIN

    RETURNVALUE := CURR_STR;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_CLOB_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_CLOB_LHR)

    RETURN NUMBER IS

  BEGIN

    IF (SCTX2.CURR_STR IS NOT NULL) THEN

      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

END;

/

 

CREATE OR REPLACE FUNCTION WM_CONCAT_CLOB_LHR(P1 VARCHAR2) RETURN CLOB

  AGGREGATE USING WM_CONCAT_IMPL_CLOB_LHR;

/

 

CREATE PUBLIC SYNONYM WM_CONCAT_CLOB_LHR FOR WM_CONCAT_CLOB_LHR;

GRANT EXECUTE ON WM_CONCAT_CLOB_LHR TO PUBLIC;

 

 

测试案例，注意函数的返回值是以逗号为分隔符的CLOB，在PL/SQL中注意使用to_char进行转换：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

   USER_ID

----------

         0

         5

SYS@lhrdb21> SELECT WM_CONCAT_CLOB_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

WM_CONCAT_LHR(D.USER_ID)

--------------------------------------------------------------------------------

0,5

 

4.1.3  逗号分隔符，返回字符串

创建函数的脚本如下：

CREATE OR REPLACE TYPE WM_CONCAT_IMPL_STRINGS_LHR AUTHID CURRENT_USER AS OBJECT

(

  CURR_STR VARCHAR2(32767),

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,

                                       P1   IN VARCHAR2) RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,

                                         RETURNVALUE OUT VARCHAR2,

                                         FLAGS       IN NUMBER)

    RETURN NUMBER,

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)

    RETURN NUMBER

);

/

CREATE OR REPLACE TYPE BODY WM_CONCAT_IMPL_STRINGS_LHR IS

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT WM_CONCAT_IMPL_STRINGS_LHR)

    RETURN NUMBER IS

  BEGIN

    SCTX := WM_CONCAT_IMPL_STRINGS_LHR(NULL);

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT WM_CONCAT_IMPL_STRINGS_LHR,

                                       P1   IN VARCHAR2) RETURN NUMBER IS

  BEGIN

    IF (CURR_STR IS NOT NULL) THEN

      CURR_STR := CURR_STR || ',' || P1;

    ELSE

      CURR_STR := P1;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN WM_CONCAT_IMPL_STRINGS_LHR,

                                         RETURNVALUE OUT VARCHAR2,

                                         FLAGS       IN NUMBER) RETURN NUMBER IS

  BEGIN

    RETURNVALUE := CURR_STR;

    RETURN ODCICONST.SUCCESS;

  END;

  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF  IN OUT WM_CONCAT_IMPL_STRINGS_LHR,

                                     SCTX2 IN WM_CONCAT_IMPL_STRINGS_LHR)

    RETURN NUMBER IS

  BEGIN

    IF (SCTX2.CURR_STR IS NOT NULL) THEN

      SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR;

    END IF;

    RETURN ODCICONST.SUCCESS;

  END;

END;

/

 

CREATE OR REPLACE FUNCTION WM_CONCAT_STRINGS_LHR(P1 VARCHAR2) RETURN VARCHAR2

  AGGREGATE USING WM_CONCAT_IMPL_STRINGS_LHR;

/

 

CREATE PUBLIC SYNONYM WM_CONCAT_STRINGS_LHR FOR WM_CONCAT_STRINGS_LHR;

GRANT EXECUTE ON WM_CONCAT_STRINGS_LHR TO PUBLIC;

 

测试案例，注意函数的返回值是以逗号为分隔符的字符串：

SYS@lhrdb21> SELECT D.USER_ID FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

   USER_ID

----------

         0

         5

 

SYS@lhrdb21> SELECT WM_CONCAT_STRINGS_LHR(D.USER_ID) FROM DBA_USERS D WHERE D.USER_ID IN (0, 5);

 

WM_CONCAT_STRINGS_LHR(D.USER_ID)

---------------------------------------------------

0,5

5  listagg的使用

这是一个Oracle的列转行函数：LISTAGG()

with temp as( 

  select 'China' nation ,'Guangzhou' city from dual union all 

  select 'China' nation ,'Shanghai' city from dual union all 

  select 'China' nation ,'Beijing' city from dual union all 

  select 'USA' nation ,'New York' city from dual union all 

  select 'USA' nation ,'Bostom' city from dual union all 

  select 'USA' nation ,'Bostom' city from dual union all 

  select 'Japan' nation ,'Tokyo' city from dual  

) 

select nation,listagg(city,',') within GROUP (order by city) 

from temp 

group by nation;

 

但是如果聚合的内容太多就会报ORA-01489: result of string concatenation is too long的错误，这个时候可以从业务的角度去修改SQL，也可以使用WM_CONCAT函数返回CLOB类型来解决这个问题。如下聚合DBA_OBJECTS中的OBJECT_NAME就会报错：

SELECT LISTAGG(OBJECT_NAME, ',') WITHIN GROUP(ORDER BY OBJECT_NAME)

  FROM DBA_OBJECTS D;

报错：ORA-01489: result of string concatenation is too long

解决：可以用WM_CONCAT返回CLOB类型即可。SELECT WM_CONCAT_CLOB_LHR(D.OBJECT_NAME) FROM DBA_OBJECTS D;

 

注意：有关WM_CONCAT函数返回CLOB类型的性能问题，我们本篇文章不讨论，聚合的内容多了，自然就慢，到底是避免出ORA-01489错误还是要结果，这个还得根据自己的情况权衡决定，比如有的系统tmp很大，随便用，那作为开发人员，估计才不会考虑这么多的，不管白猫黑猫，抓住老鼠就是好猫。


©著作权归作者所有：来自51CTO博客作者小麦苗DB宝的原创作品，如需转载，请注明出处，否则将追究法律责任

更多相关文章

  1. ubuntu下安装交叉编译的环境脚本
  2. Take Command 脚本语言基础
  3. C语言基础知识点有哪些（最全大总结）
  4. 用两张图，详解FreeModbus在单片机串口上的数据收发过程-FreeModbu
  5. makefile使用.lds链接脚本以及 常用命令 解析
  6. Oracle行列互换总结
  7. Oracle结果集缓存（Result Cache）--服务器、客户端、函数缓存
  8. MySQL分库备份与分表备份
  9. 使用Composition API在Vue3中创建防抖搜索输入框

随机推荐

  1. 以编程方式编写CSS背景的最佳方法？
  2. 安装nginx并配置php环境
  3. 在Yii中获取当前控制器和操作ID
  4. 基于PHP聊天室的编程思想
  5. 检查PHP是否启用了JavaScript
  6. php计算几分钟前、几小时前、几天前的几
  7. jQuery ajax调用不会调用我的php页面
  8. 复制到剪贴板没有瑞士法郎。只使用javasc
  9. 使用codeigniter发布到wordpress.com博客
  10. 用简单的馅饼从原子进料中拉出