Oracle中如何用正则更新TEST表存储的SQL并移除partition_table.frequency条件

场景说明 #

• 待操作的表为Oracle中的TEST表，共包含3个字段：Test_num、src_qry、tgt qry，其中src_qry和tgt qry字段存储测试用的Oracle SQL语句，SQL中会涉及customer、relation、transaction、partition_table等业务表
• 需求是批量移除src_qry、tgt qry字段中所有和partition_table的frequency列相关的过滤条件（比如frequency='D'、pt.frequency='M'这类写法），保留SQL其余逻辑完全不变

实现步骤 #

1. 前置操作：备份原表 #

执行更新前必须先备份全表数据，避免正则匹配误差导致数据损坏：

CREATE TABLE TEST_2024XXXX_BAK AS SELECT * FROM TEST;

2. 验证替换效果（必须先执行该步骤确认替换结果符合预期） #

用SELECT语句预览替换前后的SQL差异，确认没有误删其他逻辑：

SELECT
  Test_num,
  src_qry old_src,
  REGEXP_REPLACE(
    REGEXP_REPLACE(src_qry, '(AND|OR)\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*', '', 1, 0, 'i'),
    'WHERE\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*(AND|OR)?', 'WHERE ', 1, 0, 'i'
  ) new_src,
  "tgt qry" old_tgt,
  REGEXP_REPLACE(
    REGEXP_REPLACE("tgt qry", '(AND|OR)\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*', '', 1, 0, 'i'),
    'WHERE\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*(AND|OR)?', 'WHERE ', 1, 0, 'i'
  ) new_tgt
FROM TEST
WHERE REGEXP_LIKE(src_qry, '[a-zA-Z0-9_]*\.frequency\s*=', 'i')
OR REGEXP_LIKE("tgt qry", '[a-zA-Z0-9_]*\.frequency\s*=', 'i');

正则说明：两层替换分别处理过滤条件在AND/OR之后、在WHERE之后的场景，同时兼容表别名、大小写不敏感的SQL写法

3. 执行更新操作 #

确认预览结果符合要求后，执行UPDATE语句完成批量更新：

UPDATE TEST
SET
  src_qry = REGEXP_REPLACE(
    REGEXP_REPLACE(src_qry, '(AND|OR)\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*', '', 1, 0, 'i'),
    'WHERE\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*(AND|OR)?', 'WHERE ', 1, 0, 'i'
  ),
  "tgt qry" = REGEXP_REPLACE(
    REGEXP_REPLACE("tgt qry", '(AND|OR)\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*', '', 1, 0, 'i'),
    'WHERE\s+[a-zA-Z0-9_]*\.frequency\s*=\s*''[A-Za-z0-9]+''\s*(AND|OR)?', 'WHERE ', 1, 0, 'i'
  )
WHERE REGEXP_LIKE(src_qry, '[a-zA-Z0-9_]*\.frequency\s*=', 'i')
OR REGEXP_LIKE("tgt qry", '[a-zA-Z0-9_]*\.frequency\s*=', 'i');

COMMIT;

内容的提问来源于stack exchange，提问作者User 15849903 Raj