Oracle存储过程中动态遍历JSON响应并插入数据的问题

阿华AIGC实验室

2026-5-11

解决Oracle中动态遍历JSON对象并插入表的问题

嘿，作为Oracle新手碰到JSON遍历的难题太正常了，别慌！你想要的是自动遍历JSON里data对象的所有键值对，不用手动写每个键就能插入表中，对吧？我给你两种常见场景的解决方案，你可以根据自己的表结构来选：

场景1：表是「键值对存储」（推荐）

如果你的TABLE_NAME是用来存键和对应值的（比如有KEY_NAME和KEY_VALUE两列），那用JSON_TABLE直接做集合插入最高效，完全不需要循环：

首先先纠正你原来插入语句的语法问题——Oracle里INSERT的列名不能加单引号，正确的静态写法应该是：

INSERT INTO TABLE_NAME (KEY_NAME, KEY_VALUE)
VALUES ('KEY_ONE', json_value(JSON_TEXT_DATA, '$.data.Key_One'));

现在改成动态遍历的版本：

CREATE OR REPLACE PROCEDURE TEST(JSON_TEXT_DATA IN CLOB) AS
BEGIN
  -- 用JSON_TABLE解析data下的所有键值对，直接插入表
  INSERT INTO TABLE_NAME (KEY_NAME, KEY_VALUE)
  SELECT 
    UPPER(key_name), -- 转成大写匹配Oracle列名（可选，根据你的实际情况调整）
    key_value
  FROM JSON_TABLE(
    JSON_TEXT_DATA,
    '$.data.*' COLUMNS (
      key_name PATH '$."key"',  -- 提取JSON里的键名
      key_value PATH '$."value"' -- 提取对应的值
    )
  );
  
  COMMIT; -- 按需提交，也可以交给调用方处理
END;
/

代码说明：

JSON_TABLE是Oracle 12c+支持的JSON解析工具，'$.data.*'表示遍历data对象下的所有成员；
PATH '$."key"'和PATH '$."value"'分别把每个键值对拆成键名和值，变成表的一行；
用UPPER(key_name)是因为Oracle列名默认是大写的，如果你的JSON键是驼峰式（比如Key_one），转成大写后就能匹配表的列名（比如KEY_ONE）。

场景2：表的列名对应JSON的键

如果你的TABLE_NAME的列名和JSON里的键一一对应（比如表有KEY_ONE、KEY_TWO列，要把Key_one的值插入KEY_ONE列），那需要用动态SQL来生成插入语句：

CREATE OR REPLACE PROCEDURE TEST(JSON_TEXT_DATA IN CLOB) AS
  TYPE KeyValueList IS TABLE OF VARCHAR2(2000);
  v_keys KeyValueList;    -- 存所有JSON键名
  v_values KeyValueList;  -- 存对应的值
  v_sql VARCHAR2(4000);
BEGIN
  -- 先把所有键值对收集到集合里
  SELECT 
    UPPER(key_name),
    key_value
  BULK COLLECT INTO v_keys, v_values
  FROM JSON_TABLE(
    JSON_TEXT_DATA,
    '$.data.*' COLUMNS (
      key_name PATH '$."key"',
      key_value PATH '$."value"'
    )
  );
  
  -- 拼接动态INSERT语句
  v_sql := 'INSERT INTO TABLE_NAME (' || 
           LISTAGG(v_keys(i), ', ') WITHIN GROUP (ORDER BY i) || 
           ') VALUES (' || 
           LISTAGG('''' || REPLACE(v_values(i), '''', '''''') || '''', ', ') WITHIN GROUP (ORDER BY i) || 
           ')';
  
  -- 执行动态SQL
  EXECUTE IMMEDIATE v_sql;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出错啦：' || SQLERRM);
    RAISE; -- 把错误抛出去，方便调试
END;
/