为大量更新声明游标的正确方法

Question

我需要一些关于我的想法是否可行的建议。我遇到的情况是：

我需要对表的每一行进行更新。更新涉及一些逻辑。 逻辑很简单，但是需要对每一行进行操作。每行都有可能被更新。

目前，我正在考虑编写一个 ESQL/C 程序来执行此操作。我正在考虑将每一行加载到它的 相当于C结构，通过select for update游标，运行逻辑并提交。 HOLD关键字对光标起什么作用？我对这个角色有点困惑。

这些更新将在系统停机期间完成。该表包含大约 1.3 亿行。它有 约45列。大多数列的类型为 SMALLINT 和 INTEGER。

我走在正确的轨道上吗？欢迎提出建议。

数据库将是 Informix（IDS 版本 11.50 FC6）

Answer 1

完成这项工作的关键是在服务器中完成工作，而不是让服务器选择每一行，将其传递给客户端，然后接受从客户端返回的数据。

UPDATE YourTable
   SET y = (CASE WHEN z > x THEN p ELSE y)
 WHERE key_column BETWEEN lo_val AND hi_val;

复杂的部分可能是将工作分解为可管理的子事务；这就是“lo_val .. hi_val”条件的含义。如果您的逻辑日志足够大，可以处理所有正在更新的 1.3 亿行[大约 (2 * (行大小 + X) * 行数)，我相信 X 的值约为 20]，并且有空闲空间，那么您可以一次性完成所有工作。显然，这会“更新”每一行。

如果您决定必须在客户端中执行此操作（这是一个错误，但是...），那么：

您使用带有 HOLD 的 SELECT 游标，以便它在事务中保持打开状态并正确定位。您启动一项事务，获取几千行，并根据需要更新每一行。确保您使用的是准备好的 UPDATE 语句；也许您使用 WHERE CURRENT OF 条件。

---

您是否建议将更新作为游标的一部分放入存储过程中？

不，尽管您可以在存储过程中执行此操作。这在一定程度上取决于这是否是您定期做的事情；如果是这样，也许存储过程是一个好主意，但我不会进行一次性练习。

这取决于您如何确定 lo_val 和 hi_val。我可能会使用 I4GL （因为我很熟悉它），然后我希望准备 UPDATE 语句（用问号代替“lo_val”和“hi_val”），然后我希望执行它多次，每次形成一个语句事务。因此，如果您决定使用 000000..099999、100000..199999、... 的 lo_val..hi_val 范围，那么您将进行迭代：

for i = 0 to 10000000 step 100000
    let j = i + 99999
    execute p_update using i, j
end for

在 I4GL 中，您绝对不需要使用准备好的语句。如果您有 IDS 11，则可以在 SPL 中准备语句。在早期版本中，并且不会对性能造成太大影响（我怀疑您是否可以可靠地测量它），您可以简单地使用：

CREATE PROCEDURE update_your_table()
    DEFINE lo_val, hi_val INTEGER;

    FOR lo_val = 0 TO 1000000 STEP 100000
        LET hi_val = lo_val + 99999;
        UPDATE YourTable
           SET y = (CASE WHEN z > x THEN p ELSE y)
         WHERE key_column BETWEEN lo_val AND hi_val;
    END FOR;

END PROCEDURE;

未经测试的代码 - 使用风险自负！

Answer 2

SPL 是正确的选择！...但我建议您复制该表并首先测试您的批量更新。