Oracle PL/SQL：集合为空时FORALL仍显示更新1行的问题排查

问题场景 #

你编写的PL/SQL块执行后出现不符合预期的结果：

• 集合lst的计数为0，理论上FORALL循环不会执行任何UPDATE操作
• 但SQL%ROWCOUNT却显示更新了1行
• 移除my_sequence.NEXTVAL的赋值后，SQL%ROWCOUNT恢复为0，符合预期

对应的代码如下：

DECLARE  
  TYPE t_list IS TABLE OF my_table.key%TYPE;  
  lst t_list := t_list();  
  cur SYS_REFCURSOR;  
  var NUMBER; -- Holds sequence value  

BEGIN  
  -- Assign sequence value  
  var := my_sequence.NEXTVAL;  

  -- Open and fetch cursor  
  OPEN cur FOR  
    SELECT key FROM my_table WHERE some_conditions;  

  FETCH cur BULK COLLECT INTO lst;  
  CLOSE cur;  

  -- Debug output  
  DBMS_OUTPUT.PUT_LINE('lst.COUNT: ' || lst.COUNT); -- Prints 0  

  -- FORALL update  
  FORALL I IN 1..lst.COUNT  
    UPDATE my_table  
    SET col = SYSDATE  
    WHERE key = lst(I)  
    AND status = 'A';  

  DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT); -- Prints 1  

END;

疑问解答 #

1. 为何FORALL未执行时SQL%ROWCOUNT仍显示1？ #

SQL%ROWCOUNT记录的是最后一次执行的SQL操作的行数。你的代码中FORALL循环的范围是1..0，属于空范围，循环根本不会执行UPDATE语句。此时最后一次执行的SQL操作是my_sequence.NEXTVAL的调用，序列的NEXTVAL每次调用都会生成一个值，对应的SQL操作行数固定为1，因此SQL%ROWCOUNT会保留这个结果。

2. sequence.NEXTVAL是否会影响SQL%ROWCOUNT？ #

是的。调用序列的NEXTVAL或CURRVAL本质是执行了一个SQL操作，Oracle会将该操作的行数（始终为1，因为每次调用仅生成一个序列值）写入SQL%ROWCOUNT。如果后续没有其他SQL操作覆盖这个值，SQL%ROWCOUNT就会一直保留这个结果。

3. 如何确保SQL%ROWCOUNT仅反映FORALL更新的行数？ #

可以通过以下几种可靠方式实现：

方法一：根据集合计数判断

当lst.COUNT = 0时，FORALL肯定不会执行任何更新，此时直接将更新行数视为0；当lst.COUNT > 0时，SQL%ROWCOUNT的值就是FORALL实际更新的行数。修改后的调试输出逻辑如下：

DECLARE  
  -- 原有声明部分不变
  update_count NUMBER;
BEGIN  
  -- 原有执行逻辑不变

  -- 获取实际更新行数
  IF lst.COUNT = 0 THEN
    update_count := 0;
  ELSE
    update_count := SQL%ROWCOUNT;
  END IF;

  DBMS_OUTPUT.PUT_LINE('实际更新行数: ' || update_count);
END;

方法二：在FORALL前执行空SQL操作重置SQL%ROWCOUNT

在FORALL语句之前，执行一个不会影响数据的SQL操作，将SQL%ROWCOUNT重置为0。比如执行一个永远不会匹配行的UPDATE：

-- 在FORALL之前添加以下代码
UPDATE my_table SET col = col WHERE 1 = 0;

-- FORALL语句及后续输出不变
FORALL I IN 1..lst.COUNT
  UPDATE my_table
  SET col = SYSDATE
  WHERE key = lst(I)
  AND status = 'A';

DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: ' || SQL%ROWCOUNT);

这个空UPDATE的SQL%ROWCOUNT为0，之后如果FORALL执行了更新，会覆盖这个值；如果FORALL未执行，SQL%ROWCOUNT会保持0，符合预期。

方法三：使用RETURNING子句捕获更新行

通过FORALL的RETURNING子句，将更新的行信息收集到集合中，集合的计数就是实际更新的行数：

DECLARE  
  TYPE t_list IS TABLE OF my_table.key%TYPE;  
  lst t_list := t_list();  
  updated_keys t_list := t_list(); -- 用于存储更新的键值
  cur SYS_REFCURSOR;  
  var NUMBER;  

BEGIN  
  var := my_sequence.NEXTVAL;  

  OPEN cur FOR
    SELECT key FROM my_table WHERE some_conditions;
  FETCH cur BULK COLLECT INTO lst;
  CLOSE cur;

  DBMS_OUTPUT.PUT_LINE('lst.COUNT: ' || lst.COUNT);

  FORALL I IN 1..lst.COUNT
    UPDATE my_table
    SET col = SYSDATE
    WHERE key = lst(I)
    AND status = 'A'
    RETURNING key BULK COLLECT INTO updated_keys; -- 捕获更新的键

  DBMS_OUTPUT.PUT_LINE('实际更新行数: ' || updated_keys.COUNT);
END;

这种方法最直观，直接通过集合的计数得到实际更新的行数，不受其他SQL操作的影响。

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