您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 508人已围观

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

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;

　　执行时间：