-- 定义可变数组，字符串类型，长度32767，存放列值

CREATE OR REPLACE TYPE WYARRAY as TABLE OF VARCHAR(32767) ;

-- 定义分隔符函数

create or replace function delimiter(colValue  in varchar2,

                                     delimiter in varchar2) return varchar2 is

  rtnValue varchar2(32767);

begin

  rtnValue := colValue || ' delimiter=>' || delimiter || '; ';

  return rtnValue;

end delimiter;

-- 定义排序函数

create or replace function orderby(colValue in varchar2,

                                   orderby  in varchar2) return varchar2 is

  rtnValue varchar2(32767);

begin

  rtnValue := colValue || ' orderby=>' || LOWER(orderby) || '; ';

  return rtnValue;

end orderby;

-- 使用当前用户权限(使用authid current_user，定义type为用户当前用户的权限，举个例子：比如A用户他可以建立表，但是A用户在存储过程中如果建立表可能会提示权限不够，所以需要用authid current_user进行约束)

create or replace type wy_wm_concat authid current_user as object

(

--拼接字符串,存放中间值，当然也可以定义为clob，clob会使用临时段，导致临时表空间迅速增大;

--查看wmsys下的function可以发现Oracle10g到oracle11g自带的wm_concat函数的返回类型从clob变成varchar2

  currStr VARCHAR2(32767),

--分割字符串

  delimiter VARCHAR2(64),

--排序字符串(asc、desc)

  orderby VARCHAR2(64),

-- 定义字符串数组

  strArray WYARRAY,

-- 初始化接口函数

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)

    RETURN NUMBER,

-- 迭代接口函数

  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,

                                       colValue IN VARCHAR2) RETURN NUMBER,

-- 并行时字符串合并的接口函数

  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,

                                     next wy_wm_concat) RETURN NUMBER,

-- oracle终止接口函数

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,

                                         returnValue OUT VARCHAR2,

                                         flags       IN NUMBER)

    RETURN NUMBER

)

create or replace type body wy_wm_concat is  --定义函数的body

  --初始化函数

  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(init IN OUT wy_wm_concat)

    RETURN NUMBER is

  begin

    init := wy_wm_concat('', ',', 'asc', WYARRAY());

    return ODCICONST.Success;

  END;

  -- 字符串拼接，self 为当前聚集函数的指针,用来与前面的计算结果进行关联

  MEMBER FUNCTION ODCIAGGREGATEITERATE(self     IN OUT wy_wm_concat,

                                       colValue IN VARCHAR2) RETURN NUMBER is

    tempStr varchar(500);

    extendStr varchar(500);

    deStr varchar(100);

    deLen int default 0;

    segStr varchar(500);

    --定义一个二维数组

    TYPE varArry IS VARRAY(2) OF VARCHAR2(200);

    tempArry varArry := varArry('', '');

  begin

    if instr(colValue, ' ', 1) > 0 then

      tempStr := substr(colValue, 1, instr(colValue, ' ', 1) - 1);

    else

      tempStr := colValue;

    end if;

    --排序和分隔符

    extendStr := REPLACE(colValue, tempStr || ' ');

    if instr(extendStr, ' ', 1) > 0 then

      tempArry(1) := substr(extendStr, 1, instr(extendStr, ' ', 1) - 1);

      tempArry(2) := substr(extendStr, instr(extendStr, ' ', 1));

      for i in 1 .. tempArry.count loop

        -- 获取分隔符

        if (tempArry(i) is not null) and

           (instr(tempArry(i), 'delimiter=>') > 0) THEN

          deStr := 'delimiter=>';

          deLen := length(deStr);

          segStr := substr(trim(tempArry(i)),

                           instr(trim(tempArry(i)), deStr) + deLen);

          self.delimiter := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1);

        END IF;

        -- 获取排序字符串

        if tempArry(i) is not null and

           (instr(tempArry(i), 'orderby=>') > 0) THEN

          deStr := 'orderby=>';

          deLen := length(deStr);

          segStr := substr(trim(tempArry(i)),

                           instr(trim(tempArry(i)), deStr) + deLen);

          self.orderby := SUBSTR(segStr, 1, instr(segStr, ';', -1) - 1);

        END IF;

      end loop;

    end if;

    -- 存放入数组

    self.strArray.extend;

    self.strArray(self.strArray.count) := tempStr;

    return ODCICONST.Success;

  END;

  --并行操作是用来合并两个聚集函数的两个不同的指针对应的结果

  MEMBER FUNCTION ODCIAGGREGATEMERGE(self IN OUT wy_wm_concat,

                                     next wy_wm_concat) RETURN NUMBER is

  begin

    -- 将next数组中元素全部放入self指针对应的数组中

    for i in 1 .. next.strArray.count loop

      self.strArray.extend;

      self.strArray(self.strArray.count) := next.strArray(i);

    end loop;

    return ODCICONST.Success;

  END;

  -- 终止函数，返回结果

  MEMBER FUNCTION ODCIAGGREGATETERMINATE(self        IN wy_wm_concat,

                                         returnValue OUT VARCHAR2,

                                         flags       IN NUMBER) RETURN NUMBER IS

    temp_rtnValue varchar2(32767);

  BEGIN

    -- 排序

    if INSTR(self.orderby, 'desc') > 0 THEN

      for x in (select column_value

                  from Table(self.strArray)

                 order by 1 DESC) loop

        temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;

      end loop;

    ELSE

      for x in (select column_value from Table(self.strArray) order by 1 ASC) loop

        temp_rtnValue := temp_rtnValue || self.delimiter || x.column_value;

      end loop;

    END IF;

    returnValue := ltrim(temp_rtnValue, self.delimiter);

    return ODCICONST.Success;

  END;

END;

-- 定义聚集函数(未开启并行计算功能)

create or replace function wy_concat(colValue  VARCHAR2) RETURN VARCHAR2

  AGGREGATE USING wy_wm_concat;