oracle 批量刪除表數(shù)據(jù)的幾種方法

更新時(shí)間：2020年10月22日 14:43:11 作者：Marydon

這篇文章主要介紹了oracle 批量刪除表數(shù)據(jù)的幾種方法，幫助大家更好的理解和使用Oracle數(shù)據(jù)庫(kù)，感興趣的朋友可以了解下

1.情景展示

　　情景一：

　　刪除PRIMARY_INDEX_TEST表中，MINDEX_ID字段為空的數(shù)據(jù)

　　情景二：

　　刪除VIRTUAL_CARD_TEST表中的臟數(shù)據(jù)

2.解決方案

　　情景一的解決方案：

DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL

　　情景二的解決方案：

　　方案1：使用快速游標(biāo)法（刪除一次提交一次）；

--快速游標(biāo)法
BEGIN
 FOR TEMP_CURSOR IN (SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '*') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '#') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '/') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '+') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '!') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD3
      WHERE INSTR(NAME, '.') > 0) LOOP
 /* LOOP循環(huán)的是TEMP_CURSOR（逐條讀取TEMP_CURSOR） */
 DELETE FROM VIRTUAL_CARD3 WHERE VIRTUAL_CARD3.ID = TEMP_CURSOR.ID;
 COMMIT; --提交
 END LOOP;
END;

　　執(zhí)行時(shí)間：

　　方案2：更多游標(biāo)使用方法，見(jiàn)這里

　　方案3：使用存儲(chǔ)過(guò)程按id進(jìn)行逐條刪除。

CREATE OR REPLACE PROCEDURE DELETE_TABLE_BATCH(V_ROWS IN NUMBER /*刪除多少條數(shù)據(jù)后進(jìn)行提交*/) IS
 /**
 * 內(nèi)容：
 * 日期：2018/12/05
 * 作者：Marydon
 * 版本：1.0
 */
 I NUMBER(10); --聲明變量，用于記錄次數(shù)
BEGIN
 FOR TEMP_TABLE IN (SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '*') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '#') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '/') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '+') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '!') > 0
      UNION
      SELECT ID
      FROM VIRTUAL_CARD_TEST
      WHERE INSTR(NAME, '.') > 0) LOOP
 /* LOOP循環(huán)的是TEMP_TABLE（逐條讀取TEMP_TABLE） */
 DELETE VIRTUAL_CARD_TEST WHERE VIRTUAL_CARD_TEST.ID = TEMP_TABLE.ID;
 I := I + 1; --刪除一次，+1
 IF I >= V_ROWS THEN
  COMMIT; --提交
  I := 0; --重置
 END IF;
 END LOOP;
EXCEPTION
 /* 輸出異常信息 */
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('異常編號(hào)：' || SQLCODE);
 DBMS_OUTPUT.PUT_LINE('異常信息：' || SQLERRM);
 ROLLBACK; --回滾
END DELETE_TABLE_BATCH;

　　創(chuàng)建并運(yùn)行該存儲(chǔ)過(guò)程

　　刪除16522條數(shù)據(jù)，用了6分21秒，比方式一慢太多了。

　　方案4：

　　將要保留的數(shù)據(jù)插入到新表

--將要保留的數(shù)據(jù)插入到新表
CREATE TABLE VIRTUAL_CARD_TEMP2 AS(
SELECT *
 FROM VIRTUAL_CARD2
 WHERE INSTR(NAME, '*') = 0
 AND INSTR(NAME, '#') = 0
 AND INSTR(NAME, '/') = 0
 AND INSTR(NAME, '+') = 0
 AND INSTR(NAME, '!') = 0
 AND INSTR(NAME, '.') = 0)

　　刪除原來(lái)的表

--刪除原表
drop table VIRTUAL_CARD2

　　將新建的表進(jìn)行重命名成刪除表的名稱。

　　說(shuō)明：原來(lái)的表有過(guò)存在外鍵約束等關(guān)系時(shí)，并沒(méi)有進(jìn)行測(cè)試，因?yàn)樵摫頉](méi)有索引之類東西，自己測(cè)試的時(shí)候一定要慎重?。?！

　　方案5：使用in函數(shù)

DELETE FROM VIRTUAL_CARD_TEMP
 WHERE ID_CARD IN (SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '*') > 0
                   UNION
                   SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '#') > 0
                   UNION
                   SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '/') > 0
                   UNION
                   SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '+') > 0
                   UNION
                   SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '!') > 0
                   UNION
                   SELECT T1.ID_CARD
                     FROM VIRTUAL_CARD_TEMP T1
                    WHERE INSTR(T1.NAME, '.') > 0)

　　說(shuō)明：ID_CARD字段必須具有唯一性?！?/p>