PL/SQL批量更新XML格式字符串 为所有index标签头部插入新标签

处理这类XML结构修改需求，优先使用Oracle原生XML处理函数，避免字符串替换可能导致的XML格式损坏问题，以下是不同场景的可直接使用的方案：

方案1：普通批量更新（数据量100万以下推荐） #

假设你的表名为YOUR_TABLE，存储XML的列名为XML_COL，表主键为ID，执行以下UPDATE语句即可：

UPDATE YOUR_TABLE
SET XML_COL = XMLQUERY(
  'copy $d := . 
   modify (
     -- 遍历所有index节点，在节点最开头插入新标签
     for $i in $d/indexes/index
     return insert node element NEW_TAG {"abcdef"} as first into $i
   )
   return $d'
  -- 如果你的列本身就是XMLTYPE类型，直接写XML_COL即可，不需要XML_TYPE()转换
  PASSING XML_TYPE(XML_COL)
  RETURNING CONTENT
-- 如果列是XMLTYPE类型，去掉末尾的.getClobVal()
).getClobVal();
COMMIT;

如果需要动态传标签名和值，可以调整为参数化写法：

UPDATE YOUR_TABLE
SET XML_COL = XMLQUERY(
  'copy $d := . 
   modify (
     for $i in $d/indexes/index
     return insert node element {$tag_name} {$tag_val} as first into $i
   )
   return $d'
  PASSING 
    XML_TYPE(XML_COL) AS ".",
    'NEW_TAG' AS "tag_name",
    'abcdef' AS "tag_val"
  RETURNING CONTENT
).getClobVal();
COMMIT;

方案2：大数量分批提交（数据量100万以上推荐） #

如果数据量极大，为了避免锁表、UNDO表空间溢出，可以使用分批提交的PL/SQL块：

DECLARE
  -- 每批次提交行数，可根据库性能调整，推荐1000-10000之间
  BATCH_SIZE CONSTANT PLS_INTEGER := 2000;
  CURSOR cur_xml_data IS
    SELECT ID, XML_COL
    FROM YOUR_TABLE
    -- 可自行添加WHERE条件过滤需要更新的行
    FOR UPDATE;
  TYPE rec_xml IS RECORD (id YOUR_TABLE.id%TYPE, xml_content YOUR_TABLE.XML_COL%TYPE);
  TYPE tab_xml IS TABLE OF rec_xml INDEX BY PLS_INTEGER;
  l_xml_tab tab_xml;
BEGIN
  OPEN cur_xml_data;
  LOOP
    FETCH cur_xml_data BULK COLLECT INTO l_xml_tab LIMIT BATCH_SIZE;
    EXIT WHEN l_xml_tab.COUNT = 0;
    
    FORALL i IN 1..l_xml_tab.COUNT
      UPDATE YOUR_TABLE
      SET XML_COL = XMLQUERY(
        'copy $d := . 
         modify (
           for $i in $d/indexes/index
           return insert node element NEW_TAG {"abcdef"} as first into $i
         )
         return $d'
        PASSING XML_TYPE(l_xml_tab(i).xml_content)
        RETURNING CONTENT
      ).getClobVal()
      WHERE ID = l_xml_tab(i).id;
    
    COMMIT;
  END LOOP;
  CLOSE cur_xml_data;
END;
/

验证建议 #

正式执行更新前，先执行以下查询校验更新结果是否符合预期：

SELECT 
  XMLQUERY(
    'copy $d := . 
     modify (
       for $i in $d/indexes/index
       return insert node element NEW_TAG {"abcdef"} as first into $i
     )
     return $d'
    PASSING XML_TYPE(XML_COL)
    RETURNING CONTENT
  ).getClobVal() AS updated_xml
FROM YOUR_TABLE
WHERE ROWNUM <= 10;

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