Oracle PL/SQL存储过程返回表行JSON数组的最优实现方法咨询

算力阿峰

2026-10-2

问题解答

原代码存在的问题

变量命名冲突：FOR循环的游标变量和OUT输出参数都叫output，会导致参数赋值逻辑完全错误
初始值错误：开头将output赋值为右方括号]，最终生成的JSON结构会完全不符合要求
缺少首行标记更新：l_first_rec变量永远为TRUE，导致多个JSON对象之间不会添加逗号分隔符
语法错误：JSON_OBJECT行末尾多了多余的右括号
重复拼接右括号：循环结束后重复拼接右括号，最终会多出来一个无效的]

最优实现方案

Oracle 12c R2及以上版本支持原生JSON聚合函数JSON_ARRAYAGG，不需要手动循环拼接，性能更高、不会出现格式错误，是最优实现：

CREATE OR REPLACE PACKAGE BODY "PKG_GETCOR" AS

PROCEDURE SelectProc(output OUT VARCHAR2) IS 
BEGIN
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      'id' VALUE TO_CHAR(ID),
      'value' VALUE TB_VALUE
    )
    ORDER BY ID
    RETURNING VARCHAR2
  ) INTO output
  FROM MY_TABLE;
END SelectProc;

END PKG_GETCOR;
/

如果是更低版本Oracle不支持JSON_ARRAYAGG，可以使用修正后的循环版本：

CREATE OR REPLACE PACKAGE BODY "PKG_GETCOR" AS

PROCEDURE SelectProc(output OUT VARCHAR2) IS 
v_json_start CHAR(1) := '[';
v_json_end CHAR(1) := ']';
l_first_rec BOOLEAN := TRUE;

BEGIN
  output := v_json_start;
  FOR rec IN (SELECT ID, TB_VALUE FROM MY_TABLE ORDER BY ID)
  LOOP
    IF NOT l_first_rec THEN
        output := output || ',';
    END IF;
    output := output || JSON_OBJECT('id' VALUE TO_CHAR(rec.ID), 'value' VALUE rec.TB_VALUE);
    l_first_rec := FALSE;
  END LOOP;   
  output := output || v_json_end;
END SelectProc;

END PKG_GETCOR;
/