DELETE

📄字数 3.0K

👁️阅读量加载中...

一、概述

DELETE是SQL中的数据操作语言（DML）语句之一，主要功能是从数据库表中删除记录。其操作范围灵活，既可以删除表中的全部记录，也能仅删除满足特定条件的部分数据。作为可回滚的DML操作，DELETE的执行结果会被纳入事务管理范畴——在执行COMMIT命令前，删除操作不会真正持久化到数据库；若执行ROLLBACK命令，被删除的记录可恢复至操作前的状态。这一特性为数据操作的安全性提供了重要保障。

二、语法格式

DELETE语句相关语法如下所示：

( opt_from_clause::=, base_table_ref::=, bool_expr::= )

参数说明：
del_targ_tab：指定要删除数据的目标表名（base_table_ref）。表名前可以加上模式名前缀，格式为schema_name.table_name。语法上支持是否添加 FROM 关键字的两种形式。
delete_where_clause：省略时，表示删除整张表的数据。
- opt_where_clause：表示根据条件删除满足的行。
  - bool_expr：使用bool_expr布尔表达式作为过滤条件查询数据。
- WHERE CURRENT OF：用于在PL/SQL中通过游标删除当前行。
opt_returning：表示DELETE后返回被删除的列值。
- target_list：表示需要返回的字段列表。
opt_bulk：表示批量收集返回结果，常与RETURNING...INTO结合，用于一次性获取多行。
opt_into_list：将DELETE...RETURNING的返回值赋值给PL/SQL中的变量（或变量列表）。

提示

WHERE CURRENT OF只能用于游标循环中，所使用的游标必须声明为FOR UPDATE，这是为了在打开游标时锁定行，防止被其他事务修改
WHERE CURRENT OF只能用于UPDATE或DELETE，且不能和普通WHERE混合使用
在PL/SQL中，RETURNING返回的结果必须匹配对应的字段列表

三、DELETE使用要求及注意事项

用户需具备对目标表的DELETE权限
若使用外键约束，必须确保被删除记录不会违反引用完整性
DELETE可与事务控制语句（BEGIN, COMMIT, ROLLBACK）配合使用，确保数据的一致性
无WHERE条件会删除全表记录，但不会释放表空间结构，慎重使用
由于DELETE操作属于逐行处理，删除大量数据可能会导致性能下降或产生大量日志，可考虑使用TRUNCATE
索引列上的删除可能会导致索引碎片增加，需考虑定期维护（如重建索引REINDEX）

提示

TRUNCATE TABLE是DDL操作，效率高于DELETE，其自动COMMIT，无法ROLLBACK，适用于不需要回滚的大批量清表场景

四、示例

删除全表

sql

-- 建表
SQL> CREATE TABLE employees (
       emp_id    NUMBER PRIMARY KEY,
       name      VARCHAR2(100),
       salary    NUMBER,
       dept_id   NUMBER
     );

-- 插入数据
SQL> INSERT INTO employees VALUES (1, 'Alice', 8000, 10);
SQL> INSERT INTO employees VALUES (2, 'Bob',   4000, 20);
SQL> INSERT INTO employees VALUES (3, 'Carol', 4500, 20);
SQL> INSERT INTO employees VALUES (4, 'David', 7000, 10);
SQL> INSERT INTO employees VALUES (5, 'Eve',   3900, 30);


-- `OPT_FROM_CLAUSE`:满足多表条件删除  

SQL> CREATE TABLE em_information(id INT, sex VARCHAR);

SQL> INSERT INTO em_information VALUES(1,'女')(2,'男')(3,'男')(4,'女')(5,'女')(6,'男');

SQL> DELETE FROM em_information AS t1 FROM employees AS t2 WHERE t1.id = t2.emp_id;

SQL> SELECT * FROM em_information;
+----+-----+
| ID | SEX |
+----+-----+
| 6  | 男  |
+----+-----+

-- 删除全表数据
SQL> DELETE FROM employees;

-- 删除全表数据（不带FROM）
SQL> DELETE employees;

WHERE条件删除满足要求的记录

sql

-- 删除dept_id等于30的记录
SQL> DELETE FROM employees WHERE dept_id = 30;

RETURNING INTO删除单行数据

sql

-- 插入数据
SQL> INSERT INTO employees VALUES (1, 'Alice', 8000, 10);
SQL> INSERT INTO employees VALUES (2, 'Bob',   4000, 20);
SQL> INSERT INTO employees VALUES (3, 'Carol', 4500, 20);
SQL> INSERT INTO employees VALUES (4, 'David', 7000, 10);
SQL> INSERT INTO employees VALUES (5, 'Eve',   3900, 30);

SQL> DECLARE
       v_name   employees.name%TYPE;
       v_salary employees.salary%TYPE;
     BEGIN
       DELETE FROM employees
       WHERE emp_id = 2
       RETURNING name, salary INTO v_name, v_salary;    -- 将删除的值返回到v_name和v_salary中
       -- 输出返回列表中的值
       DBMS_OUTPUT.PUT_LINE('Deleted: ' || v_name || ', salary: ' || v_salary);
     END;
     /
Deleted: Bob, salary: 4000

RETURNING BULK COLLECT INTO删除多行数据

sql

SQL> DECLARE
       TYPE name_list IS TABLE OF employees.name%TYPE;
       TYPE salary_list IS TABLE OF employees.salary%TYPE;
       -- 定义变量
       v_names    name_list;
       v_salaries salary_list;
     BEGIN
       DELETE FROM employees
       WHERE salary < 6000
       RETURNING name, salary BULK COLLECT INTO v_names, v_salaries; -- 利用BULK COLLECT批量收集返回结果
       -- 将多行记录分别输出
       FOR i IN 1 .. v_names.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('Deleted: ' || v_names(i) || ', salary: ' || v_salaries(i));
       END LOOP;
     END;
     /
Deleted: Carol, salary: 4500
Deleted: Eve, salary: 3900

使用WHERE CURRENT OF（游标方式删除当前行）

sql

SQL> DECLARE
       -- 声明游标，用于查询dept_id等于10的记录 
       CURSOR emp_cur IS
         SELECT emp_id FROM employees WHERE dept_id = 10 FOR UPDATE;
       -- 定义了变量v_emp_id，类型与employees.emp_id字段一致
       v_emp_id employees.emp_id%TYPE;
     BEGIN
       OPEN emp_cur;
       LOOP
        -- 进入循环，每次从游标中抓取一行emp_id
         FETCH emp_cur INTO v_emp_id;
         EXIT WHEN emp_cur%NOTFOUND;
        -- 删除当前游标指向的那一行
         DELETE FROM employees WHERE CURRENT OF emp_cur;
         DBMS_OUTPUT.PUT_LINE('Deleted emp_id: ' || v_emp_id);
       END LOOP;
       CLOSE emp_cur;
     END;
     /
Deleted emp_id: 1
Deleted emp_id: 4

DELETE ​

一、概述 ​

二、语法格式 ​

三、DELETE使用要求及注意事项 ​

四、示例 ​

DELETE

一、概述

二、语法格式

三、DELETE使用要求及注意事项

四、示例