Oracle SQL-LISTAGG将被称为EVEN CASE语句的条件将不会填满 [英] Oracle SQL - LISTAGG will be called even condition of CASE statement will not be fullfilled

问题描述

我必须在版本为Oracle Database 12c Release 12.1.0.1.0 - 64bit Production的oracle DB服务器上工作。因此，在使用LISTAGG函数时，我不能使用ON OVERFLOW ...。为了克服大小大于4000字节的列的聚合，我希望使用提到的建议解决方案here，这样我就不会收到错误01489. 00000 - "result of string concatenation is too long"。

不幸的是，这种方法对我不起作用。即使条件为1=1(不应调用LISTAGG函数)，我也会收到上述错误。

此处查询：

select distinct t.id,
    (case when 1=1 then
        'Test'
    else
        LISTAGG(mh.RNAME1, '; ') WITHIN GROUP (ORDER BY mh.RNAME1)
    end) as RNAME1LIST,
    (case when 1=1 then
        'Test'
    else
        LISTAGG(mh.RNAME2, '; ') WITHIN GROUP (ORDER BY mh.RNAME2)
    end) as RNAME2LIST, 
    t.email1 as EMAIL1
from mh 
join mp on MH.MID = MP.MID
RIGHT JOIN T ON mh.T_ID = T.ID
group by t.id, t.email1;

我正在寻找此行为的解释和替代方法，这样我就不会收到提到的错误。

推荐答案

这是因为聚合实际上是在ORACLE评估行组时在飞翔上进行的，但是case表达式位于select列表中，因此对结果数据集进行了评估。

您预期的处理应该缓冲所有输入并维护交叉引用(聚合到源行)。或者处理复杂的依赖关系树，因为在select列表中，您可以将聚合结果与同一表达式中的group by列一起使用，这也可能引入循环依赖。

下面是要查看的一些日志记录函数的示例，无论case结果如何，它都是在聚合步骤计算的。并在聚合之后评估case。

/*Logging table*/
create table t (
  /*To have a sequence*/
  id number GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1)
  , val number
  , src varchar2(100)
)

/*Logger to check what is going on here*/
create function f_test(
  p_val in number
  , p_src in varchar2
) return varchar2
as
  pragma autonomous_transaction;
begin
  insert into t(val, src)
  values (p_val, p_src);
  commit;
  
  return p_val;
end;
/

/*Log aggregation function invocation and case/where evaluation*/
with a as (
  select
    level as l
    , mod(level, 3) as grp
  from dual
  connect by level < 7
)
select
  grp
  , case
      when f_test(grp, 'CASE CONDITION') > 0
      then f_test(grp, 'CASE RESULT')
      else max(f_test(grp, 'AGG'))
    end as res_agg
from a
where f_test(grp, 'WHERE') = grp
group by grp

GRP | RES_AGG
--: | :------
  2 | 2      
  0 | 0      
  1 | 1      

select *
from t
order by id

ID | VAL | SRC           
-: | --: | :-------------
 1 |   1 | WHERE         
 2 |   1 | AGG           
 3 |   2 | WHERE         
 4 |   2 | AGG           
 5 |   0 | WHERE         
 6 |   0 | AGG           
 7 |   1 | WHERE         
 8 |   1 | AGG           
 9 |   2 | WHERE         
10 |   2 | AGG           
11 |   0 | WHERE         
12 |   0 | AGG           
13 |   2 | CASE CONDITION
14 |   2 | CASE RESULT   
15 |   0 | CASE CONDITION
16 |   1 | CASE CONDITION
17 |   1 | CASE RESULT   

db<；>；小提琴here

但是，您可以像在Oracle 12c+中那样解决此问题。您可以在with子句中声明本地函数。这样，您可以将数据聚合为集合类型，然后在PL/SQL中将其连接起来。或者作为一种通用且更快速的方法：按照另一个答案中的描述，为clob数据类型定义您的自定义Listagg。

with function f_listagg_clob(
  p_vc2_tab in sys.odcivarchar2list
  , p_sep in varchar2 default ', '
)
  return clob
as
  r_clob clob;
begin
  for i in 1..p_vc2_tab.count loop
    r_clob := r_clob || case when i > 1 then p_sep end || p_vc2_tab(i);
  end loop;

  return r_clob;
end;

a as (
  select
    dbms_random.string('X', 100) as str
  from dual
  connect by level < 1000
)
select length(f_listagg_clob(cast(collect(str) as sys.odcivarchar2list))) as result_len
from a

| RESULT_LEN |
| ---------: |
|     101896 |

db<；>；小提琴here

这篇关于Oracle SQL-LISTAGG将被称为EVEN CASE语句的条件将不会填满的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！