Oracle游标中ROWNUM未按预期顺序输出的问题咨询

我编写了一个存储过程，通过遍历游标生成SQL语句，希望添加按100、200、300递增的行进度计数器。相关代码如下：

CURSOR c_data IS
SELECT ROWNUM AS rn, <data columns>
FROM <table>
ORDER BY <columns>;

FOR r_data IN c_data LOOP
  <write statement>
  IF MOD(r_data.rn, 100) = 0 THEN
    <write counter>
  END IF;
END LOOP;

但输出的计数器顺序不符合预期，始终是300、100、200、800、400、500、600、700这样的乱序。请问ROWNUM未按顺序输出的原因是什么？

更新说明：已了解频繁提交是不良实践，会关注SQL性能问题，但仍需保留进度计数器。

原因分析 #

核心问题在于Oracle中ROWNUM的生成时机早于ORDER BY执行：

• Oracle的执行逻辑是先从基表中读取数据，为每条返回的记录分配ROWNUM值；
• 完成ROWNUM分配后，才会执行ORDER BY对结果集进行排序；
• 这就导致排序后的结果里，原本的ROWNUM顺序被彻底打乱，遍历游标时拿到的rn自然是无序的，最终进度计数器输出乱序。

修正方案 #

要让ROWNUM按排序后的顺序生成，需要先完成排序，再分配ROWNUM，将排序逻辑放到子查询中：

CURSOR c_data IS
SELECT ROWNUM AS rn, t.*
FROM (
  -- 先执行排序
  SELECT <data columns>
  FROM <table>
  ORDER BY <columns>
) t;

FOR r_data IN c_data LOOP
  <write statement>
  IF MOD(r_data.rn, 100) = 0 THEN
    <write counter>
  END IF;
END LOOP;

内层子查询先完成数据排序，外层查询再为排序后的结果分配连续递增的ROWNUM，此时遍历游标时rn会按排序后的顺序依次递增，进度计数器就能正常按100、200、300的顺序输出。

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