Oracle PLSQL自定义表类型转JSON_OBJECT_T实现求助

类型定义示例（基于常见场景假设） #

如果你的类型尚未定义，可参考以下示例（已有则跳过）：

-- 自定义对象类型
CREATE OR REPLACE TYPE XX_OUTPUT_OBJ AS OBJECT (
  ID NUMBER,
  NAME VARCHAR2(100),
  AMOUNT NUMBER
);
/

-- 自定义表类型（对象类型的集合）
CREATE OR REPLACE TYPE XX_INPUT_TYPE AS TABLE OF XX_OUTPUT_OBJ;
/

转换实现代码 #

以下代码将XX_INPUT_TYPE表类型数据转换为JSON_OBJECT_T，生成包含数据数组的标准JSON格式：

DECLARE
  -- 初始化测试用表类型数据
  l_input XX_INPUT_TYPE := XX_INPUT_TYPE(
    XX_OUTPUT_OBJ(1, '产品A', 150),
    XX_OUTPUT_OBJ(2, '产品B', 200),
    XX_OUTPUT_OBJ(3, '产品C', 80)
  );
  l_data_array JSON_ARRAY_T := JSON_ARRAY_T(); -- 存储每条数据的JSON数组
  l_final_json JSON_OBJECT_T := JSON_OBJECT_T(); -- 最终的JSON对象
  l_single_obj JSON_OBJECT_T; -- 单条数据的JSON对象
BEGIN
  -- 遍历表类型集合
  FOR i IN 1..l_input.COUNT LOOP
    l_single_obj := JSON_OBJECT_T();
    -- 为单条数据添加属性（自动处理字符转义，避免拼接错误）
    l_single_obj.PUT('id', l_input(i).ID);
    l_single_obj.PUT('name', l_input(i).NAME);
    l_single_obj.PUT('amount', l_input(i).AMOUNT);
    -- 将单条数据JSON加入数组
    l_data_array.PUT(l_single_obj);
  END LOOP;

  -- 将数据数组放入最终JSON对象
  l_final_json.PUT('result', l_data_array);

  -- 打印生成的JSON字符串
  DBMS_OUTPUT.PUT_LINE(l_final_json.TO_STRING());
END;
/

生成的JSON格式示例 #

运行后将输出类似以下的JSON：

{
  "result": [
    {"id":1,"name":"产品A","amount":150},
    {"id":2,"name":"产品B","amount":200},
    {"id":3,"name":"产品C","amount":80}
  ]
}

关键说明 #

• 避免直接字符串拼接生成JSON：如果字段值包含引号、特殊字符，拼接会导致JSON格式错误，使用JSON_OBJECT_T.PUT()方法会自动处理转义逻辑。
• 若目标格式是纯数组而非包含数组的对象，直接返回l_data_array.TO_STRING()即可。
• 确保Oracle版本支持JSON_OBJECT_T：该类从Oracle 12c R2（12.2）开始提供，若版本过低需使用JSON_ARRAY、JSON_OBJECT函数替代。

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