oracle clob分隔符,重写Oracle的wm_concat函数，自定义分隔符、排序

oracle中，wm_concat函数是一个聚合函数，和mysql中的group_concat函数类似，不过group_concat函数比较强大，可以定义分隔符和排序，当然所谓强大是相对的，这里假使我们不知道oracle中的over函数，也不知道listagg函数。

我们先来看看wm_concat函数能实现什么功能，通俗点==>列传行，如果不明白，请看下面截图(可以看到分隔符默认为','顺序也是杂乱的)

        

所以，接下来，我们开始重写wm_concat函数(需要注意和需要说明的地方放在代码注释中...)

(1) 因为需要进行排序，首先自定义一个可变数组

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

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

(2)自定义排序函数、分隔符函数

-- 定义分隔符函数

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;

(3) 重定义oracle接口函数、以及接口函数的实现体(实现分隔符和排序的主要代码)

-- 使用当前用户权限(使用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;

(4)自定义聚集函数

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

create or replace function wy_concat(colValue VARCHAR2) RETURN VARCHAR2

AGGREGATE USING wy_wm_concat;

至此，主要的代码已经全部奉上，看看运行效果，如下截图：

①看看调用的默认情况(分隔符默认是逗号，排序默认是升序，在初始化函数中如此定义的)

②自定义分隔符(利用分隔符函数将分隔符定义为*)

③降序排序

④去重，为了可以使用wm_concat自带的去重函数，所以在自定义分隔符和排序函数时，实质是实用了字符串处理(如果你觉得处理字符串麻烦，可以自定义 type... as object ，在使用的时候可以很方便，不会用的童鞋可以私下问)