Oracle ORDS REST处理器PL/SQL模式下如何返回集合查询结果

以下是不同场景下的实现方式，均可以实现和集合查询模式SELECT * FROM SOMETABLE一致的JSON返回效果：

方案1：Oracle 12c及以上版本使用内置JSON函数（推荐） #

Oracle 12c Release 2开始支持JSON_OBJECT(*)语法，可以直接匹配行的所有字段生成JSON对象，配合JSON_ARRAYAGG聚合为完整的结果数组，和原生集合查询返回格式完全一致：

DECLARE
  -- 存储最终JSON结果，数据量大建议用CLOB
  v_query_result CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(*) 
           RETURNING CLOB -- 数据量超过默认VARCHAR2长度时必须加
         ) RETURNING CLOB
    INTO v_query_result
    FROM SOMETABLE; -- 替换为实际查询的表名
  
  -- 输出结果，也可作为存储过程/函数的返回值对外提供
  DBMS_OUTPUT.PUT_LINE(v_query_result);
END;
/

如果需要格式化输出可读性更高的JSON，只需要在JSON_OBJECT后加PRETTY参数：JSON_OBJECT(*) PRETTY

方案2：封装为通用查询函数 #

如果需要复用该能力，可以封装为通用函数，支持动态传入表名查询：

CREATE OR REPLACE FUNCTION get_table_json(p_table_name VARCHAR2) 
RETURN CLOB
IS
  v_json_result CLOB;
  v_sql_text VARCHAR2(2000);
BEGIN
  -- 用DBMS_ASSERT校验表名，避免SQL注入风险
  v_sql_text := 'SELECT JSON_ARRAYAGG(JSON_OBJECT(*) RETURNING CLOB) RETURNING CLOB FROM ' 
                || DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name);
  EXECUTE IMMEDIATE v_sql_text INTO v_json_result;
  RETURN v_json_result;
END;
/

调用示例：SELECT get_table_json('EMP') FROM DUAL;

方案3：12c以下旧版本Oracle手动拼接JSON #

如果使用的是不支持内置JSON函数的旧版本，可以手动拼接字符串实现，需要注意转义JSON中的特殊字符：

DECLARE
  v_json_result CLOB := '[';
  v_first_row BOOLEAN := TRUE;
  -- 定义查询游标
  CURSOR cur_data IS SELECT * FROM SOMETABLE;
  v_row cur_data%ROWTYPE;
BEGIN
  OPEN cur_data;
  LOOP
    FETCH cur_data INTO v_row;
    EXIT WHEN cur_data%NOTFOUND;
    
    -- 非首行前加逗号分隔
    IF NOT v_first_row THEN
      v_json_result := v_json_result || ',';
    END IF;
    v_first_row := FALSE;
    
    -- 按实际字段拼接，字符串类型字段需要转义双引号
    v_json_result := v_json_result || '{'
      || '"ID":' || v_row.ID || ','
      || '"NAME":"' || REPLACE(v_row.NAME, '"', '\\"') || '"'
    || '}';
  END LOOP;
  CLOSE cur_data;
  
  v_json_result := v_json_result || ']';
  DBMS_OUTPUT.PUT_LINE(v_json_result);
END;
/

注意事项 #

• 单表数据量超过1万行时，建议显式指定RETURNING CLOB参数，避免超出VARCHAR2类型的长度上限
• 字符串字段中如果存在双引号、换行符等JSON特殊字符，必须做转义处理，避免返回的JSON格式不合法

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