Oracle Function — wmsys.wm_concat 合并行记录函数

一、描述

       小需求又来了,现在需要将某表上的记录查询出来并且要求连接使用。通过查找后发现Oracle有个内部函数 wm_concat,这个函数在wmsys用户下。使用时那是当然的容易。一般人我都不告诉他(开个小玩笑)。
二、实验
1.创建实验表

  1. SAM@orcl > create table t1 (id int, name varchar2(10));
  2. Table created.
  3. SAM@orcl > insert into t1 values (1,‘sam1′);
  4. 1 row created.
  5. SAM@orcl > insert into t1 values (2,‘sam2′);
  6. 1 row created.
  7. SAM@orcl > insert into t1 values (3,‘sam3′);
  8. 1 row created.
  9. SAM@orcl > insert into t1 values (4,‘sam4′);
  10. 1 row created.
  11. SAM@orcl > insert into t1 values (5,‘sam5′);
  12. 1 row created.
  13. SAM@orcl > commit;
  14. Commit complete.
  15. SAM@orcl > select * from t1;
  16.         ID NAME
  17. ———- ———-
  18.          1 sam1
  19.          2 sam2
  20.          3 sam3
  21.          4 sam4
  22.          5 sam5

2.使用wm_concat函数查询,并得到想要的结果

  1. SAM@orcl > select wm_concat(id),wm_concat(name) from t1;
  2. WM_CONCAT(ID)
  3. ——————————————————————————–
  4. WM_CONCAT(NAME)
  5. ——————————————————————————–
  6. 1,2,3,4,5
  7. sam1,sam2,sam3,sam4,sam5

3.到此处问题已被解决,但更加深入了解一下,我打算建立一个t2表,将查询出来的数据插入到t2表中,得到的结果却报错,数据类型不匹配。

  1. SAM@orcl > create table t2 (sum_id int,sum_name varchar2(100));
  2. Table created.
  3. SAM@orcl > insert into t2 select wm_concat(id),wm_concat(name) from t1;
  4. insert into t2 select wm_concat(id),wm_concat(name) from t1
  5.                        *
  6. ERROR at line 1:
  7. ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

4.再次验证,看数据类型到底变为什么?将查出结果直接放在新建表t3中,成功。

  1. SAM@orcl > create table t3 as select wm_concat(id) as sum_id,wm_concat(name) as sum_name from t1;
  2. Table created.

5.查看新表t3结构,数据类型发生变化,由源来的int, varchar2变为了clob

  1. SAM@orcl > desc t3;
  2.  Name Null? Type
  3.  —————————————– ——– —————————-
  4.  SUM_ID CLOB
  5.  SUM_NAME CLOB

6.查询t3表内容

  1. SAM@orcl > select * from t3;
  2. SUM_ID
  3. ——————————————————————————–
  4. SUM_NAME
  5. ——————————————————————————–
  6. 1,2,3,4,5
  7. sam1,sam2,sam3,sam4,sam5
四、扩展知识
1.此时开发人员认为这个函数果然厉害,就说能不能看一下这个函数如何写的,与此同时也想到我的心里去了,看看Oracle开发人员的思路。说不定能大开眼界。采用dbms_matadata.get_ddl来取function脚本。结果发现该Function已被加密,无法读取。加密方式为wrap加密。

  1. SAM@orcl > set long 99999 pages 1000
  2. SAM@orcl > SELECT SYS.DBMS_METADATA.get_ddl (‘FUNCTION’,‘WM_CONCAT’,WMSYS’) FROM DUAL;
  3. SYS.DBMS_METADATA.GET_DDL(‘FUNCTION’,‘WM_CONCAT’,‘WMSYS’)
  4. ——————————————————————————–
  5.   CREATE OR REPLACE FUNCTION “WMSYS”.“WM_CONCAT” wrapped
  6. a000000
  7. 1
  8. abcd
  9. abcd
  10. abcd
  11. abcd
  12. abcd
  13. abcd
  14. abcd
  15. abcd
  16. abcd
  17. abcd
  18. abcd
  19. abcd
  20. abcd
  21. abcd
  22. abcd
  23. 8
  24. 53 96
  25. antgYqrbNGLSC7Re+71hueZFyT4wg0SvLZ6pyi+mUCJD1KOso
  26. xPiallQXtwu7BTsCmx9/hIg
  27. +ln6MEC75cHHT8YFQPvfjqPM1MuiY1Z0kXN0TQ0W8KE1SkAqjh/+tB/q
  28. +oI45dREmV5OHaYy
  29. H/E=
五、总结
       有时候通过小小的知识点就可以帮助我们DBA和开发人员解决很大的问题,所以此时我想到的就是知识是无限的海洋,等待着我们不停的探索与学习。再有就是我认为古人:三人行,必有我师。那是相当的corrent and nice. 最后还是希望我能够不断的成长与进步。fighting! Where there is a will, there is a way. 

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » Oracle Function — wmsys.wm_concat 合并行记录函数

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址