wm_concat的源代码

最新推荐文章于 2023-11-14 15:31:30 发布

zcany

最新推荐文章于 2023-11-14 15:31:30 发布

阅读量496

点赞数

分类专栏： oracle 文章标签： oracle

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/qq_26826997/article/details/122988579

版权

oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在Oracle中创建一个名为wm_concat的自定义聚合函数，该函数用于将多个VARCHAR2类型的值合并成一个字符串，以逗号分隔。通过odciaggregateinitialize、odciaggregateiterate、odciaggregateterminate和odciaggregatemerge等ODCI方法实现，适用于数据聚合操作。

摘要由CSDN通过智能技术生成

CREATE OR REPLACE TYPE wm_concat_impl 
    AUTHID CURRENT_USER 
AS OBJECT (
    curr_str VARCHAR2 (32767), 
    STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl) 
     RETURN NUMBER, 
    MEMBER FUNCTION odciaggregateiterate (
     SELF IN OUT wm_concat_impl, 
     p1  IN  VARCHAR2 
    ) 
     RETURN NUMBER, 
    MEMBER FUNCTION odciaggregateterminate (
     SELF   IN  wm_concat_impl, 
     returnvalue OUT  VARCHAR2, 
     flags   IN  NUMBER 
    ) 
     RETURN NUMBER, 
    MEMBER FUNCTION odciaggregatemerge (
     SELF IN OUT wm_concat_impl, 
     sctx2 IN  wm_concat_impl 
    ) 
     RETURN NUMBER 
); 
/

CREATE OR REPLACE TYPE BODY wm_concat_impl 
IS 
    STATIC FUNCTION odciaggregateinitialize (sctx IN OUT wm_concat_impl) 
     RETURN NUMBER 
    IS 
    BEGIN 
     sctx := wm_concat_impl (NULL); 
     RETURN odciconst.success; 
    END; 
    MEMBER FUNCTION odciaggregateiterate (
     SELF IN OUT wm_concat_impl, 
     p1  IN  VARCHAR2 
    ) 
     RETURN NUMBER 
    IS 
    BEGIN 
     IF (curr_str IS NOT NULL) 
     THEN 
     curr_str := curr_str || ',' || p1; 
     ELSE 
     curr_str := p1; 
     END IF; 

     RETURN odciconst.success; 
    END; 
    MEMBER FUNCTION odciaggregateterminate (
     SELF   IN  wm_concat_impl, 
     returnvalue OUT  VARCHAR2, 
     flags   IN  NUMBER 
    ) 
     RETURN NUMBER 
    IS 
    BEGIN 
     returnvalue := curr_str; 
     RETURN odciconst.success; 
    END; 
    MEMBER FUNCTION odciaggregatemerge (
     SELF IN OUT wm_concat_impl, 
     sctx2 IN  wm_concat_impl 
    ) 
     RETURN NUMBER 
    IS 
    BEGIN 
     IF (sctx2.curr_str IS NOT NULL) 
     THEN 
     SELF.curr_str := SELF.curr_str || ',' || sctx2.curr_str; 
     END IF; 

     RETURN odciconst.success; 
    END; 
END; 
/

CREATE OR REPLACE FUNCTION wm_concat (p1 VARCHAR2) 
    RETURN VARCHAR2 
    AGGREGATE USING wm_concat_impl; 
/