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

2024-07-30 00:57:32

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 批量删除表数据的资料请关注我们其它相关文章！

oracle 数据按主键删除慢问题的解决方法

问题描述: 根据表主键id删除一条数据,在PL/SQL上执行commit后执行时间都大于5秒.!!! 问题分析: 需求是删除一个主表A,另有两个附表建有此表的主键ID的外键.删除A表的数据级联删除另两个表的关联数据.增删改查使用hibernate实现. 一开始一直以为是hibernate的内部处理上有关联操作导致的删除和更新数据缓慢.所以将原先使用hibernate的saveOrupdate方法,改查jdbc的 sql语句来处理update和delete数据操作.但是依然没效果!!! 怀疑数据库
oracle数据库的删除方法详解

1.图形界面删除练习之前记得创建快照执行命令之前要保证数据库属于open状态 SQL> alter database open; [oracle@localhost ~]$ dbca 2.静默删除使用DBCA进行删除的数据库必须处于OPEN状态,否则不能删除. SQL> alter database open; [oracle@localhost ~]$ dbca -silent -deleteDatabase -sourceDB OCPLHR1 3.SQL窗口 startup ##启动
彻底删除Oracle数据库的方法

1.关闭oracle所有的服务.可以在windows的服务管理器中关闭: 2.打开注册表:regedit 打开路径: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\ 删除该路径下的所有以oracle开始的服务名称,这个键是标识Oracle在windows下注册的各种服务! 3.打开注册表,找到路径: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE 删除该oracle目录,该目录下注册着Oracle数据库的软件安装信
Oracle删除重复的数据，Oracle数据去重复

Oracle 数据库中查询重复数据: select * from employee group by emp_name having count (*)>1; Oracle 查询可以删除的重复数据 select t1.* from employee t1 where (t1.emp_name) in (SELECT t2.emp_name from employee t2 group by emp_name having count (*)>1) and t1.emp_id not in
Oracle删除数据报ORA 02292错误的巧妙解决方法

废话不多说了,直接给大家贴上完美的解决方法,具体代码如下所示: --查询表TP_MENU有哪些约束 select * from user_constraints u where u.constraint_name like '%TB_ROLE%'; --失效约束 alter table TP_MENU disable constraint FK_TP_MENU_REFERENCE_TP_MENU cascade; alter table TB_ROLE_REF_MENU disable cons
oracle数据库添加或删除一列的sql语句

alert table 表名 add column 列名 alter table 表名 drop column 列名 eg: alter table TPointManage add AddPointsReason number(8) alter table textattrdetail drop column AddPointsReason 需要注意的一点,如果要修改的表,不是当前的用户的表,那么就需要添加上用户的名称.以及有修改此表的权限.
Oracle误删除表数据后的数据恢复详解

Oracle误删除表数据后的恢复详解测试环境: SYSTEM:IBM AIX 5L Oracle Version:10gR2 1. undo_retention参数的查询与修改使用show parameter undo命令查看当前的数据库参数undo_retention设置. 显示如下: SQL> show parameter undo NAME TYPE VAL
oracle查询重复数据和删除重复记录示例分享

一.查询某个字段重复 select * from User u where u.user_name in (select u.user_name from User u group by u.user_name having count(*) > 1) 二,删除表中某几个字段的重复例:表中有条六条记录. 其中张三和王五的记录有重复 TableA id customer PhoneNo 001 张三 777777 002 李四 444444 003 王五 555555 004 张三 7
Oracle数据库中的级联查询、级联删除、级联更新操作教程

级联查询在ORACLE 数据库中有一种方法可以实现级联查询 select * //要查询的字段 from table //具有子接点ID与父接点ID的表 start with selfid=id //给定一个startid(字段名为子接点ID,及开始的ID号) connect by prior selfid=parentid //联接条件为子接点等于父接点,不能反这个SQL主要用于菜单的级联查询,给一个父接点可以查出所有的子接点.及子接点的子接点,一查到底,很实用.不过呢这个程序只能在ora
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
PHP实现更新中间关联表数据的两种方法

本文以示例形式展示了PHP实现更新中间关联表数据的两种方法.分享给大家供大家参考之用.具体方法如下: 首先,中间关联表:这里的中间表只存表1的主键和表2的主键,即多对多的形式. 执行数据添加和删除为框架内部方法,不属于思路部分. 方法一:先全部删除旧数据,后添加新数据 $res = $this->classes->classEdit($id, $data); //修改主表数据 if($res) { //先删除关联表数据 $bool = $this->lesson_classes->
postgresql 删除重复数据的几种方法小结

在使用PG数据库的这段时间,总结了三种删除重复数据的方法,其中最容易想到的就是最常规的删除方法,但此方法性能较差,删数据耗时较久,虽容易实现,但性能太差,影响写数据的速率. 另外就是被使用的group by删除方法,效率较高. 还有一种是刚发现的,还没有验证,现在就总结下这三种删除方法,并验证各自的执行效率. 首先创建一张基础表,并插入一定量的重复数据. test=# create table deltest(id int, name varchar(255)); CREATE TABLE te
mysql 大表批量删除大量数据的实现方法

问题参考自:https://www.zhihu.com/question/440066129/answer/1685329456 ,mysql中,一张表里有3亿数据,未分表,其中一个字段是企业类型,企业类型是一般企业和个体户,个体户的数据量差不多占50%,根据条件把个体户的行都删掉.请问如何操作?答案为个人原创假设表的引擎是 Innodb, MySQL 5.7+ 删除一条记录,首先锁住这条记录,数据原有的被废弃,记录头发生变化,主要是打上了删除标记.也就是原有的数据 deleted_flag
MySQL删除表数据与MySQL清空表命令的3种方法浅析

目录一.MySQL清空表数据命令:truncate 二.MySQL删除表命令:drop 三.MySQL清空数据表内容的语法:delete 补充:MySQL删除表操作delete.truncate.drop的区别总结一.MySQL清空表数据命令:truncate SQL语法: truncate table 表名注意: 不能与where一起使用. truncate删除数据后是不可以rollback的. truncate删除数据后会重置Identity(标识列.自增字段),相当于自增列会被置为
MySQL删除表数据的方法

在MySQL中有两种方法可以删除数据,一种是DELETE语句,另一种是TRUNCATE TABLE语句.DELETE语句可以通过WHERE对要删除的记录进行选择.而使用TRUNCATE TABLE将删除表中的所有记录.因此,DELETE语句更灵活. 如果要清空表中的所有记录,可以使用下面的两种方法: DELETE FROM table1 TRUNCATE TABLE table1 其中第二条记录中的TABLE是可选的. 如果要删除表中的部分记录,只能使用DELETE语句.
mysql批量删除大量数据

mysql批量删除大量数据假设有一个表(syslogs)有1000万条记录,需要在业务不停止的情况下删除其中statusid=1的所有记录,差不多有600万条, 直接执行 DELETE FROM syslogs WHERE statusid=1 会发现删除失败,因为lock wait timeout exceed的错误. 因为这条语句所涉及的记录数太多,因此我们通过LIMIT参数分批删除,比如每10000条进行一次删除,那么我们可以利用 MySQL这样的语句来完成: DELETE FROM s
mysql清空表数据的两种方式和区别解析

在MySQL中删除数据有两种方式: truncate(截短)属于粗暴型的清空 delete属于精细化的删除删除操作如果你需要清空表里的所有数据,下面两种均可: delete from tablename; truncate table tablename; 而如果你只是删除一部分数据,就只能使用delete: delete from tablename where case1 and case2; 区别在精细化的删除部分数据时,只能使用delete. 而清空所有表数据时,两者均可,此时这两
MyBatis批量插入数据的三种方法实例

目录前言准备工作 1.循环单次插入 2.MP 批量插入 ① 控制器实现 ② 业务逻辑层实现 ③ 数据持久层实现 MP 性能测试 MP 源码分析 3.原生批量插入 ① 业务逻辑层扩展 ② 数据持久层扩展 ③ 添加 UserMapper.xml 原生批量插入性能测试缺点分析解决方案总结前言批量插入功能是我们日常工作中比较常见的业务功能之一,之前我也写过一篇关于<MyBatis Plus 批量数据插入功能,yyds!>的文章,但评论区的反馈不是很好,主要有两个问题:第一,对 MyBat
为什么MySQL 删除表数据磁盘空间还一直被占用

目录 1.Mysql数据结构 2.表文件大小未更改和mysql设计有关 3.那怎么才能让表大小变小 4.Online DDL 5.总结最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多. 为了节约成本,定期进行数据备份,并通过delete删除表记录. 明明已经执行了delete,可表文件的大小却没减小,令人费解项目中使用Mysql作为数据库,对于表来说,一般为表结构和表数据.表结构占用空间都是比较小的,一般都是表数据占用的空间. 当我们使用

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

相关推荐

随机推荐