Определите WM_CONCAT(col) как LISTAGG(col, ',') для Oracle Migration

[TL;DR] Вы не можете реализовать пользовательскую версию WM_CONCAT в Oracle 18c, чтобы она вел себя точно так же, как LISTAGG, но вы можете приблизиться к ней с помощью определяемой пользователем функции агрегирования.

LISTAGG имеет синтаксис:

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‹>скрипка здесь

Обновление:

Если мы выберем неэффективную функцию агрегирования, которая хранит все значения в коллекции, а затем вызывает 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‹>скрипка здесь