可更新游标

语法

UPDATE  [ [ AS ]  ]
    SET 
    WHERE CURRENT OF 

DELETE FROM  [ [ AS ]  ]
    WHERE CURRENT OF 

描述

当遍历结果集的每一行时，可以使用可更新游标直接在游标指向的行上更改记录。可更新游标是标准 SQL 功能 (ISO/IEC 9075-2:2011)。

有关详细信息，请参见 SQL 标准文档 (ISO/IEC 9075-2:2011) 的第 14.8 和 14.13 节。

限制

以下限制适用：

• 必须使用包括 FOR UPDATE 子句的 SELECT 语句声明游标。如果没有 FOR UPDATE，游标将不可更新，并且无法防止对表执行并发的 WRITE 操作。

• 可更新游标只能用于 UPDATE 和 DELETE 操作。

• 禁止在单个查询中使用可更新游标；它必须在 SQLScript 中使用。

• 只有持久表（ROW 和 COLUMN 表）可以使用可更新游标进行更新。不支持全局和本地临时表以及分区表。

• 通过可更新游标对表执行的 UPDATE 或 DELETE 操作每行只允许执行一次。

• 自主事务不能在涉及可更新游标的循环中使用。

  备注如果在单个事务中声明选择同一表的多个游标，则可以多次更新同一行。

  示例

  使用可更新游标更新单个表的示例： 示例代码CREATE TABLE employees (employee_id INTEGER, employee_name VARCHAR(30));INSERT INTO employees VALUES (1, 'John');INSERT INTO employees VALUES (20010, 'Sam');INSERT INTO employees VALUES (21, 'Julie');INSERT INTO employees VALUES (10005, 'Kate'); DO BEGIN DECLARE CURSOR cur FOR SELECT * FROM employees FOR UPDATE; FOR r AS cur DO IF r.employee_id < 10000 THEN UPDATE employees SET employee_id = employee_id + 10000 WHERE CURRENT OF cur; ELSE DELETE FROM employees WHERE CURRENT OF cur; END IF; END FOR;END;

  通过可更新游标更新或删除多个表（当前仅支持 COLUMN 表）的示例。

  备注在这种情况下，您必须通过在游标的 SELECT 语句中使用 FOR UPDATE OF 子句来指定要锁定的表列。请记住，通过可更新游标进行的 DML 执行每行只允许执行一次。 示例代码CREATE COLUMN TABLE employees (employee_id INTEGER, employee_name VARCHAR(30), department_id INTEGER);INSERT INTO employees VALUES (1, 'John', 1);INSERT INTO employees VALUES (2, 'Sam', 2);INSERT INTO employees VALUES (3, 'Julie', 3);INSERT INTO employees VALUES (4, 'Kate', 4); CREATE COLUMN TABLE departments (department_id INTEGER, department_name VARCHAR(20));INSERT INTO departments VALUES (1, 'Development');INSERT INTO departments VALUES (2, 'Operation');INSERT INTO departments VALUES (3, 'HR');INSERT INTO departments VALUES (4, 'Security'); DO BEGIN DECLARE CURSOR cur FOR SELECT employees.employee_name, departments.department_name FROM employees, departments WHERE employees.department_id = departments.department_id FOR UPDATE OF employees.employee_id, departments.department_id; FOR r AS cur DO IF r.department_name = 'Development' THEN UPDATE employees SET employee_id = employee_id + 10000, department_id = department_id + 100 WHERE CURRENT OF cur; UPDATE departments SET department_id = department_id + 100 WHERE CURRENT OF cur; ELSEIF r.department_name = 'HR' THEN DELETE FROM employees WHERE CURRENT OF cur; DELETE FROM departments WHERE CURRENT OF cur; END IF; END FOR;END;