转载

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. 




正文到此结束
Loading...