如何用单条查询将JSON数据更新至Oracle 19C数据库？

实现方案 #

利用Oracle的MERGE语句结合JSON_TABLE函数，可将JSON数组中的数据批量匹配到目标表的现有记录，完成单条查询下的批量更新。这里默认CUST_NUM与SORT_ORDER的组合为唯一标识（作为更新匹配条件），若你的唯一键规则不同，自行调整ON子句即可。

批量更新SQL示例 #

DECLARE 
  myJSON CLOB := '[
    {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "FROZEN YOGURT"},
    {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "SORBET"},
    {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "GELATO"}
  ]';
BEGIN
  MERGE INTO jt_test t
  USING (
    SELECT CUST_NUM, SORT_ORDER, CATEGORY
    FROM JSON_TABLE(myJSON, '$[*]'
      COLUMNS (
        CUST_NUM INT PATH '$.CUST_NUM',
        SORT_ORDER INT PATH '$.SORT_ORDER',
        CATEGORY VARCHAR2(100) PATH '$.CATEGORY'
      )
    )
  ) j
  ON (t.CUST_NUM = j.CUST_NUM AND t.SORT_ORDER = j.SORT_ORDER)
  WHEN MATCHED THEN
    UPDATE SET t.CATEGORY = j.CATEGORY;
END;
/

代码说明

• JSON_TABLE：将JSON数组解析为关系型数据集，提取每个对象的字段值；
• MERGE：通过ON子句匹配目标表与JSON解析出的数据集，匹配成功时执行UPDATE操作，可根据需求添加多个字段到SET子句中。

Python cx_Oracle 执行批量更新 #

通过Python传递JSON参数执行更新，避免硬编码，适合动态生成的JSON数据：

import cx_Oracle
import json

# 数据库连接配置
dsn = cx_Oracle.makedsn("你的主机名", "端口", service_name="服务名")
connection = cx_Oracle.connect(user="用户名", password="密码", dsn=dsn)

try:
    cursor = connection.cursor()
    # 准备1000条更新数据（示例仅3条）
    update_data = [
        {"CUST_NUM": 12345, "SORT_ORDER": 1, "CATEGORY": "FROZEN YOGURT"},
        {"CUST_NUM": 12345, "SORT_ORDER": 2, "CATEGORY": "SORBET"},
        {"CUST_NUM": 12345, "SORT_ORDER": 3, "CATEGORY": "GELATO"}
        # 更多条目...
    ]
    json_str = json.dumps(update_data)
    
    # 执行MERGE语句
    merge_sql = """
        MERGE INTO jt_test t
        USING (
            SELECT CUST_NUM, SORT_ORDER, CATEGORY
            FROM JSON_TABLE(:json_data, '$[*]'
                COLUMNS (
                    CUST_NUM INT PATH '$.CUST_NUM',
                    SORT_ORDER INT PATH '$.SORT_ORDER',
                    CATEGORY VARCHAR2(100) PATH '$.CATEGORY'
                )
            )
        ) j
        ON (t.CUST_NUM = j.CUST_NUM AND t.SORT_ORDER = j.SORT_ORDER)
        WHEN MATCHED THEN
            UPDATE SET t.CATEGORY = j.CATEGORY
    """
    cursor.execute(merge_sql, json_data=json_str)
    connection.commit()
    print(f"成功更新 {cursor.rowcount} 条记录")
finally:
    cursor.close()
    connection.close()

注意事项 #

• 必须保证CUST_NUM与SORT_ORDER的组合唯一，否则会导致多条记录被同一JSON条目更新，引发数据混乱；
• 若JSON数据量较大（如1000条），PL/SQL中建议用CLOB类型存储JSON，避免varchar2长度不足；
• Python端cx_Oracle会自动处理字符串长度，只要JSON数据不超过Oracle允许的CLOB上限即可。

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