Oracle表列已设首行值为V0001，如何批量更新剩余行生成连续序列

前提说明 #

你需要先确定行的排序规则，即你预期哪一行对应V0002、哪一行对应V0003，可以用表的主键、创建时间、ROWID等唯一且稳定的字段作为排序依据。

操作步骤 #

1. 先验证序列生成是否符合预期 #

执行以下SELECT语句，确认生成的序列值和行的对应关系符合你的要求：

SELECT 
  seq_col 原有值,
  'V' || LPAD(ROW_NUMBER() OVER(ORDER BY 你的排序字段 ASC),4,'0') 生成的序列值,
  ROWID
FROM your_table
ORDER BY 你的排序字段 ASC;

替换说明：

• seq_col替换为你要更新的列名
• your_table替换为你的实际表名
• 你的排序字段替换为你用来确定行顺序的字段，不确定排序规则时可以直接写ROWID

2. 执行更新操作 #

确认生成的序列符合预期后，用以下MERGE语句批量更新，会自动跳过已经设置为V0001的第一行：

MERGE INTO your_table t1
USING (
  SELECT 
    ROWID row_id,
    'V' || LPAD(ROW_NUMBER() OVER(ORDER BY 你的排序字段 ASC),4,'0') new_seq
  FROM your_table
) t2
ON (t1.ROWID = t2.row_id AND t1.seq_col != 'V0001')
WHEN MATCHED THEN UPDATE SET t1.seq_col = t2.new_seq;

执行完成后提交事务即可。

补充说明 #

• 如果你需要序列严格从V0002开始给未更新的行赋值，可以把窗口函数部分修改为：'V' || LPAD(1 + ROW_NUMBER() OVER(ORDER BY 你的排序字段 ASC),4,'0')，同时WHERE条件过滤掉已经是V0001的行即可。
• Oracle 12c及以上版本也可以通过创建临时序列配合UPDATE实现，上述MERGE方案兼容所有主流Oracle版本，无需额外创建数据库对象，执行完没有残留。

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