主页 / 网络编程 /

Oracle使用游标进行分批次更新数据的6种方式及速

网络编程 2021-07-05 16:09www.168986.cn编程入门
这篇文章主要介绍了Oracle使用游标进行分批次更新的5种方式及速度比对，帮助大家更好的理解和使用数据库，感兴趣的朋友可以了解下

1.情景展示

　　一共有22w条数据， 需要将A表的主键更新至B表的指定字段，如何快速完成更新？

2.解决方案

　　声明

　　解决方案不只一种，该文章只介绍快速游标法及代码实现；

　　两张表的ID和ID_CARD字段都建立了索引。 

　　方式一使用隐式游标（更新一次提交1次）

--快速游标法
BEGIN
  FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD
                        FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
                       WHERE T1.ID_CARD = T2.ID_CARD
                         AND T1.REMARK = '市区数据'
                         AND T2.REMARK = '市区数据') LOOP
    / LOOP循环的是TEMP_CURSOR（逐条读取TEMP_CURSOR） /
    UPDATE VIRTUAL_CARD10
       SET INDEX_ID = TEMP_CURSOR.ID
     WHERE ID_CARD = TEMP_CURSOR.ID_CARD;
    COMMIT; --提交
  END LOOP;
END;

　　执行时间

　　方式二使用隐式游标（更新1000次提交1次）（推荐使用）

/ 使用隐式游标进行分批次更新 /
DECLARE
 V_COUNT NUMBER(10);
BEGIN
 / 隐式游标 /
 FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD
            FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
            WHERE T1.ID_CARD = T2.ID_CARD
             AND T1.REMARK = '市区数据'
             AND T2.REMARK = '市区数据') LOOP
  / 业务逻辑 /
  UPDATE VIRTUAL_CARD10
    SET INDEX_ID = TEMP_CURSOR.ID
   WHERE ID_CARD = TEMP_CURSOR.ID_CARD;
  / 更新一次，+1 /
  V_COUNT := V_COUNT + 1;
  / 1000条提交1次 /
  IF V_COUNT >= 1000 THEN
   COMMIT; --提交
   V_COUNT := 0; --重置
  END IF;
 END LOOP;
 COMMIT; -- 提交所有数据，把这个去掉，可以查看是否是自己想要的效果，再决定是否提交
END;

　　执行时间

　　方式三显式游标+分批次更新（1000条1提交）

/ 使用游标进行分批次更新 /
DECLARE
  V_COUNT    NUMBER(10);
  V_INDEX_ID PRIMARY_INDEX10.ID%TYPE;
  V_ID_CARD  PRIMARY_INDEX10.ID_CARD%TYPE;
  CURSOR TEMP_CURSOR IS
    SELECT T2.ID, T2.ID_CARD
      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
     WHERE T1.ID_CARD = T2.ID_CARD
       AND T1.REMARK = '市区数据'
       AND T2.REMARK = '市区数据';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    / 取得一行游标数据并放到对应变量中 /
    FETCH TEMP_CURSOR
      INTO V_INDEX_ID, V_ID_CARD;
    / 如果没有数据则退出 /
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    / 业务逻辑 /
    UPDATE VIRTUAL_CARD10
       SET INDEX_ID = V_INDEX_ID
     WHERE ID_CARD = V_ID_CARD;
    / 更新一次，+1 /
    V_COUNT := V_COUNT + 1;
    / 1000条提交1次 /
    IF V_COUNT >= 1000 THEN
      COMMIT; --提交
      V_COUNT := 0; --重置
    END IF;
  END LOOP;
  COMMIT; -- 提交所有数据，把这个去掉，可以查看是否是自己想要的效果，再决定是否提交
  CLOSE TEMP_CURSOR;
END;

　　执行时间

　　10000条1提交，执行时间

　　方式四显式游标+数组（更新一次提交一次）（使用BULK COLLECT）

/ 使用游标+数组进行更新（更新一次提交一次） /
DECLARE
  / 创建数组一列多行 /
  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;
  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;
  / 起别名 /
  V_INDEX_ID TYPE_INDEX_ID;
  V_ID_CARD  TYPE_ID_CARD;
  / 将查询出来的数据放到游标里 /
  CURSOR TEMP_CURSOR IS
    SELECT T2.ID, T2.ID_CARD
      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
     WHERE T1.ID_CARD = T2.ID_CARD
       AND T1.REMARK = '市区数据'
       AND T2.REMARK = '市区数据';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    / 取得1000行游标数据并放到对应数组中，每次读取1000条数据 /
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    / 如果没有数据则退出 /
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    / 遍历数据 /
    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP
      / 业务逻辑 /
      UPDATE VIRTUAL_CARD10
         SET INDEX_ID = V_INDEX_ID(I)
       WHERE ID_CARD = V_ID_CARD(I);
      COMMIT;
    END LOOP;
  END LOOP;
  CLOSE TEMP_CURSOR;
END;

　　执行时间

　　方式五 显式游标+数组（1000条提交一次）（使用BULK COLLECT）

/ 使用游标+数组进行更新（1000条提交一次） /
DECLARE
  / 创建数组一列多行 /
  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;
  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;
  / 起别名 /
  V_INDEX_ID TYPE_INDEX_ID;
  V_ID_CARD  TYPE_ID_CARD;
  / 将查询出来的数据放到游标里 /
  CURSOR TEMP_CURSOR IS
    SELECT T2.ID, T2.ID_CARD
      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
     WHERE T1.ID_CARD = T2.ID_CARD
       AND T1.REMARK = '市区数据'
       AND T2.REMARK = '市区数据';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    / 取得1000行游标数据并放到对应数组中 /
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    / 如果没有数据则退出 /
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    / 遍历数据 /
    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP --或者FOR I IN 1 .. V_INDEX_ID.COUNT LOOP
      / 业务逻辑 /
      UPDATE VIRTUAL_CARD10
         SET INDEX_ID = V_INDEX_ID(I)
       WHERE ID_CARD = V_ID_CARD(I);
      IF I >= V_INDEX_ID.LAST THEN
        COMMIT; --提交
      END IF;
    END LOOP;
  END LOOP;
  CLOSE TEMP_CURSOR;
END;

　　执行时间

　　方式六推荐使用（使用BULK COLLECT和FORALL）

/ 使用游标+数组进行更新（BULK COLLECT和FORALL） /
DECLARE
  / 创建数组一列多行 /
  TYPE TYPE_INDEX_ID IS TABLE OF PRIMARY_INDEX10.ID%TYPE;
  TYPE TYPE_ID_CARD IS TABLE OF PRIMARY_INDEX10.ID_CARD%TYPE;
  / 起别名 /
  V_INDEX_ID TYPE_INDEX_ID;
  V_ID_CARD  TYPE_ID_CARD;
  / 将查询出来的数据放到游标里 /
  CURSOR TEMP_CURSOR IS
    SELECT T2.ID, T2.ID_CARD
      FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2
     WHERE T1.ID_CARD = T2.ID_CARD
       AND T1.REMARK = '市区数据'
       AND T2.REMARK = '市区数据';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    / 取得1000行游标数据并放到对应数组中 /
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    / 如果没有数据则退出 /
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    / 遍历数据 /
    FORALL I IN 1 .. V_INDEX_ID.COUNT-- 或者V_INDEX_ID.FIRST .. V_INDEX_ID.LAST
    / 业务逻辑 /
      UPDATE VIRTUAL_CARD10
         SET INDEX_ID = V_INDEX_ID(I)
       WHERE ID_CARD = V_ID_CARD(I);
    COMMIT; --提交
  END LOOP;
  CLOSE TEMP_CURSOR;
END;

　　执行时间

　　从Oracle8开始，oracle为PL/SQL引入了两个新的数据操纵语言（DML）语句BULK COLLECT和FORALL。

　　这两个语句在PL/SQL内部进行一种数组处理；BULK COLLECT提供对数据的高速检索，FORALL可大大改进INSERT、UPDATE和DELETE操作的性能。

　　Oracle数据库使用这些语句大大减少了PL/SQL与SQL语句执行引擎的环境切换次数，从而使其性能有了显著提高。 

小结

　　数据量小的时候可以用方式二，数据量大的时候推荐使用方式六；

　　一定要建索引。

以上就是Oracle使用游标进行分批次更新的6种方式及速度比对的详细内容，更多关于Oracle 游标的资料请关注狼蚁SEO其它相关文章！

上一篇：Oracle游标使用参考语句实例解析 下一篇：oracle 批量删除表数据的几种方法

编程语言

Copyright © 2016-2025 www.168986.cn 狼蚁网络 版权所有 Power by