oracle - 用户定义的聚合函数中的有序迭代？

Question

我刚刚实现了 ODCIAggregate 接口(interface)来创建自定义聚合函数。它工作得很好而且速度很快，但我希望它能做更多的事情。我有一个声明是这样的:

SELECT SomeId, myAggregationFunction(Item) FROM
(
  SELECT 
    Foo.SomeId, 
    SomeType(Foo.SomeValue, Foo.SomeOtherValue) AS Item
  FROM
    Foo
  ORDER BY Foo.SomeOrderingValue
)
GROUP BY SomeId;

我的问题是，项目没有按照我的内部(有序)SELECT 返回它们的相同顺序传递到我的实现的 ODCIAggregateIterate 函数。

我用 Google 搜索了一下，没有找到任何 Oracle 提供的方法来这样做。你们有没有人根据该要求尝试过类似的问题？

谢谢!

Answer 1

您是否考虑过使用 COLLECT 而不是数据磁带？

至少对于字符串聚合，COLLECT 方法更简单，速度也更快。不过，它确实使您的 SQL 有点奇怪。

下面是一个仅使用简单字符串连接的示例。

--Create a type
create or replace type sometype as object
(
    someValue varchar2(100),
    someOtherValue varchar2(100)
);

--Create a nested table of the type.
--This is where the performance improvement comes from - Oracle can aggregate
--the types in SQL using COLLECT, and then can process all the values at once.
--This significantly reduces the context switches between SQL and PL/SQL, which
--are usually more expensive than the actual work.
create or replace type sometypes as table of sometype;

--Process all the data (it's already been sorted before it gets here)
create or replace function myAggregationFunction(p_sometypes in sometypes)
return varchar2 is
    v_result varchar2(4000);
begin
    --Loop through the nested table, just concatenate everything for testing.
    --Assumes a dense nested table
    for i in 1 .. p_sometypes.count loop
        v_result := v_result || ',' ||
            p_sometypes(i).someValue || '+' || p_sometypes(i).someOtherValue;
    end loop;

    --Remove the first delimeter, return value
    return substr(v_result, 2);
end;
/

--SQL
select someId
    ,myAggregationFunction
    (
        cast
        (
            --Here's where the aggregation and ordering happen
            collect(sometype(SomeValue, SomeOtherValue)
                order by SomeOrderingValue)
            as someTypes
        )
    ) result
from
(
    --Test data: note the unordered SoemOrderingValue.
    select 1 someId, 3 SomeOrderingValue, '3' SomeValue, '3' SomeOtherValue
    from dual union all
    select 1 someId, 1 SomeOrderingValue, '1' SomeValue, '1' SomeOtherValue
    from dual union all
    select 1 someId, 2 SomeOrderingValue, '2' SomeValue, '2' SomeOtherValue
    from dual
) foo
group by someId;

--Here are the results, aggregated and ordered.
SOMEID  RESULT
------  ------
1       1+1,2+2,3+3