需求:
给定数据表: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.建立测试表和数据:CREATE TABLE WM_TEST(CODE INTEGER, NAME VARCHAR2(20 BYTE));
- Insert into WM_TEST(CODE, NAME) Values(1, 'a');
- Insert into WM_TEST(CODE, NAME) Values(1, 'b');
- Insert into WM_TEST(CODE, NAME) Values(1, 'c');
- Insert into WM_TEST(CODE, NAME) Values(2, '中');
- Insert into WM_TEST(CODE, NAME) Values(2, '国');
- Insert into WM_TEST(CODE, NAME) Values(2, '人');
- COMMIT; --2.建立自定义聚合函数CREATE OR REPLACE TYPE ConcatObj AS OBJECT(fieldValue VARCHAR2(4000), separator VARCHAR2(100)) / CREATE OR REPLACE TYPE type_wm_concatAS OBJECT(l_join_str VARCHAR2(32767 BYTE), --连接后的字符串l_flag VARCHAR2(100 BYTE), --分隔符,默认值可在body中定义STATIC FUNCTION ODCIAggregateInitialize--初始化 (sctx IN OUT type_wm_concat) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate--迭代器,处理每行数据 (self IN OUT type_wm_concat, VALUE IN ConcatObj) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate--迭代结束后处理代码 (self IN OUT type_wm_concat, return_v OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge--结果合并 (self IN OUT type_wm_concat, ctx2 IN type_wm_concat) RETURN NUMBER);
- /CREATE OR REPLACE TYPE BODY type_wm_concatISSTATIC FUNCTION ODCIAggregateInitialize -- 初始化(sctx IN OUT type_wm_concat)RETURN NUMBERISBEGINsctx := type_wm_concat (NULL, NULL);RETURN ODCIConst.success;END ODCIAggregateInitialize;MEMBER FUNCTION ODCIAggregateIterate -- 迭代器,处理每行数据(self IN OUT type_wm_concat,VALUE IN ConcatObj)RETURN NUMBERISBEGINIF self.l_join_str IS NOT NULL AND VALUE.fieldValue IS NOT NULLTHENself.l_join_str := self.l_join_str || self.l_flag || VALUE.fieldValue;ELSIF VALUE.fieldValue IS NOT NULLTHENself.l_join_str := VALUE.fieldValue;self.l_flag := VALUE.separator;END IF;RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateTerminate -- 迭代结束后处理代码(self IN OUT type_wm_concat,return_v OUT VARCHAR2,flags IN NUMBER)RETURN NUMBERISBEGINreturn_v := self.l_join_str;RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateMerge (self IN OUT type_wm_concat,ctx2 IN type_wm_concat)RETURN NUMBERISBEGINIF ctx2.l_join_str IS NOT NULL AND self.l_join_str IS NOT NULLTHENself.l_join_str := self.l_join_str || self.l_flag || ctx2.l_join_str;ELSIF ctx2.l_join_str IS NOT NULLTHENself.l_join_str := ctx2.l_join_str;END IF;RETURN ODCIConst.Success;END;END;/--3.封装为一个普通的SQL函数:CREATE OR REPLACE FUNCTION my_wm_concat(pi_str ConcatObj) RETURN VARCHAR2PARALLEL_ENABLEAGGREGATE USING type_wm_concat;
- /-- 4. 测试:SELECT wt.code, my_wm_concat (ConcatObj (wt.name, '|+=')) namesFROM wm_test wtGROUP BY wt.code;code name1 a|+=b|+=c2 中|+=国|+=人/
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: