Oracle使用游標進行分批次更新數(shù)據(jù)的6種方式及速度比對

更新時間：2020年10月22日 14:42:21 作者：Marydon

這篇文章主要介紹了Oracle使用游標進行分批次更新的5種方式及速度比對，幫助大家更好的理解和使用數(shù)據(jù)庫，感興趣的朋友可以了解下

1.情景展示

　　一共有22w條數(shù)據(jù)，需要將A表的主鍵更新至B表的指定字段，如何快速完成更新？

2.解決方案

　　聲明：

　　解決方案不只一種，該文章只介紹快速游標法及代碼實現(xiàn)；

　　兩張表的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 = '**市****區(qū)數(shù)據(jù)'
                         AND T2.REMARK = '**市****區(qū)數(shù)據(jù)') LOOP
    /* LOOP循環(huán)的是TEMP_CURSOR（逐條讀取TEMP_CURSOR） */
    UPDATE VIRTUAL_CARD10
       SET INDEX_ID = TEMP_CURSOR.ID
     WHERE ID_CARD = TEMP_CURSOR.ID_CARD;
    COMMIT; --提交
  END LOOP;
END;

　　執(zhí)行時間：

　　方式二：使用隱式游標（更新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 = '**市****區(qū)數(shù)據(jù)'
             AND T2.REMARK = '**市****區(qū)數(shù)據(jù)') LOOP
  /* 業(yè)務邏輯 */
  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; -- 提交所有數(shù)據(jù)，把這個去掉，可以查看是否是自己想要的效果，再決定是否提交
END;

　　執(zhí)行時間：

　　方式三：顯式游標+分批次更新（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 = '**市****區(qū)數(shù)據(jù)'
       AND T2.REMARK = '**市****區(qū)數(shù)據(jù)';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    /* 取得一行游標數(shù)據(jù)并放到對應變量中 */
    FETCH TEMP_CURSOR
      INTO V_INDEX_ID, V_ID_CARD;
    /* 如果沒有數(shù)據(jù)則退出 */
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    /* 業(yè)務邏輯 */
    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; -- 提交所有數(shù)據(jù)，把這個去掉，可以查看是否是自己想要的效果，再決定是否提交
  CLOSE TEMP_CURSOR;
END;

　　執(zhí)行時間：

　　10000條1提交，執(zhí)行時間：

　　方式四：顯式游標+數(shù)組（更新一次提交一次）（使用BULK COLLECT）

/* 使用游標+數(shù)組進行更新（更新一次提交一次） */
DECLARE
  /* 創(chuàng)建數(shù)組：一列多行 */
  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;
  /* 將查詢出來的數(shù)據(jù)放到游標里 */
  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 = '**市****區(qū)數(shù)據(jù)'
       AND T2.REMARK = '**市****區(qū)數(shù)據(jù)';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    /* 取得1000行游標數(shù)據(jù)并放到對應數(shù)組中，每次讀取1000條數(shù)據(jù) */
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    /* 如果沒有數(shù)據(jù)則退出 */
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    /* 遍歷數(shù)據(jù) */
    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP
      /* 業(yè)務邏輯 */
      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;

　　執(zhí)行時間：

　　方式五：顯式游標+數(shù)組（1000條提交一次）（使用BULK COLLECT）

/* 使用游標+數(shù)組進行更新（1000條提交一次） */
DECLARE
  /* 創(chuàng)建數(shù)組：一列多行 */
  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;
  /* 將查詢出來的數(shù)據(jù)放到游標里 */
  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 = '**市****區(qū)數(shù)據(jù)'
       AND T2.REMARK = '**市****區(qū)數(shù)據(jù)';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    /* 取得1000行游標數(shù)據(jù)并放到對應數(shù)組中 */
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    /* 如果沒有數(shù)據(jù)則退出 */
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    /* 遍歷數(shù)據(jù) */
    FOR I IN V_INDEX_ID.FIRST .. V_INDEX_ID.LAST LOOP --或者：FOR I IN 1 .. V_INDEX_ID.COUNT LOOP
      /* 業(yè)務邏輯 */
      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;

　　執(zhí)行時間：

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

/* 使用游標+數(shù)組進行更新（BULK COLLECT和FORALL） */
DECLARE
  /* 創(chuàng)建數(shù)組：一列多行 */
  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;
  /* 將查詢出來的數(shù)據(jù)放到游標里 */
  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 = '**市****區(qū)數(shù)據(jù)'
       AND T2.REMARK = '**市****區(qū)數(shù)據(jù)';
BEGIN
  OPEN TEMP_CURSOR;
  LOOP
    /* 取得1000行游標數(shù)據(jù)并放到對應數(shù)組中 */
    FETCH TEMP_CURSOR BULK COLLECT
      INTO V_INDEX_ID, V_ID_CARD LIMIT 1000;
    /* 如果沒有數(shù)據(jù)則退出 */
    EXIT WHEN TEMP_CURSOR%NOTFOUND;
    /* 遍歷數(shù)據(jù) */
    FORALL I IN 1 .. V_INDEX_ID.COUNT-- 或者V_INDEX_ID.FIRST .. V_INDEX_ID.LAST
    /* 業(yè)務邏輯 */
      UPDATE VIRTUAL_CARD10
         SET INDEX_ID = V_INDEX_ID(I)
       WHERE ID_CARD = V_ID_CARD(I);
    COMMIT; --提交
  END LOOP;
  CLOSE TEMP_CURSOR;
END;

　　執(zhí)行時間：

　　從Oracle8開始，oracle為PL/SQL引入了兩個新的數(shù)據(jù)操縱語言（DML）語句：BULK COLLECT和FORALL。

　　這兩個語句在PL/SQL內(nèi)部進行一種數(shù)組處理；BULK COLLECT提供對數(shù)據(jù)的高速檢索，F(xiàn)ORALL可大大改進INSERT、UPDATE和DELETE操作的性能。

　　Oracle數(shù)據(jù)庫使用這些語句大大減少了PL/SQL與SQL語句執(zhí)行引擎的環(huán)境切換次數(shù)，從而使其性能有了顯著提高。

小結(jié)：

　　數(shù)據(jù)量小的時候可以用方式二，數(shù)據(jù)量大的時候推薦使用方式六；

　　一定要建索引。

以上就是Oracle使用游標進行分批次更新的6種方式及速度比對的詳細內(nèi)容，更多關于Oracle 游標的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

oracle 12c安裝教程（window）
這篇文章主要為大家詳細介紹了oracle 12c的安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-03-03
Oracle LogMiner的使用實例代碼
這篇文章主要給大家分享了關于Oracle LogMiner的使用實例代碼，文中通過示例代碼介紹了關于查詢當前日志組、業(yè)務用戶插入操作、歸檔日志切換、業(yè)務用戶插入操作以及歸檔日志切換等等的相關功能，需要的朋友可以參考借鑒，下面來一起看看吧。
2017-07-07
PLSQL14下載與安裝使用教程
PL/SQL Developer 14是allround automations最新推出的一款PL/SQL數(shù)據(jù)庫管理軟件，但是是收費的，今天小編給大家?guī)砹薖LSQL14下載與安裝使用教程，一起看看吧
2021-09-09
redhat 4中安裝Oracle 10g圖文教程
本文主要講訴了在redhat 4中安裝Oracle 10g的全過程的詳細記錄，不過只是安裝過程，建庫不在本教程范圍內(nèi)，呵呵，自己摸索或者等待本文續(xù)篇
2014-08-08
Oracle在PL/SQL中嵌入SQL語句
這篇文章介紹了Oracle在PL/SQL中嵌入SQL語句的方法，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2022-05-05
Oracle中的MD5加密詳解
MD5是我們常用的一種加密方式，在各個方面都有用到這個加密方式，今天我們來探討下在Oracle中如何使用MD5加密
2014-09-09
Oracle連接數(shù)據(jù)庫提示ORA-12638:身份證明檢索失敗的解決辦法
今天在使用應用程序連接Oracle時碰到了"ORA-12638:身份證明檢索失敗"錯誤,給大家總結(jié)解決方法,這篇文章主要給大家介紹了關于Oracle連接數(shù)據(jù)庫提示ORA-12638:身份證明檢索失敗的解決辦法,需要的朋友可以參考下
2023-10-10
Oracle?19c的參數(shù)sec_case_sensitive_logon與ORA-01017錯誤問題分析
這篇文章主要介紹了Oracle?19c的參數(shù)sec_case_sensitive_logon與ORA-01017錯誤,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2023-04-04
oracle?mysql?拼接值遇到的坑及雙豎線?||?concat詳解
在Oracle中，字符串拼接有兩種方法，分別是CONCAT()函數(shù)和“||”拼接，本文給大家講解oracle?mysql?拼接值遇到的坑及雙豎線?||?concat詳解，感興趣的朋友跟隨小編一起看看吧
2023-04-04
ORACLE學習筆記-新建用戶及建表篇
Oracle系統(tǒng)，即是以Oracle關系數(shù)據(jù)庫為數(shù)據(jù)存儲和管理作為構(gòu)架基礎，構(gòu)建出的數(shù)據(jù)庫管理系統(tǒng)。世界第一個支持SQL語言的商業(yè)數(shù)據(jù)庫，定位于高端工作站，以及作為服務器的小型計算機，Oracle公司的整個產(chǎn)品線包括數(shù)據(jù)庫服務器、企業(yè)商務應用套件、應用開發(fā)和決策支持工具
2014-08-08