Oracle中如何更新CLOB字段内JSON的HREF属性值？

针对你需要更新TEST_CLOB表中CLOB类型字段IMPORTDATA内HREF属性为"unknown"到"-1"的需求，分两种场景给出解决方案：

场景1：Oracle 12cR2及以上版本（推荐） #

利用Oracle原生的JSON操作函数，基于JSON结构精准修改属性，避免字符串替换的误操作：

UPDATE TEST_CLOB
SET IMPORTDATA = JSON_SET(IMPORTDATA, '$.HREF', '-1')
WHERE JSON_EXISTS(IMPORTDATA, '$.HREF?(@ == "unknown")');

语句说明： #

• JSON_SET：用于修改JSON对象中指定路径的属性值，第一个参数是CLOB类型的JSON数据，第二个参数是属性路径$.HREF，第三个参数是新值-1
• JSON_EXISTS：筛选出HREF属性值为"unknown"的记录，只更新符合条件的行，提升效率

场景2：Oracle版本低于12cR2 #

如果不支持JSON函数，可使用字符串替换方式（注意：仅当JSON格式规范且"HREF":"unknown"不会出现在其他属性值或注释中时使用）：

UPDATE TEST_CLOB
SET IMPORTDATA = REPLACE(IMPORTDATA, '"HREF":"unknown"', '"HREF":"-1"')
WHERE DBMS_LOB.INSTR(IMPORTDATA, '"HREF":"unknown"') > 0;

注意事项： #

• 优先使用JSON函数方案，字符串替换存在误匹配风险（比如其他属性值包含相同字符串）
• 若表数据量较大，建议分批更新（比如加ROWNUM限制），避免长时间锁表影响业务
• 操作前建议备份数据，或先执行SELECT语句验证筛选结果：

  -- 验证JSON函数方案的筛选结果
  SELECT * FROM TEST_CLOB WHERE JSON_EXISTS(IMPORTDATA, '$.HREF?(@ == "unknown")');
  
  -- 验证字符串替换方案的筛选结果
  SELECT * FROM TEST_CLOB WHERE DBMS_LOB.INSTR(IMPORTDATA, '"HREF":"unknown"') > 0;
  

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