oracle 批量删除表数据的4种方式

1.情景展示

  情景一:

  删除PRIMARY_INDEX_TEST表中,MINDEX_ID字段为空的数据

  情景二:

  删除VIRTUAL_CARD_TEST表中的脏数据

2.解决方案

  情景一的解决方案: 

DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL

  情景二的解决方案:

  最简单的方法,见文末 2019/10/17

  2018/12/10

  方案1:使用快速游标法(删除一次提交一次);

 1 --快速游标法
 2 BEGIN
 3   FOR TEMP_CURSOR IN (SELECT ID
 4                         FROM VIRTUAL_CARD3
 5                        WHERE INSTR(NAME, '*') > 0
 6                       UNION
 7                       SELECT ID
 8                         FROM VIRTUAL_CARD3
 9                        WHERE INSTR(NAME, '#') > 0
10                       UNION
11                       SELECT ID
12                         FROM VIRTUAL_CARD3
13                        WHERE INSTR(NAME, '/') > 0
14                       UNION
15                       SELECT ID
16                         FROM VIRTUAL_CARD3
17                        WHERE INSTR(NAME, '+') > 0
18                       UNION
19                       SELECT ID
20                         FROM VIRTUAL_CARD3
21                        WHERE INSTR(NAME, '!') > 0
22                       UNION
23                       SELECT ID
24                         FROM VIRTUAL_CARD3
25                        WHERE INSTR(NAME, '.') > 0) LOOP
26     /* LOOP循环的是TEMP_CURSOR(逐条读取TEMP_CURSOR) */
27     DELETE FROM VIRTUAL_CARD3 WHERE VIRTUAL_CARD3.ID = TEMP_CURSOR.ID;
28     COMMIT; --提交
29   END LOOP;
30 END;

  执行时间:

  方案2:更多游标使用方法,见文末推荐;

  方案3:使用存储过程按id进行逐条删除。

 1 CREATE OR REPLACE PROCEDURE DELETE_TABLE_BATCH(V_ROWS IN NUMBER /*删除多少条数据后进行提交*/) IS
 2   /**
 3   * 内容:
 4   * 日期:2018/12/05
 5   * 作者:Marydon
 6   * 版本:1.0
 7   */
 8   I NUMBER(10); --声明变量,用于记录次数
 9 BEGIN
10   FOR TEMP_TABLE IN (SELECT ID
11                        FROM VIRTUAL_CARD_TEST
12                       WHERE INSTR(NAME, '*') > 0
13                      UNION
14                      SELECT ID
15                        FROM VIRTUAL_CARD_TEST
16                       WHERE INSTR(NAME, '#') > 0
17                      UNION
18                      SELECT ID
19                        FROM VIRTUAL_CARD_TEST
20                       WHERE INSTR(NAME, '/') > 0
21                      UNION
22                      SELECT ID
23                        FROM VIRTUAL_CARD_TEST
24                       WHERE INSTR(NAME, '+') > 0
25                      UNION
26                      SELECT ID
27                        FROM VIRTUAL_CARD_TEST
28                       WHERE INSTR(NAME, '!') > 0
29                      UNION
30                      SELECT ID
31                        FROM VIRTUAL_CARD_TEST
32                       WHERE INSTR(NAME, '.') > 0) LOOP
33     /* LOOP循环的是TEMP_TABLE(逐条读取TEMP_TABLE) */
34     DELETE VIRTUAL_CARD_TEST WHERE VIRTUAL_CARD_TEST.ID = TEMP_TABLE.ID;
35     I := I + 1; --删除一次,+1
36     IF I >= V_ROWS THEN
37       COMMIT; --提交
38       I := 0; --重置
39     END IF;
40   END LOOP;
41 EXCEPTION
42   /* 输出异常信息 */
43   WHEN OTHERS THEN
44     DBMS_OUTPUT.PUT_LINE('异常编号:' || SQLCODE);
45     DBMS_OUTPUT.PUT_LINE('异常信息:' || SQLERRM);
46     ROLLBACK; --回滚
47 END DELETE_TABLE_BATCH;

  创建并运行该存储过程

  删除16522条数据,用了6分21秒,比方式一慢太多了。 

  方案4:

  将要保留的数据插入到新表

 1 --将要保留的数据插入到新表
 2 CREATE TABLE VIRTUAL_CARD_TEMP2 AS(
 3 SELECT *
 4   FROM VIRTUAL_CARD2
 5  WHERE INSTR(NAME, '*') = 0
 6    AND INSTR(NAME, '#') = 0
 7    AND INSTR(NAME, '/') = 0
 8    AND INSTR(NAME, '+') = 0
 9    AND INSTR(NAME, '!') = 0
10    AND INSTR(NAME, '.') = 0)

  删除原来的表

--删除原表
drop table VIRTUAL_CARD2

  将新建的表进行重命名成删除表的名称。

  说明:原来的表有过存在外键约束等关系时,并没有进行测试,因为该表没有索引之类东西,自己测试的时候一定要慎重!!!

  方案5:使用in函数

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)

  说明:ID_CARD字段必须具有唯一性。 

 

写在最后

  哪位大佬如若发现文章存在纰漏之处或需要补充更多内容,欢迎留言!!!

 相关推荐:

 

posted @ 2018-12-05 18:19  Marydon  阅读(18561)  评论(0编辑  收藏  举报