幕雪

cursor如何更新是指哪种“更新”？如何通过游标更新数据？为什么使用这种方式？需要满足哪些前提？





在数据库编程和操作中，“更新cursor”这个说法可能会引起一些误解。它通常不是指改变游标本身的定义或刷新游标已经读取的数据集（标准游标通常是静态的，反映打开时的状态），而是指通过游标来更新游标当前指向的数据库中的数据行。

本文将围绕这一核心概念，详细解释如何在数据库环境（特别是关系型数据库，如SQL、PL/SQL、T-SQL等）中使用游标来更新数据，以及相关的背景、原因、方法和注意事项。

是指哪种“更新”？

当讨论“cursor如何更新”时，几乎总是特指通过游标来修改该游标当前指向的、数据库表中的某一行数据。换句话说，是更新游标所定位到的那条记录的内容，而不是更新游标的定义、查询结果集或者游标的位置（游标位置是通过FETCH操作移动的）。

这种更新操作是针对游标当前正在处理的单一行进行的，这与我们平时使用一条独立的UPDATE语句来更新多行（甚至整个表）是不同的。

如何通过游标更新数据？

通过游标更新数据的核心机制是使用UPDATE ... WHERE CURRENT OF cursor_name;语句。为了能够使用这个语句，游标的声明必须满足特定条件。

前提条件：使用 FOR UPDATE 声明游标

不是所有游标都可以用来更新数据。如果打算通过游标修改其指向的数据行，必须在声明游标时明确指定其用于更新。这通常通过在游标查询语句的末尾加上FOR UPDATE [OF column_list] [NOWAIT | WAIT n]子句来实现。

FOR UPDATE子句的作用：

  1. 告诉数据库，这个游标将用于后续的更新或删除操作。
  2. 当游标FETCH（获取）一行数据时，数据库会锁定这一行，防止其他事务在当前事务处理期间修改或删除它，从而确保通过WHERE CURRENT OF执行更新时，该行数据是稳定的。
  3. OF column_list (可选)：指定哪些列的更新需要被锁定。如果省略，则锁定所有列的更新。
  4. NOWAIT 或 WAIT n (可选)：指定如果需要锁定的行已经被其他事务锁定，当前操作是立即返回错误（NOWAIT）还是等待n秒（WAIT n）或者无限期等待（省略WAIT/NOWAIT，默认行为通常是等待）。

示例（PL/SQL）：


DECLARE
CURSOR emp_cursor IS
SELECT employee_id, salary, status
FROM employees
WHERE status = 'PENDING'
FOR UPDATE OF salary NOWAIT;

v_emp_id employees.employee_id%TYPE;
v_salary employees.salary%TYPE;
v_status employees.status%TYPE;

BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO v_emp_id, v_salary, v_status;
EXIT WHEN emp_cursor%NOTFOUND;

-- 假设这里有一些复杂的逻辑，根据条件决定是否更新工资
IF v_status = 'PENDING' AND v_salary < 5000 THEN
DBMS_OUTPUT.PUT_LINE('Updating salary for employee ' || v_emp_id);

-- **通过游标更新当前行**
UPDATE employees
SET salary = v_salary * 1.10, status = 'PROCESSED'
WHERE CURRENT OF emp_cursor;

END IF;
END LOOP;

CLOSE emp_cursor;
COMMIT; -- 提交所有通过游标进行的更新

EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- 如果发生错误，回滚所有操作
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
IF emp_cursor%ISOPEN THEN
CLOSE emp_cursor;
END IF;
RAISE;
END;
/

解释上面的代码流程：

  1. 声明一个名为emp_cursor的游标，查询employees表中状态为’PENDING’的员工，并使用FOR UPDATE OF salary NOWAIT锁定将要获取的行的salary列进行更新。
  2. 打开游标。
  3. 进入循环，每次从游标中获取一行数据到变量中。
  4. 判断是否已获取完所有行，如果是则退出循环。
  5. 在循环内部，针对当前获取到的行执行特定的业务逻辑。
  6. 使用UPDATE employees SET ... WHERE CURRENT OF emp_cursor;语句。这里的关键是WHERE CURRENT OF emp_cursor，它指示数据库更新的不是满足某个WHERE条件的行，而是当前emp_cursor游标定位到的那一行。
  7. 循环继续，直到所有行都被处理。
  8. 关闭游标。
  9. 提交事务，使所有的更新永久生效。如果在处理过程中发生任何错误，则回滚事务。

更新的是哪一行？

UPDATE ... WHERE CURRENT OF cursor_name;语句总是只更新游标在执行FETCH操作后定位到的那单独一行。每次FETCH移动游标，WHERE CURRENT OF就针对新的当前行执行操作。

为什么要使用游标更新而不是直接更新？

虽然对于大多数批量数据修改场景，使用一条简单的UPDATE table SET ... WHERE condition;语句通常效率最高且最推荐，但在某些特定情况下，使用游标进行行级更新是有其原因的：

  • 复杂的行级处理逻辑： 如果更新决策或计算依赖于当前行的多个列，或者需要在更新前后执行一些附加的、无法用纯SQL直接表达的操作（例如调用外部过程、记录详细日志、与其他系统交互等），那么游标提供了一个按行处理的框架，可以在获取每一行后，在编程语言的控制结构（如IF语句、CASE语句）中实现这些复杂的逻辑，然后才决定是否以及如何更新该行。
  • 结合程序控制流： 当更新操作是大型存储过程或函数中的一部分，并且该过程已经需要按行迭代处理数据时，利用现有游标结构来执行更新是顺理成章的。
  • 分批处理： 在某些非常巨大的表上，为了避免单个大事务导致的锁定问题或回滚段溢出，有时会使用游标配合COMMIT语句在处理一定数量的行后进行提交（但这需要非常小心地设计，以避免数据不一致或其他并发问题）。不过，现代数据库提供了更好的大批量处理机制，如批量DML（BULK COLLECT和FORALL）或表分区并行处理，这些通常比游标逐行提交更优。

重要区别：

  • 游标更新是行级（Row-by-row）处理。
  • 直接SQL更新是集级（Set-based）处理。

集级处理通常由数据库引擎高度优化，可以更有效地利用资源（如一次读取多个数据块，批量应用修改），因此对于简单的批量更新，直接SQL更新的性能通常远优于游标更新。

需要满足哪些前提？

成功通过游标更新数据需要满足几个关键前提：

  1. 游标必须是可更新的： 声明游标时必须包含FOR UPDATE子句。这是最基本也是最重要的前提。
  2. 游标查询必须是可更新的： 游标的SELECT语句不能过于复杂，例如通常不能包含：
    • 聚合函数（COUNT, SUM, AVG等）。
    • GROUP BY或HAVING子句。
    • DISTINCT关键字。
    • JOIN（某些数据库在特定条件下支持可更新视图或JOIN的更新，但直接基于多表JOIN的游标通常不可更新）。
    • 集合操作符（UNION, INTERSECT, EXCEPT）。
    • 基于复杂表达式或函数的伪列。
    • 从多个基表选择列（除非是通过可更新视图）。

    简单来说，游标查询结果中的每一行必须能够清晰地、无歧义地映射回一个可更新的基表中的某一行。

  3. WHERE CURRENT OF语句必须在FETCH之后，下次FETCH或关闭游标之前执行： WHERE CURRENT OF cursor_name子句依赖于游标当前的有效位置。在成功获取一行数据后，游标就定位到了那一行，此时执行更新操作才是有效的。一旦执行了下一次FETCH，游标位置移动，或者游标被关闭，就不能再使用WHERE CURRENT OF来更新之前获取的行了。
  4. 执行更新的用户必须具有目标表的UPDATE权限。
  5. 并发控制： 如果其他事务正在尝试锁定或修改游标将要处理的行，使用FOR UPDATE时的NOWAIT或WAIT选项将决定程序的行为（立即错误、等待）。需要根据应用场景选择合适的锁定策略并处理可能的锁定冲突异常。

与直接SQL更新有何不同？

再次强调游标更新与直接SQL更新的主要区别：

  • 处理方式：
    • 游标更新： 逐行处理，在程序逻辑控制下对每一行单独进行判断和更新。
    • 直接SQL更新： 集体处理，一条语句根据WHERE条件一次性对所有符合条件的行进行更新。
  • 性能：
    • 游标更新： 通常涉及更多的数据库交互（FETCH、UPDATE each row），开销较大，对于更新大量行效率低下。
    • 直接SQL更新： 数据库引擎能够优化整个操作，通常性能远高于游标更新，特别是对于大量数据的更新。
  • 事务和锁定：
    • 游标更新： 使用FOR UPDATE在FETCH时锁定单行或一组行。COMMIT/ROLLBACK控制整个游标操作期间所有单行更新组成的事务。
    • 直接SQL更新： 在语句执行前锁定所有需要更新的行，整个更新操作作为一个原子事务单元（除非显式控制）。
  • 适用场景：
    • 游标更新： 需要复杂的行级逻辑判断和处理，或者更新操作与现有的行级处理流程紧密耦合。
    • 直接SQL更新： 大多数批量数据修改场景，简单的基于条件的更新。
  • 语法：
    • 游标更新： 需要DECLARE CURSOR ... FOR UPDATE, OPEN, FETCH循环, UPDATE ... WHERE CURRENT OF cursor_name, CLOSE。
    • 直接SQL更新： 单个UPDATE table_name SET ... WHERE condition;语句。

总而言之，通过游标更新数据是一种强大的、允许在行级应用复杂逻辑的数据库操作方式，但由于其逐行处理的特性，在性能上通常不如直接的集级更新语句。因此，应根据具体的业务需求和数据量大小权衡选择最合适的方法。在绝大多数情况下，如果可以通过一条或几条集级SQL语句完成的更新，都应该优先考虑使用它们而不是游标。


cursor如何更新