oracle 批量删除表数据的几种方法_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)