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

时间:2022-10-20 15:08:03
 

1.情景展示

  情景一:

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

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

  情景二:

  删除VIRTUAL_CARD_TEST表中的脏数据

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

2.解决方案

  情景一的解决方案:

DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL

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

  情景二的解决方案:

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

  2018/12/10

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

 --快速游标法
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;

  执行时间:

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

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

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

 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;

  创建并运行该存储过程

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

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

  方案4:

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

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

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

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

  方案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字段必须具有唯一性。 

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

写在最后

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

相关推荐: