SQL Oracle 中 listagg 的替代方案？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9412512/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
alternative to listagg in Oracle?
提问by Data-Base
listagg is a function introduced in Oracle 11.2! now this function is bugging us allot, we are migrating from MySQL to Oracle and we have this query:
listagg 是 Oracle 11.2 中引入的函数！现在这个函数正在困扰我们，我们正在从 MySQL 迁移到 Oracle，我们有这个查询：
SELECT
p_id,
MAX(registered) AS registered,
listagg(MESSAGE, ' ') within GROUP (ORDER BY registered) AS MESSAGE
FROM
umm_parent_id_remarks_v m
GROUP BY
m.p_id;
is works fine in MySQL as far as we know what bugging us is under Oracle it returns VARCAR and not CLOB as we need! the text is huge and we do need it to be CLOB!
就我们知道在 Oracle 下困扰我们的是什么，它在 MySQL 中运行良好，它返回 VARCAR 而不是我们需要的 CLOB！ 文本很大，我们确实需要它是 CLOB！
here is what I tried to do!
这就是我试图做的！
create a CLOB_T table of CLOB Type!
创建一个 CLOB 类型的 CLOB_T 表！
then create the function
然后创建函数
create or replace
function listaggclob (t in clob_t)
return clob
as
ret clob := '';
i number;
begin
i := t.first;
while i is not null loop
if ret is not null then
ret := ret || ' ';
end if;
ret := ret || t(i);
i := t.next(i);
end loop;
return ret;
end;
now if I run it:
现在，如果我运行它：
SELECT
p_id,
MAX(registered) AS registered,
listaggclob(cast(collect (MESSAGE) as clob_t)) MESSAGE
FROM
umm_parent_id_remarks_v m
GROUP BY
m.p_id;
I get
我得到
ORA-22814: attribute or element value is larger than specified in type
ORA-22814: 属性或元素值大于类型中指定的值
is there any solution for it?
有什么解决办法吗？
thanks you
感谢您
回答by Colin Pickard
回答by Marco Zecca
WM_CONCAT worked for me.
WM_CONCAT 对我来说有效。
SELECT replace(WMSYS.WM_CONCAT(myTable.name), ',', ';')
FROM myTable
GROUP BY myTable.id
I wrapped it with a "replace" to specify a different item separator (';') from the one used by WM_CONCAT (',').
我用“替换”将它包裹起来，以指定与 WM_CONCAT (',') 使用的分隔符不同的项目分隔符 (';')。
回答by Ankur Bhutani
Use xmlAgg, example is shown below:
使用xmlAgg，示例如下所示：
SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;
This will return clob value and so no need to create custom function.
这将返回 clob 值，因此无需创建自定义函数。
回答by Codo
You might want to look at user-defined aggregate functions.
您可能想查看用户定义的聚合函数。
Differnt string aggregation techniques are shown here. They include an example for user-defined aggregate functions.
回答by Jon Heller
You can solve the ORA-22814error by using MULTISETinstead of COLLECT:
您可以ORA-22814使用MULTISET代替来解决错误COLLECT：
SELECT
p_id,
MAX(registered) AS registered,
listaggclob(cast(multiset(
select MESSAGE
from umm_parent_id_remarks_v
where umm_parent_id_remarks_v.p_id = m.p_id
) as clob_t)) MESSAGE
FROM
umm_parent_id_remarks_v m
GROUP BY
m.p_id;
回答by Javed Khan
-- Creating Clobe Type -- CREATE OR REPLACE TYPE "MSCONCATIMPL_CLOB" AS OBJECT ( resultstring CLOB, delimiter VARCHAR2(10),
-- 创建 Clobe 类型 -- CREATE OR REPLACE TYPE "MSCONCATIMPL_CLOB" AS OBJECT (resultstring CLOB, delimiter VARCHAR2(10),
STATIC FUNCTION odciaggregateinitialize ( io_srccontext IN OUT msconcatimpl_clob ) RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (
self IN OUT msconcatimpl_clob,
value IN CLOB
) RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (
self IN msconcatimpl_clob,
o_returnvalue OUT CLOB,
i_flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (
self IN OUT msconcatimpl_clob,
i_ctx2 IN msconcatimpl_clob
) RETURN NUMBER
); / -- Creating Clobe Type Body --
); / -- 创建 Clobe 类型体 --
CREATE OR REPLACE TYPE BODY "MSCONCATIMPL_CLOB" IS STATIC FUNCTION odciaggregateinitialize ( io_srccontext IN OUT msconcatimpl_clob ) RETURN NUMBER IS BEGIN io_srccontext := msconcatimpl_clob( NULL, NULL ); io_srccontext.delimiter := ' '; RETURN odciconst.success; END odciaggregateinitialize;
创建或替换类型主体“MSCONCATIMPL_CLOB”是静态函数 odciaggregateinitialize ( io_srccontext IN OUT msconcatimpl_clob ) RETURN NUMBER IS BEGIN io_srccontext := msconcatimpl_clob( NULL, NULL ); io_srccontext.delimiter := ' '; 返回 odciconst.success; 结束 odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate (
self IN OUT msconcatimpl_clob,
value IN CLOB
) RETURN NUMBER
IS
BEGIN
IF
value IS NOT NULL
THEN
IF
self.resultstring IS NULL
THEN
self.resultstring := self.resultstring || value;
ELSE
self.resultstring := self.resultstring
|| self.delimiter
|| value;
END IF;
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregateterminate (
self IN msconcatimpl_clob,
o_returnvalue OUT CLOB,
i_flags IN NUMBER
) RETURN NUMBER
IS
BEGIN
o_returnvalue := self.resultstring;
RETURN odciconst.success;
END odciaggregateterminate;
MEMBER FUNCTION odciaggregatemerge (
self IN OUT msconcatimpl_clob,
i_ctx2 IN msconcatimpl_clob
) RETURN NUMBER
IS
BEGIN
IF
self.resultstring IS NULL
AND
i_ctx2.resultstring IS NOT NULL
THEN
self.resultstring := i_ctx2.resultstring;
ELSIF
self.resultstring IS NOT NULL
AND
i_ctx2.resultstring IS NOT NULL
THEN
self.resultstring := self.resultstring
|| self.delimiter
|| i_ctx2.resultstring;
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
END; /
结尾; /
-- Creating Clobe Function --
-- 创建Clobe函数 --
CREATE OR REPLACE FUNCTION ms_concat_clob ( input VARCHAR2 ) RETURN CLOB PARALLEL_ENABLE AGGREGATE USING msconcatimpl_clob; /
CREATE OR REPLACE FUNCTION ms_concat_clob ( input VARCHAR2 ) RETURN CLOB PARALLEL_ENABLE AGGREGATE USING msconcatimpl_clob; /
