我们正在从环境数据库的Oracle 10g迁移到18c。为了使事情复杂化，并非计划立即迁移所有环境，因此该应用程序必须同时支持这两种环境。发现的不兼容性之一WM_Concat是在10g ListAgg中受支持，但在18c中不受支持，而（新的等效功能）在18c中受支持，但在10g中不受支持。因此，我正在寻找一种暂时可以在两个数据库版本中使用的实现。
我的想法是wm_concat(myColumn)10g等同listagg(myColumn, ',')于18c，因此我想wm_concat(myColumn)在新的18c数据库中定义为传递给listagg(myColumn, ',')幕后并返回结果的函数。这样，该应用程序就可以安全地继续wm_concat在10g和18c数据库上正常使用，直到所有环境都在18c上运行为止，此后该应用程序可以交换使用，listagg并且wm_concat可以从18c数据库中删除临时自定义功能，从而完成迁移。
总而言之，什么是正确的定义方式，wm_concat使其wm_concat(myColumn)行为与listagg(myColumn, ',')查询完全相同？
[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 | 利斯塔格 -：| ：-------- | ：------ 1 | C，B，A | 出租车 2 | D，E | D，E
如您所见，输出的顺序不同。因此您可以获得接近但不完全匹配的结果。
db <> 在这里拨弄
更新：
如果我们使用效率低下的聚合函数，该函数将所有值存储在集合中，然后调用，LISTAGG那么我们可以更进一步：
ID | WM_CONCAT | LISTAGG -: | :-------- | :------ 1 | C,B,A | C,A,B 2 | D,E | D,E
然后：
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;
/
输出：
ID | WM_CONCAT | 利斯塔格 -：| ：-------- | ：------ 1 | A，B，C | A，B，C 2 | D，E | D，E
它将提供与LISTAGG（仅当）要按字母顺序对值进行排序时相同的输出。您不能指定其他顺序。它还需要从PL / SQL到SQL的上下文切换，以在最后一步执行聚合，因此它可能比纯PL / SQL聚合函数要慢，并且它将集合保存在内存中并继续扩展它，因此可以随着集合的增长（或在并行系统中合并）而增加额外的开销，这将进一步降低它的速度。
因此，LISTAGG如果您愿意忍受性能方面的问题，它仍然不是，但是它离您将近的接近。
db <> 在这里拨弄
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有