PL/SQL中用SYS_REFCURSOR+SELECT FOR UPDATE遇ORA-01002报错求助

这个错误的根源非常清晰——你在使用FOR UPDATE锁定行的游标循环过程中，每次更新一行就执行COMMIT，这直接导致了游标失效！

错误原因详解 #

当你打开带有FOR UPDATE的游标时，Oracle会对查询到的行加排他锁，同时游标会维护一个与这些锁定行关联的上下文。一旦执行COMMIT：

1. 所有被锁定的行都会释放锁
2. 游标与数据库的关联上下文被销毁，游标变为无效状态
   此时后续的FETCH操作就会触发ORA-01002: fetch out of sequence异常，因为游标已经无法再访问之前的结果集了。

另外你的代码还有两个小问题：

• 循环中没有EXIT语句，当游标%notfound时仅设置了cnumber，会导致无限循环
• 输入参数a_id被用作FETCH的目标变量，这会覆盖输入参数的值，应该使用一个本地变量（比如a_vid）

解决办法 #

方案1：统一提交（推荐，适合数据量不大的场景）

把COMMIT移到循环外部，等所有行更新完成后一次性提交，这样游标在整个循环过程中始终保持有效：

PROCEDURE get_rows( a_cursor OUT SYS_REFCURSOR, a_id IN VARCHAR, a_count IN NUMBER) IS
    a_vid mytable.VID%TYPE; -- 定义本地变量存储VID
    cnumber NUMBER;
BEGIN
    OPEN a_cursor FOR 
        SELECT mytable.VID FROM mytable WHERE ROWNUM <= a_count FOR UPDATE; 
    
    LOOP
        FETCH a_cursor INTO a_vid; 
        
        IF a_cursor%NOTFOUND THEN
            cnumber := 9999;
            EXIT; -- 必须退出循环，避免无限循环
        ELSE
            UPDATE mytable SET ... WHERE VID = a_vid; 
            -- 这里不要COMMIT
        END IF;
    END LOOP;
    
    COMMIT; -- 所有更新完成后统一提交
    CLOSE a_cursor; -- 记得关闭游标
END get_rows;

方案2：批量处理+分批提交（适合大数据量场景）

如果业务上必须分批提交（比如避免回滚段溢出），可以使用BULK COLLECT批量抓取数据，结合FORALL批量更新，每处理一批数据后提交一次：

PROCEDURE get_rows( a_cursor OUT SYS_REFCURSOR, a_id IN VARCHAR, a_count IN NUMBER) IS
    TYPE vid_table_type IS TABLE OF mytable.VID%TYPE;
    v_vids vid_table_type;
    v_batch_size CONSTANT NUMBER := 100; -- 每批次处理100行，可根据实际调整
BEGIN
    OPEN a_cursor FOR 
        SELECT mytable.VID FROM mytable WHERE ROWNUM <= a_count FOR UPDATE; 
    
    LOOP
        -- 批量抓取数据
        FETCH a_cursor BULK COLLECT INTO v_vids LIMIT v_batch_size;
        EXIT WHEN v_vids.COUNT = 0;
        
        -- 批量更新，效率比单条更新高很多
        FORALL i IN 1..v_vids.COUNT
            UPDATE mytable SET ... WHERE VID = v_vids(i);
        
        COMMIT; -- 每批次提交一次
    END LOOP;
    
    CLOSE a_cursor;
END get_rows;

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