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

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 游标的资料请关注其它相关文章！

• Oracle 遍历游标的四种方式汇总（for、fetch、while、BULK COLLECT）
• Oracle数据库游标连接超出解决方案
• 详解Oracle游标的简易用法
• Oracle游标的使用实例详解
• Oracle中游标Cursor基本用法详解
• 详解Oracle隐式游标和显式游标
• Oracle存储过程游标用法分析
• Oracle出现超出打开游标最大数的解决方法
• Oracle显示游标的使用及游标for循环
• Oracle存储过程返回游标实例详解
• Oracle 游标使用总结

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！