oracle 替换wmsys.wm_concat的函数

来源：互联网发布：vissim交通仿真软件编辑：程序博客网时间：2024/06/06 16:42

本文转自：http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

命令窗口下执行：

CREATE OR REPLACE TYPE t_string_agg AS OBJECT(  g_string  VARCHAR2(32767),  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)    RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,                                       value  IN      VARCHAR2 )     RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,                                         returnValue  OUT  VARCHAR2,                                         flags        IN   NUMBER)    RETURN NUMBER,  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,                                     ctx2  IN      t_string_agg)    RETURN NUMBER);/SHOW ERRORSCREATE OR REPLACE TYPE BODY t_string_agg IS  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)    RETURN NUMBER IS  BEGIN    sctx := t_string_agg(NULL);    RETURN ODCIConst.Success;  END;  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,                                       value  IN      VARCHAR2 )    RETURN NUMBER IS  BEGIN    SELF.g_string := self.g_string || ',' || value;    RETURN ODCIConst.Success;  END;  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,                                         returnValue  OUT  VARCHAR2,                                         flags        IN   NUMBER)    RETURN NUMBER IS  BEGIN    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');    RETURN ODCIConst.Success;  END;  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,                                     ctx2  IN      t_string_agg)    RETURN NUMBER IS  BEGIN    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;    RETURN ODCIConst.Success;  END;END;/SHOW ERRORSCREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)RETURN VARCHAR2PARALLEL_ENABLE AGGREGATE USING t_string_agg;/SHOW ERRORS

调用示例：

select string_agg(p.account_id) from account_manage p;

执行结果：

10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,28,29,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,56,55,57

0 0