Oracle 中 listagg 的替代品？

Question

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;

据我们所知，在 MySQL 中工作正常 困扰我们的是在 Oracle 下它返回 VARCAR 而不是我们需要的 CLOB！ 文本很大，我们确实需要它是 CLOB！

这就是我尝试做的！

创建一个 CLOB 类型的 CLOB_T 表！

然后现在创建该函数

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;

，如果我运行它：

  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;

我得到

ORA-22814：属性或元素值大于类型中指定的

有什么解决方案吗？

感谢您

Answer 1

使用收集或编写您自己的聚合函数。

Answer 2

使用 xmlAgg，示例如下所示：

SELECT RTRIM(XMLAGG(XMLELEMENT(E,colname,',').EXTRACT('//text()') ORDER BY colname).GetClobVal(),',') AS LIST
FROM tablename;

这将返回 clob 值，因此无需创建自定义函数。

Answer 3

您可以使用 MULTISET 而不是 COLLECT 来解决 ORA-22814 错误：

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;

Answer 4

您可能想查看用户定义的聚合函数 。

此处显示了不同的字符串聚合技术。它们包括用户定义的聚合函数的示例。

Answer 5

WM_CONCAT 为我工作。

SELECT replace(WMSYS.WM_CONCAT(myTable.name), ',', ';')
FROM myTable
GROUP BY myTable.id

我用“replace”包裹它，以指定与 WM_CONCAT 使用的项目分隔符（';'）不同的项目分隔符（','）。

Answer 6

-- Creating Clobe Type --
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 --

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;

    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 --

CREATE OR REPLACE FUNCTION ms_concat_clob ( input   VARCHAR2 ) RETURN CLOB
    PARALLEL_ENABLE
AGGREGATE USING msconcatimpl_clob;
/