SQL开发知识: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语句执行引擎的环境切换次数,从而使其性能有了显著提高。 

小结:

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

  一定要建索引。

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


数据运维技术 » SQL开发知识:Oracle使用游标进行分批次更新数据的6种方式及速度比对