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

(0)

相关推荐

  • Oracle出现超出打开游标最大数的解决方法

    本文实例讲述了Oracle出现超出打开游标最大数的解决方法.分享给大家供大家参考,具体如下: Java代码在执行conn.createStatement()和conn.prepareStatement()的时候,实际上都是相当与在数据库中打开了一个cursor.尤其是,假如你的createStatement和prepareStatement是在一个循环里面的话,就会非常轻易出现这个问题.因为游标一直在不停的打开,而且没有关闭. 一般来说,我们在写Java代码的时候,createStatement

  • Oracle 遍历游标的四种方式汇总(for、fetch、while、BULK COLLECT)

    1.情景展示 Oracle 遍历游标的四种方式(for.fetch.while.bulk collect+forall) 2.问题分析 我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 方式一:FOR 循环(推荐使用) 变形一:遍历显式游标 /* 如果是在存储过程外使用显式游标,需要使用DECLARE关键字 */ DECLARE   /*创建游标*/   CURSOR CUR_FIRST_INDEX IS     SELECT

  • Oracle显示游标的使用及游标for循环

    下面给大家介绍在什么情况下用隐式游标,什么情况下用显示游标: 1.查询返回单行记录时→隐式游标: 2.查询返回多行记录并逐行进行处理时→显式游标 --显示游标属性 declare CURSOR cur_emp IS SELECT * FROM emp; row_emp cur_emp%ROWTYPE; BEGIN OPEN cur_emp; FETCH cur_emp INTO row_emp; WHILE cur_emp%FOUND LOOP dbms_output.put_line(row_

  • Oracle数据库游标连接超出解决方案

    oracle数据库忽然连不上了,一查是游标数量超了. 1.查看游标数 show parameter open_cursors 2.修改游标数 alter system set open_cursors = 10000; 3.获取打开的游标数 select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'AF651_U8' and o.sid = s.sid

  • Oracle存储过程游标用法分析

    本文实例讲述了Oracle存储过程游标用法.分享给大家供大家参考,具体如下: 使用游标的5个步骤 1.声明一些变量用于保存select语句返回的指 2.声明游标,并指定select 语句 3.打开游标 4.从游标中获取记录 5.关闭游标 从游标中获取每一条记录可使用fetch语句.fetch语句将列的指读取到指定的变量中: 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure se

  • Oracle 游标使用总结

    Oracle游标分为显示游标和隐式游标. 显示游标(Explicit Cursor):在PL/SQL程序中定义的.用于查询的游标称作显示游标. 隐式游标(Implicit Cursor):是指非PL/SQL程序中定义的.而且是在PL/SQL中使用UPDATE/DELETE语句时,Oracle系统自动分配的游标. 一.显示游标 1.使用步骤 (1)定义 (2)打开 (3)使用 (4)关闭 2.使用演示 首先创建测试用表STUDENT,脚本如下: 复制代码 代码如下: CREATE TABLE "S

  • Oracle中游标Cursor基本用法详解

    查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL

  • Oracle存储过程返回游标实例详解

    有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 复制代码 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor; --方法1 ) begin open cur_arg for select * from tablename; end 调用 复制代码 代码如下: declare cur_calling sys_refcu

  • 详解Oracle游标的简易用法

    下面看下Oracle游标的简易用法,具体代码如下所示: create or replace procedure NW_DelYW(iOPERATION_ID number, sUserID varchar2) is sCurDJBH yw_operation_link.djbh%type; cursor table_yw(ywid yw_operation.id%type) is select * from yw_operation_link t1 where t1.operation_id =

  • 详解Oracle隐式游标和显式游标

    游标是什么?就是在内存开辟的一块临时存储空间. 1.Oracle隐式游标 1.1Oracle有常用的哪些隐式游标 1.2 Oracle隐式游标演示 -- 隐式游标 (使用的表为Oracle默认自带的emp表) -- sql%rowcount:影响记录条数 sql%found:是否有满足条件的记录 set serveroutput on; declare v_ename a_emp.ename%type; begin select ename into v_ename from a_emp whe

  • Oracle游标的使用实例详解

    什么是游标? ①从表中检索出结果集,从中每次指向一条记录进行交互的机制. ②关系数据库中的操作是在完整的行集合上执行的. 由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的所有行.由该语句返回完整的行集合叫做结果集. 应用程序,尤其是互动和在线应用程序,把完整的结果集作为一个单元处理并不总是有效的. 这些应用程序需要一种机制来一次处理一行或连续的几行.而游标是对提供这一机制的结果集的扩展. 游标是通过游标库来实现的.游标库是常常作为数据库系统或数据访问 API 的一部

随机推荐