Oracle PL/SQL函数返回多行查询结果报错，求正确实现方法

问题根源 #

1. 字符串缓冲区不足：固定长度的varchar2(1000)无法容纳大量拼接内容，触发缓冲区溢出错误。
2. 查询语法错误：原查询未对name、id分组，直接搭配count(*)会导致聚合列不匹配的报错。
3. 低效拼接方式：循环中用||拼接字符串不仅效率低，还容易触发长度限制问题。
4. 函数调用逻辑冗余：外部查询重复关联temp和temp2，与函数内部的关联逻辑重叠，造成不必要的性能损耗。

修复方案（推荐用LISTAGG替代循环） #

方案1：返回VARCHAR2（适用于结果长度≤32767，Oracle 12c+） #

create or replace function sample(userinput varchar) return varchar2 is
  c_list varchar2(32767); -- PL/SQL中VARCHAR2最大支持32767，12c+ SQL层也支持返回该长度
begin
  -- 用LISTAGG直接聚合，避免循环拼接
  select LISTAGG(name || id, ', ') WITHIN GROUP (ORDER BY id)
  into c_list
  from temp 
  join temp2 on temp.id = temp2.id
  where data = userinput
  group by name, id; -- 必须分组，匹配SELECT中的非聚合列

  return c_list;
end;
/

方案2：返回CLOB（适用于超长篇结果） #

如果拼接结果超过32767字符，改用CLOB类型存储：

create or replace function sample(userinput varchar) return CLOB is
  c_list CLOB;
begin
  select LISTAGG(name || id, ', ') WITHIN GROUP (ORDER BY id)
  into c_list
  from temp 
  join temp2 on temp.id = temp2.id
  where data = userinput
  group by name, id;

  return c_list;
end;
/

方案3：保留循环逻辑（仅当必须用循环时） #

如果业务需求必须用循环处理，用CLOB动态扩展并优化拼接方式：

create or replace function sample(userinput varchar) return CLOB is
  c_list CLOB := ''; -- 初始化CLOB
begin
  for c in (
    select name, id, count(*) as cnt -- 保留count(*)需加入SELECT和GROUP BY
    from temp 
    join temp2 on temp.id = temp2.id
    where data = userinput
    group by name, id
  ) loop
    -- 用DBMS_LOB.APPEND高效拼接，避免||的性能问题
    DBMS_LOB.APPEND(c_list, c.name || c.id || '(' || c.cnt || '), ');
  end loop;

  -- 移除末尾多余的分隔符（可选）
  if DBMS_LOB.GETLENGTH(c_list) > 0 then
    c_list := DBMS_LOB.SUBSTR(c_list, DBMS_LOB.GETLENGTH(c_list)-2, 1);
  end if;

  return c_list;
end;
/

额外注意事项 #

• 外部查询调用函数时，确保参数传递逻辑正确：比如select name, sample(name) ...中，name会作为userinput传入函数，需确认data列确实与name值对应，否则查询结果不符合预期。
• Oracle 12c之前的版本，SQL层VARCHAR2最大长度为4000，超过该长度必须用CLOB返回。

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