Oracle 聚合函数解决聚集连接字符串问题

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wzy0623/article/details/53906139

需求: 给定数据表:wm_test code name 1 a 1 b 1 c 2 中 2 国 2 人

需要的结果(分隔符可以由参数输入): code name 1 a,b,c 2 中,国,人

分析:

这个问题在可以使用Oracle的wmsys.wm_concat 函数解决: select wt.code, wm_concat(wt.name) names from wm_test wt group by wt.code;

但是这有三个问题: 1. wmsys.wm_concat 是10g才有的,以前的版本无法使用 2. wmsys.wm_concat 是ORACLE内部函数,没有对外公布,也就是说,你可以使用,但是如果发生什么问题ORACLE概不负责。最显然的是ORACLE版本从10.2.0.4升级到10.2.0.5,只是一个小版本的变更,足以让你的系统出现问题。

解决方案: 1. 升级到Oracle 11g Release 2,此版本引入了LISTAGG 函数,使得聚集连接字符串变得很容易,并且允许使用我们指定连接串中的字段顺序。 2. 用自己定义的聚合函数替换wmsys.wm_concat

代码如下:

[sql] view plain copy

  1. -- 1. 建立测试表和数据:
  2. CREATE TABLE WM_TEST
  3. (
  4. CODE INTEGER,
  5. NAME VARCHAR2(20 BYTE)
  6. );
  7. Insert into WM_TEST (CODE, NAME) Values (1, 'a');
  8. Insert into WM_TEST (CODE, NAME) Values (1, 'b');
  9. Insert into WM_TEST (CODE, NAME) Values (1, 'c');
  10. Insert into WM_TEST (CODE, NAME) Values (2, '中');
  11. Insert into WM_TEST (CODE, NAME) Values (2, '国');
  12. Insert into WM_TEST (CODE, NAME) Values (2, '人');
  13. COMMIT;
  14. -- 2. 建立自定义聚合函数
  15. CREATE OR REPLACE TYPE ConcatObj AS OBJECT
  16. (
  17. fieldValue VARCHAR2 (4000),
  18. separator VARCHAR2 (100)
  19. )
  20. /
  21. CREATE OR REPLACE TYPE type_wm_concat
  22. AS OBJECT
  23. (
  24. l_join_str VARCHAR2 (32767 BYTE), -- 连接后的字符串
  25. l_flag VARCHAR2 (100 BYTE), -- 分隔符,默认值可在body中定义
  26. STATIC FUNCTION ODCIAggregateInitialize -- 初始化
  27. (sctx IN OUT type_wm_concat)
  28. RETURN NUMBER,
  29. MEMBER FUNCTION ODCIAggregateIterate -- 迭代器,处理每行数据
  30. (self IN OUT type_wm_concat,
  31. VALUE IN ConcatObj)
  32. RETURN NUMBER,
  33. MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
  34. (self IN OUT type_wm_concat,
  35. return_v OUT VARCHAR2,
  36. flags IN NUMBER)
  37. RETURN NUMBER,
  38. MEMBER FUNCTION ODCIAggregateMerge -- 结果合并
  39. (self IN OUT type_wm_concat,
  40. ctx2 IN type_wm_concat)
  41. RETURN NUMBER
  42. );
  43. /
  44. CREATE OR REPLACE TYPE BODY type_wm_concat
  45. IS
  46. STATIC FUNCTION ODCIAggregateInitialize -- 初始化
  47. (sctx IN OUT type_wm_concat)
  48. RETURN NUMBER
  49. IS
  50. BEGIN
  51. sctx := type_wm_concat (NULL, NULL);
  52. RETURN ODCIConst.success;
  53. END ODCIAggregateInitialize;
  54. MEMBER FUNCTION ODCIAggregateIterate -- 迭代器,处理每行数据
  55. (self IN OUT type_wm_concat,
  56. VALUE IN ConcatObj)
  57. RETURN NUMBER
  58. IS
  59. BEGIN
  60. IF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULL
  61. THEN
  62. self.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;
  63. ELSIF VALUE.fieldValue IS NOT NULL
  64. THEN
  65. self.l_join_str := VALUE.fieldValue;
  66. self.l_flag := VALUE.separator;
  67. END IF;
  68. RETURN ODCIConst.Success;
  69. END;
  70. MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码
  71. (self IN OUT type_wm_concat,
  72. return_v OUT VARCHAR2,
  73. flags IN NUMBER)
  74. RETURN NUMBER
  75. IS
  76. BEGIN
  77. return_v := self.l_join_str;
  78. RETURN ODCIConst.Success;
  79. END;
  80. MEMBER FUNCTION ODCIAggregateMerge (self IN OUT type_wm_concat,
  81. ctx2 IN type_wm_concat)
  82. RETURN NUMBER
  83. IS
  84. BEGIN
  85. IF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULL
  86. THEN
  87. self.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;
  88. ELSIF ctx2.l_join_str IS NOT NULL
  89. THEN
  90. self.l_join_str := ctx2.l_join_str;
  91. END IF;
  92. RETURN ODCIConst.Success;
  93. END;
  94. END;
  95. /
  96. -- 3. 封装为一个普通的SQL函数:
  97. CREATE OR REPLACE FUNCTION my_wm_concat (pi_str ConcatObj)
  98. RETURN VARCHAR2
  99. PARALLEL_ENABLE
  100. AGGREGATE USING type_wm_concat;
  101. /
  102. -- 4. 测试:
  103. SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) names
  104. FROM wm_test wt
  105. GROUP BY wt.code;

code name 1 a|+=b|+=c 2 中|+=国|+=人

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券