开发笔记:重写Oracle的wm_concat函数，自定义分隔符排序

-- 定义可变数组，字符串类型，长度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;