如何简化PL/SQL中Fetch Into语句以返回JSON_OBJECT_T类型数据

问题场景 #

需要返回包含表中记录数组的JSON_OBJECT_T类型响应，但表存在50列，原代码需逐个声明列变量再拼接JSON，希望优化代码，无需逐个声明列变量，直接将查询结果转换为JSON_ARRAY_T和JSON_OBJECT_T返回。

原代码 #

-- Declare a JSON object variable and a cursor for the query
DECLARE
  l_json_array JSON_ARRAY_T := JSON_ARRAY_T();
  l_json_object JSON_OBJECT_T;
  p_json_object JSON_OBJECT_T := JSON_OBJECT_T();
  l_cursor SYS_REFCURSOR;
  l_id NUMBER;
  l_name VARCHAR2(100);
  l_email_id VARCHAR2(100);
BEGIN
  OPEN l_cursor FOR
    SELECT id, name, email_id
    FROM EMPLOYEE_TBL;
  LOOP
    FETCH l_cursor INTO l_id, l_name, l_email_id;
    EXIT WHEN l_cursor%NOTFOUND;
    l_json_object := JSON_OBJECT_T();
    l_json_object.put('empno', l_id);
    l_json_object.put('ename', l_name);
    l_json_object.put('job', l_email_id);
    l_json_array.append(l_json_object);
  END LOOP;
  CLOSE l_cursor;
  p_json_object.put('employees', l_json_array);
  DBMS_OUTPUT.PUT_LINE(p_json_object.stringify);
END;

期望响应格式 #

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

优化方案 #

方案1：SQL层直接生成JSON（推荐） #

利用Oracle原生JSON函数在SQL层直接构造目标JSON结构，无需声明列变量和PL/SQL循环，性能更优：

DECLARE
  p_json_object JSON_OBJECT_T;
BEGIN
  -- 直接通过SQL生成嵌套JSON并转换为JSON_OBJECT_T
  SELECT JSON_OBJECT(
           'employees' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
               'empno' VALUE id,
               'ename' VALUE name,
               'job' VALUE email_id
               -- 50列只需按此格式依次添加键值映射，无需声明变量
             )
           )
         ) INTO p_json_object
  FROM EMPLOYEE_TBL;

  DBMS_OUTPUT.PUT_LINE(p_json_object.stringify);
END;
/

说明

• JSON_ARRAYAGG将多行数据聚合为JSON数组
• JSON_OBJECT将每行的列映射为指定键名的JSON对象
• 直接将生成的JSON结构赋值给JSON_OBJECT_T变量，全程无需单独声明列变量

方案2：游标结合行级JSON转换 #

如果需要保留游标处理逻辑（比如需添加复杂业务判断），可在游标查询中直接将每行转为JSON对象，再Fetch到JSON_OBJECT_T变量：

DECLARE
  l_json_array JSON_ARRAY_T := JSON_ARRAY_T();
  p_json_object JSON_OBJECT_T := JSON_OBJECT_T();
  l_cursor SYS_REFCURSOR;
  l_row_json JSON_OBJECT_T;
BEGIN
  OPEN l_cursor FOR
    SELECT JSON_OBJECT(
             'empno' VALUE id,
             'ename' VALUE name,
             'job' VALUE email_id
             -- 其他列依次添加键值映射
           ) AS row_json
    FROM EMPLOYEE_TBL;

  LOOP
    FETCH l_cursor INTO l_row_json;
    EXIT WHEN l_cursor%NOTFOUND;
    l_json_array.append(l_row_json);
  END LOOP;
  CLOSE l_cursor;

  p_json_object.put('employees', l_json_array);
  DBMS_OUTPUT.PUT_LINE(p_json_object.stringify);
END;
/

说明

• 游标查询中提前将每行转换为JSON_OBJECT结构
• 直接Fetch到JSON_OBJECT_T类型变量，无需声明每个列的单独变量
• 循环仅需将行JSON对象追加到数组中即可

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