oracle按时间删除表中数据,oracle 批量删除表数据的几种方法

1.情景展示

情景一：

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

情景二：

删除VIRTUAL_CARD_TEST表中的脏数据

2.解决方案

情景一的解决方案：

DELETE FROM PRIMARY_INDEX_TEST WHERE MINDEX_ID IS NULL

情景二的解决方案：

方案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;

执行时间：

方案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;

创建并运行该存储过程

删除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 批量删除表数据的几种方法的详细内容，更多关于oracle 批量删除表数据的资料请关注其它相关文章！