oracle批量删除表数据的几种方法

DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL

--快速游标法
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循环的是TEMP_CURSOR（逐条读取TEMP_CURSOR） */
 DELETE FROM VIRTUAL_CARD3 WHERE VIRTUAL_CARD3.ID = TEMP_CURSOR.ID;
 COMMIT; --提交
 END LOOP;
END;

CREATE OR REPLACE PROCEDURE DELETE_TABLE_BATCH(V_ROWS IN NUMBER /*删除多少条数据后进行提交*/) IS
 /**
 * 内容：
 * 日期：2018/12/05
 * 作者：Marydon
 * 版本：1.0
 */
 I NUMBER(10); --声明变量，用于记录次数
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循环的是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('异常编号：' || SQLCODE);
 DBMS_OUTPUT.PUT_LINE('异常信息：' || SQLERRM);
 ROLLBACK; --回滚
END DELETE_TABLE_BATCH;

--将要保留的数据插入到新表
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)

--删除原表
drop table VIRTUAL_CARD2

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)