Oracle存储过程如何为OUT参数赋值并返回JSON格式结果

你原有代码存在两个核心错误：

• 混用了OPEN...FOR游标语法和SELECT...INTO赋值语法，二者不能同时使用
• 无法直接将SYS_REFCURSOR类型强制转换为CLOB类型，游标是结果集指针，不属于可直接转换的类型

完整实现代码 #

CREATE OR REPLACE PROCEDURE GET_TABLE_NAMES(
    JSON_DATA OUT CLOB,
    OUT_IS_SUCCESS OUT BOOLEAN,
    OUT_ERROR_MESSAGE OUT VARCHAR2
)
AS
BEGIN
    -- 初始化状态参数
    OUT_IS_SUCCESS := TRUE;
    OUT_ERROR_MESSAGE := NULL;
    JSON_DATA := EMPTY_CLOB();

    -- 直接查询聚合为JSON赋值给输出参数，无需游标中转
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT('TABLE_NAME' VALUE T.TABLE_NAME)
    ) INTO JSON_DATA
    FROM (
        SELECT TABLE_NAME FROM all_tables
    ) T;

EXCEPTION
    WHEN OTHERS THEN
        -- 异常时更新状态
        OUT_IS_SUCCESS := FALSE;
        OUT_ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 4000); -- 截断到参数最大长度避免溢出
        JSON_DATA := EMPTY_CLOB();
END GET_TABLE_NAMES;
/

功能说明 #

• 无需额外定义游标：JSON_ARRAYAGG是聚合函数，查询结果只有单条CLOB类型的JSON串，直接用SELECT INTO即可完成对JSON_DATA的赋值
• 状态参数自动更新：执行成功时OUT_IS_SUCCESS返回TRUE、OUT_ERROR_MESSAGE返回空；执行报错时OUT_IS_SUCCESS返回FALSE、OUT_ERROR_MESSAGE返回报错信息
• 返回JSON格式标准：输出的JSON_DATA为[{"TABLE_NAME":"表名1"},{"TABLE_NAME":"表名2"},...]的标准JSON结构，可直接解析使用

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