Oracle 19C中FOR循环内ROLLBACK引发ORA-01002错误的原因咨询

问题场景 #

我在Oracle Database 19C中编写了如下存储过程：

Declare
 i integer;
BEGIN
  Delete from t_invent;
  -- 若在此处添加COMMIT或删除这条DELETE语句，错误不会触发

  FOR r_in IN (SELECT * FROM t_in) LOOP
  
    -- 插入数据到目标表
    INSERT INTO t_invent (item_id, item_name) VALUES (r_in.item_id, r_in.item_name);
    
    -- 满足条件时回滚事务
    if r_in.count = 90 then
       ROLLBACK; 
       -- 执行此处ROLLBACK会触发ORA-01002: Fetch out of Sequence错误
    end if;
    
  END LOOP;

END;

测试现象 #

• 在FOR循环前执行COMMIT，或删除开头的DELETE语句，ORA-01002错误不会出现
• 循环内触发ROLLBACK后，过程会抛出该错误，有时还会继续执行约12条数据后才崩溃
• 循环内每50条数据执行COMMIT可规避错误，但将提交间隔增至100条时错误重现

疑问 #

根据Oracle官方文档，游标失效会引发此错误。我推测ROLLBACK会回滚包括开头DELETE在内的事务，但并未对t_in表执行任何DML操作，不解为何游标会失效。

问题原因 #

这是因为Oracle的隐式游标（FOR循环自动创建的游标）与当前事务上下文绑定，当执行ROLLBACK时，会直接破坏游标依赖的一致性快照：

1. 开头的DELETE开启了一个事务，FOR循环的隐式游标基于该事务的一致性快照建立，用来保证读取数据的事务一致性
2. ROLLBACK不仅回滚了INSERT和DELETE的修改，还会销毁当前事务的一致性快照，导致绑定的隐式游标失效
3. 提前COMMIT或删除DELETE时，游标是在无活跃事务的状态下创建的，此时Oracle使用独立的只读快照，不受后续ROLLBACK影响
4. 循环能继续执行12条数据，是因为Oracle会预取一批游标数据（默认预取量），缓存数据耗尽后，再尝试从数据库获取数据时就会检测到游标失效
5. 每50条COMMIT有效，是因为每次提交会结束当前事务，游标会在新事务下重新获取数据；而100条时错误重现，说明预取数据量小于100，ROLLBACK发生在预取数据用完前，后续获取便会失败

解决方案 #

方案1：使用保存点替代全事务回滚 #

如果仅需要回滚单条插入操作，不要使用全事务ROLLBACK，改用保存点回滚到指定位置，不会破坏整个事务和游标：

Declare
 i integer;
BEGIN
  Delete from t_invent;

  FOR r_in IN (SELECT * FROM t_in) LOOP
    SAVEPOINT sp_before_insert;
    INSERT INTO t_invent (item_id, item_name) VALUES (r_in.item_id, r_in.item_name);
    
    if r_in.count = 90 then
       ROLLBACK TO SAVEPOINT sp_before_insert; 
    end if;
    
  END LOOP;
  COMMIT; -- 最后统一提交事务
END;

方案2：显式管理游标（需处理重复读取风险） #

如果必须在循环内执行提交/回滚，可显式声明游标，在事务结束后重新打开游标（需配合行级锁或唯一标识避免重复读取数据）：

Declare
  CURSOR c_in IS SELECT * FROM t_in;
  r_in c_in%ROWTYPE;
BEGIN
  Delete from t_invent;
  OPEN c_in;
  LOOP
    FETCH c_in INTO r_in;
    EXIT WHEN c_in%NOTFOUND;
    
    INSERT INTO t_invent (item_id, item_name) VALUES (r_in.item_id, r_in.item_name);
    
    if r_in.count = 90 then
       ROLLBACK;
       CLOSE c_in;
       OPEN c_in; -- 重新打开游标，基于新事务快照读取
    end if;
  END LOOP;
  CLOSE c_in;
  COMMIT;
END;

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