-------------------------------------------
-- Export file for user BOSS1214 --
-- Created by user on 2011-2-25, 9:34:30 --
-------------------------------------------
spool str_sum_sql.log
prompt
prompt Creating type STR_SUM_OBJ
prompt =========================
prompt
CREATE OR REPLACE TYPE STR_SUM_OBJ AS OBJECT --聚合函数的实质就是一个对象
(
SUM_STRING VARCHAR2(4000),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ)
RETURN NUMBER, --对象初始化
--聚合函数的迭代方法(这是最重要的方法)
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_SUM_OBJ,
VALUE IN VARCHAR2) RETURN NUMBER,
--当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_SUM_OBJ,
V_NEXT IN STR_SUM_OBJ)
RETURN NUMBER,
--终止聚集函数的处理,返回聚集函数处理的结果.
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_SUM_OBJ,
RETURN_VALUE OUT VARCHAR2,
V_FLAGS IN NUMBER)
RETURN NUMBER
)
/
prompt
prompt Creating function STR_SUM
prompt =========================
prompt
CREATE OR REPLACE FUNCTION STR_SUM(VALUE VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING STR_SUM_OBJ;
/
prompt
prompt Creating type body STR_SUM_OBJ
prompt ==============================
prompt
CREATE OR REPLACE TYPE BODY STR_SUM_OBJ IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(V_SELF IN OUT STR_SUM_OBJ)
RETURN NUMBER IS
BEGIN
V_SELF := STR_SUM_OBJ(NULL);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT STR_SUM_OBJ,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
SELF.SUM_STRING := SELF.SUM_STRING || VALUE;
RETURN ODCICONST.SUCCESS;
END ODCIAGGREGATEITERATE;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT STR_SUM_OBJ,
V_NEXT IN STR_SUM_OBJ)
RETURN NUMBER IS
BEGIN
SELF.SUM_STRING := SELF.SUM_STRING || V_NEXT.SUM_STRING;
RETURN ODCICONST.SUCCESS;
END ODCIAGGREGATEMERGE;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN STR_SUM_OBJ,
RETURN_VALUE OUT VARCHAR2,
V_FLAGS IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN_VALUE := SELF.SUM_STRING;
RETURN ODCICONST.SUCCESS;
END ODCIAGGREGATETERMINATE;
END;
/
spool off
示例用法如下:
select STR_SUM(pi.productcode || ',')
from t_productinfos pi
where pi.producttypecode = '00';
另外,wm_concat函数,在一些oracle版本里可能会返回clob类型,导致程序出错。
因此
wm_concat是
WMSYS下的东西
,
oracle内部用的,一般程序中最好不要用到它。
*********************************************************************************
今天在测试环境上运行一个使用到wmsys.wm_concat函数的过程,日志记录错误为
1
ORA-00932: inconsistent datatypes: expected - got CLOB
但是该过程在生产环境上运行正常不报错。网上google之,该函数为undocumented的,不被oracle官方支持,随着版本变化也会有修改,但并不保证行为一致。
测试环境为
1
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 compatible string 10.2.0.3.0
重启并修改compatible string 为10.2.0.5.0,测试wmsys.wm_concat返回值仍为CLOB
生产环境为
1
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 compatible string 11.2.0.0.0
最终解决方法是在wm_concat外层用to_char作转换。
PS:之前在11g生产环境中用过一次wm_concat,结果发现在同一个select子句中使用该函数不能超过两次,于是换用listagg和正则表
达式解决了问题。看来以后尽量避免使用wmsys.wm_concat,不便于移植啊!