oracle - 将 "WM_CONCAT(col)"定义为 "LISTAGG(col, ' ,')"用于 Oracle 迁移

Question

我们正在为我们的环境数据库从 Oracle 10g 迁移到 18c。使事情复杂化的是，并非所有环境都计划一次迁移，因此应用程序必须同时支持这两种环境。发现的不兼容性之一是 WM_Concat 在 10g 中受支持但在 18c 中不受支持，而 ListAgg(新的等效函数)在 18c 中受支持但在 10g 中不受支持。因此，我正在寻找一种暂时适用于两个数据库版本的实现。

我的想法是10g中的wm_concat(myColumn)等同于18c中的listagg(myColumn, ',')，所以我想定义wm_concat(myColumn) 成为新的 18c 数据库中的一个函数，它在后台传递给 listagg(myColumn, ',') 并返回结果。这样，应用程序可以安全地继续在 10g 和 18c 数据库上正常使用 wm_concat，直到所有环境都在 18c 上，之后可以将应用程序切换为使用 listagg并且可以从 18c 数据库中删除临时自定义 wm_concat 函数，完成迁移。

总而言之，定义 wm_concat 的正确方法是什么，以便 wm_concat(myColumn) 的行为与 listagg(myColumn, ',' ) 在查询中？

Answer 1

[TL;DR] 您无法在 Oracle 18c 中实现自定义版本的 WM_CONCAT 以使其行为与 LISTAGG 完全相同，但您可以接近用户-定义聚合函数。

---

LISTAGG has the syntax :

WM_CONCAT 的语法是:

WM_CONCAT( expr )

您可以看到 WM_CONCAT 无法指定分隔符或 ORDER BY 子句。

如果你想在以后的版本中重新定义 WM_CONCAT 那么你最终可能会使用用户定义的聚合函数:

用户定义对象:

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
);
/

用户定义的对象主体:

CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := SUBSTR( SELF.g_string, 2 );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.g_string := SELF.g_string || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/

用户定义的聚合函数:

CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/

测试数据:

CREATE TABLE test_data ( id, value ) AS
  SELECT 1, 'C' FROM DUAL UNION ALL
  SELECT 1, 'A' FROM DUAL UNION ALL
  SELECT 1, 'B' FROM DUAL UNION ALL
  SELECT 2, 'D' FROM DUAL UNION ALL
  SELECT 2, 'E' FROM DUAL;

测试查询:

SELECT id,
       wm_concat( value ) AS wm_concat,
       LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS listagg
FROM   test_data
GROUP BY id;

输出:

ID | WM_CONCAT | LISTAGG-: | :-------- | :------ 1 | C,B,A     | C,A,B   2 | D,E       | D,E    

如您所见，输出的顺序不同；所以你可以接近但不是完全匹配。

db<> fiddle here

---

更新:

如果我们寻找一个低效的聚合函数，将所有值存储在一个集合中，然后调用 LISTAGG，那么我们可以更接近:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  strings stringlist,

  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(
    sctx  IN OUT  t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    sctx := t_string_agg( stringlist() );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self   IN OUT  t_string_agg,
    value  IN      VARCHAR2
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.strings.EXTEND;
    SELF.strings( SELF.strings.COUNT ) := value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self         IN   t_string_agg,
    returnValue  OUT  VARCHAR2,
    flags        IN   NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    SELECT LISTAGG( column_value, ',' ) WITHIN GROUP ( ORDER BY column_value )
    INTO   returnValue
    FROM   TABLE( SELF.strings );
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self  IN OUT  t_string_agg,
    ctx2  IN      t_string_agg
  ) RETURN NUMBER
  IS
  BEGIN
    SELF.strings := SELF.strings MULTISET UNION ALL ctx2.strings;
    RETURN ODCIConst.Success;
  END;
END;
/

CREATE OR REPLACE FUNCTION wm_concat (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/

然后:

SELECT id,
       wm_concat( value ) AS wm_concat,
       LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY value ) AS listagg
FROM   test_data
GROUP BY id;

输出:

ID | WM_CONCAT | LISTAGG-: | :-------- | :------ 1 | A,B,C     | A,B,C   2 | D,E       | D,E    

如果(且仅当)您想要按字母顺序对值进行排序，它将给出与 LISTAGG 相同的输出；您不能指定不同的顺序。它还需要从 PL/SQL 到 SQL 的上下文切换以在最后一步中执行聚合，因此它可能比纯 PL/SQL 聚合函数慢，并且它将集合保存在内存中并继续扩展它，因此会有随着集合的增长(或在并行系统中合并)会产生额外的开销，这将进一步减慢它的速度。

因此，它仍然不是 LISTAGG，但如果您愿意忍受性能方面的问题，它会尽可能接近。

db<> fiddle here