Oracle触发器使用LISTAGG报ORA-00937非单组分组函数错误咨询

问题原因 #

你遇到的ORA-00937错误属于Oracle聚合函数的语法约束：当查询中使用了LISTAGG这类聚合函数时，SELECT子句中所有未被聚合包裹的字段，都必须出现在GROUP BY子句中。你当前的SQL中，:new.jobid、LOCATIONNAME、DEPTNAME等字段都属于非聚合字段，没有声明分组规则，因此触发报错。

解决方案 #

方案1：补充GROUP BY子句（推荐，适配所有支持LISTAGG的Oracle版本） #

因为你通过:new主键关联的JOBS_LOCATION、JOBS_DEPARTMENT、JOBS_GRADE表均为1:1匹配，仅JOBS_JOBCLASS关联的分类是1:N关系，直接在查询末尾加上GROUP BY子句即可：

INSERT INTO jobs_tfour_data
(
   jobid,
   title,
   reference,
   salary,
   location,
   department,
   grade,       
   opendate,
   closedate,
   description,
   internal,
   category
)     
select :new.jobid,:new.TITLE,:new.REFERENCE,:new.SALARY,LOCATIONNAME,DEPTNAME,GRADENAME,:new.OPENDATE,:new.CLOSEDATE,:new.description,:new.internal, LISTAGG(CLASSNAME,'; ') WITHIN GROUP (order by cj.CLASSID) as category
from JOBS_LOCATION l , JOBS_DEPARTMENT d, JOBS_GRADE g,JOBS_JOBCLASS cj ,JOBS_CLASSIFICATION c 
where  
l.locationid = :new.locationid 
and d.deptid = :new.departmentid 
and g.gradeid =  :new.gradeid  
and cj.jobid = :new.jobid
and cj.classid = c.classid   
-- 新增GROUP BY，列出所有非聚合字段
GROUP BY :new.jobid,:new.TITLE,:new.REFERENCE,:new.SALARY,LOCATIONNAME,DEPTNAME,GRADENAME,:new.OPENDATE,:new.CLOSEDATE,:new.description,:new.internal;

方案2：子查询单独聚合分类字段 #

如果不想调整整体GROUP BY规则，可以把分类聚合逻辑封装为标量子查询，写法更简洁：

INSERT INTO jobs_tfour_data
(
   jobid,
   title,
   reference,
   salary,
   location,
   department,
   grade,       
   opendate,
   closedate,
   description,
   internal,
   category
)     
select 
    :new.jobid,
    :new.TITLE,
    :new.REFERENCE,
    :new.SALARY,
    l.LOCATIONNAME,
    d.DEPTNAME,
    g.GRADENAME,
    :new.OPENDATE,
    :new.CLOSEDATE,
    :new.description,
    :new.internal,
    -- 子查询单独聚合分类
    (SELECT LISTAGG(c.CLASSNAME,'; ') WITHIN GROUP (order by cj.CLASSID) 
     FROM JOBS_JOBCLASS cj ,JOBS_CLASSIFICATION c 
     WHERE cj.jobid = :new.jobid AND cj.classid = c.classid) as category
from JOBS_LOCATION l , JOBS_DEPARTMENT d, JOBS_GRADE g
where  
l.locationid = :new.locationid 
and d.deptid = :new.departmentid 
and g.gradeid =  :new.gradeid;

低版本Oracle兼容方案（11g之前无LISTAGG的场景） #

如果使用的是Oracle 11g以下版本，可以用XML拼接方式实现相同效果，该方式也支持超长字符串拼接：

-- 替换LISTAGG部分为如下写法
RTRIM(XMLAGG(XMLELEMENT(E, CLASSNAME, '; ').EXTRACT('//text()') ORDER BY cj.CLASSID).GETCLOBVAL(), '; ') as category

注意事项 #

如果分类拼接后的长度超过VARCHAR2类型的4000字节上限，需要将jobs_tfour_data表的category字段改为CLOB类型，避免拼接截断报错。

内容的提问来源于stack exchange，提问作者kbodh