Oracle 12c：如何在REST调用中用动态SQL实现通用JSON查询返回

阿华AIGC实验室

2026-5-19

解决Oracle 12c中动态SQL结合REST返回JSON的问题

我正好做过类似的需求，在Oracle 12c环境下结合APEX实现通用查询REST接口完全可行，你遇到的动态SQL无法运行的问题，核心是要正确处理动态游标+JSON转换，再配合REST服务发布，下面一步步给你拆解：

1. 先搞定动态SQL转游标

静态SQL能用SYS_REFCURSOR，动态的话只要用OPEN ... FOR语句就能打开动态游标，先写个存储过程来封装这个逻辑：

CREATE OR REPLACE PROCEDURE open_dynamic_cursor(p_sql IN VARCHAR2, p_cursor OUT SYS_REFCURSOR)
IS
BEGIN
  -- 先做基础校验：只允许SELECT语句，防止恶意操作
  IF UPPER(TRIM(p_sql)) NOT LIKE 'SELECT%' THEN
    RAISE_APPLICATION_ERROR(-20001, '仅支持查询类SQL语句');
  END IF;
  
  OPEN p_cursor FOR p_sql;
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20002, 'SQL执行失败: ' || SQLERRM);
END;
/

2. 把动态游标转成JSON

因为返回的列数不固定，用Oracle原生的JSON_ARRAYAGG很难处理动态列，APEX_JSON包是最佳选择——它能自动识别游标里的所有列，直接转成JSON格式。我们写个函数把整个流程串起来：

CREATE OR REPLACE FUNCTION get_query_result_json(p_sql IN VARCHAR2) RETURN CLOB
IS
  l_cursor SYS_REFCURSOR;
  l_json_output CLOB;
BEGIN
  -- 打开动态游标
  open_dynamic_cursor(p_sql, l_cursor);
  
  -- 初始化APEX_JSON输出
  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object;
  -- 直接把游标写入JSON的"data"节点
  APEX_JSON.write('data', l_cursor);
  APEX_JSON.close_object;
  
  -- 获取生成的JSON
  l_json_output := APEX_JSON.get_clob_output;
  APEX_JSON.free_output;
  CLOSE l_cursor;
  
  RETURN l_json_output;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常返回错误信息
    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
    APEX_JSON.write('error', SQLERRM);
    APEX_JSON.close_object;
    l_json_output := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;
    RETURN l_json_output;
END;
/

3. 发布成REST接口（用APEX）

APEX自带的RESTful Services可以快速把上面的函数发布成接口，步骤如下：

进入你的APEX工作区，导航到SQL Workshop > RESTful Services
点击Create创建新的REST Module，比如命名为DynamicQueryAPI，Base Path设为/api/dynamic-query/
创建Resource Template：路径设为/run，方法选POST（用POST比GET更安全，避免SQL暴露在URL里，也没有长度限制）
在Resource Handler的Source里选择PL/SQL，然后写以下代码：

DECLARE
  l_input_sql VARCHAR2(32767);
  l_result_json CLOB;
BEGIN
  -- 从请求的JSON体里获取传入的SQL语句
  l_input_sql := APEX_JSON.get_varchar2(p_path => 'sql');
  -- 调用我们写的JSON生成函数
  l_result_json := get_query_result_json(l_input_sql);
  -- 输出JSON响应
  HTP.p(l_result_json);
  -- 设置响应头为JSON格式
  OWA_UTIL.mime_header('application/json', FALSE);
END;

4. 测试接口

用Postman或者curl发送POST请求，请求体是JSON格式：

{"sql": "SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 20"}

你会得到类似这样的响应：

{
  "data": [
    {"EMPNO":7369,"ENAME":"SMITH","SAL":800},
    {"EMPNO":7566,"ENAME":"JONES","SAL":2975},
    {"EMPNO":7788,"ENAME":"SCOTT","SAL":3000},
    {"EMPNO":7876,"ENAME":"ADAMS","SAL":1100},
    {"EMPNO":7902,"ENAME":"FORD","SAL":3000}
  ]
}