Oracle数据删除 #

Oracle数据删除方式
├── DELETE语句
│   ├── DML操作
│   ├── 可以回滚
│   ├── 支持WHERE条件
│   └── 触发触发器
├── TRUNCATE语句
│   ├── DDL操作
│   ├── 不能回滚
│   ├── 删除所有数据
│   └── 不触发触发器
└── DROP语句
    ├── DDL操作
    ├── 删除表结构和数据
    └── 可闪回恢复

-- 创建示例表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    salary NUMBER(8,2),
    department_id NUMBER(4),
    manager_id NUMBER(6),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees VALUES (1, 'John', 'Doe', 'john@example.com', 5000, 10, 100, SYSDATE);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 'jane@example.com', 6000, 10, 100, SYSDATE);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 'bob@example.com', 7000, 20, 101, SYSDATE);
INSERT INTO employees VALUES (4, 'Alice', 'Williams', 'alice@example.com', 8000, 20, 101, SYSDATE);
INSERT INTO employees VALUES (5, 'Charlie', 'Brown', 'charlie@example.com', 9000, 30, 102, SYSDATE);

-- 创建子表
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    total_amount NUMBER(12,2),
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES employees(employee_id)
);

-- DELETE基本语法
DELETE FROM table_name
WHERE condition;

-- 删除所有数据
DELETE FROM employees;

-- 删除指定条件数据
DELETE FROM employees
WHERE employee_id = 1;

-- 使用WHERE条件
DELETE FROM employees
WHERE department_id = 10;

-- 使用多个条件
DELETE FROM employees
WHERE department_id = 10 AND salary < 5000;

-- 使用IN条件
DELETE FROM employees
WHERE employee_id IN (1, 2, 3);

-- 使用BETWEEN条件
DELETE FROM employees
WHERE salary BETWEEN 5000 AND 7000;

-- 使用LIKE条件
DELETE FROM employees
WHERE email LIKE '%@oldcompany.com';

-- 使用IS NULL条件
DELETE FROM employees
WHERE manager_id IS NULL;

-- 使用子查询条件
DELETE FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1700
);

-- 使用相关子查询删除
DELETE FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

-- 使用子查询比较删除
DELETE FROM employees
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
);

-- 使用WITH子句删除（12c+）
WITH low_salary AS (
    SELECT employee_id
    FROM employees
    WHERE salary < 5000
)
DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM low_salary);

-- 删除重复数据（保留一条）
-- 方法1：使用ROWID
DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MAX(ROWID)
    FROM employees
    GROUP BY employee_id
);

-- 方法2：使用ROW_NUMBER()
DELETE FROM employees
WHERE ROWID IN (
    SELECT ROWID
    FROM (
        SELECT 
            ROWID,
            ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY hire_date) AS rn
        FROM employees
    )
    WHERE rn > 1
);

-- 方法3：创建临时表
CREATE TABLE employees_temp AS
SELECT DISTINCT * FROM employees;

TRUNCATE TABLE employees;
INSERT INTO employees SELECT * FROM employees_temp;
DROP TABLE employees_temp;

-- TRUNCATE基本语法
TRUNCATE TABLE table_name;

-- TRUNCATE选项
TRUNCATE TABLE employees;
TRUNCATE TABLE employees DROP ALL STORAGE;  -- 释放所有空间
TRUNCATE TABLE employees REUSE STORAGE;     -- 保留空间

-- TRUNCATE特点
-- 1. DDL操作，自动提交
-- 2. 不能回滚
-- 3. 不触发DELETE触发器
-- 4. 重置高水位线
-- 5. 比DELETE快

-- TRUNCATE限制
-- 1. 不能TRUNCATE有外键引用的表
-- TRUNCATE TABLE employees;  -- 如果orders表引用employees，会报错

-- 解决方案：先禁用外键
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;
TRUNCATE TABLE employees;
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;

-- 2. TRUNCATE后数据不可恢复
-- 确保已备份重要数据

-- 3. TRUNCATE会重置序列（如果使用IDENTITY列）
-- 12c+ IDENTITY列
CREATE TABLE test (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(50)
);

INSERT INTO test VALUES (DEFAULT, 'Test1');
INSERT INTO test VALUES (DEFAULT, 'Test2');

SELECT * FROM test;  -- id: 1, 2

TRUNCATE TABLE test;

INSERT INTO test VALUES (DEFAULT, 'Test3');
SELECT * FROM test;  -- id: 1（序列重置）

-- 创建带级联删除的外键
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES employees(employee_id)
        ON DELETE CASCADE
);

-- 删除父记录时自动删除子记录
DELETE FROM employees WHERE employee_id = 1;
-- orders表中customer_id=1的记录也会被删除

-- 创建带SET NULL的外键
CREATE TABLE orders (
    order_id NUMBER(10) PRIMARY KEY,
    customer_id NUMBER(6),
    order_date DATE,
    CONSTRAINT fk_customer 
        FOREIGN KEY (customer_id) 
        REFERENCES employees(employee_id)
        ON DELETE SET NULL
);

-- 删除父记录时子记录外键设为NULL
DELETE FROM employees WHERE employee_id = 1;
-- orders表中customer_id=1的记录的customer_id会变为NULL

-- 手动删除子记录再删除父记录
-- 方法1：分步删除
DELETE FROM orders WHERE customer_id = 1;
DELETE FROM employees WHERE employee_id = 1;
COMMIT;

-- 方法2：使用存储过程
CREATE OR REPLACE PROCEDURE delete_employee(p_emp_id NUMBER) AS
BEGIN
    -- 删除相关订单
    DELETE FROM orders WHERE customer_id = p_emp_id;
    
    -- 删除员工
    DELETE FROM employees WHERE employee_id = p_emp_id;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 执行删除
EXEC delete_employee(1);

-- 分批删除大量数据
DECLARE
    v_batch_size NUMBER := 10000;
    v_rows_deleted NUMBER;
BEGIN
    LOOP
        DELETE FROM employees
        WHERE department_id = 10
        AND ROWNUM <= v_batch_size;
        
        v_rows_deleted := SQL%ROWCOUNT;
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE('Deleted ' || v_rows_deleted || ' rows');
        
        EXIT WHEN v_rows_deleted < v_batch_size;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Deletion completed');
END;
/

-- PL/SQL中使用FORALL批量删除
DECLARE
    TYPE id_array IS TABLE OF NUMBER;
    v_ids id_array;
BEGIN
    -- 获取要删除的ID
    SELECT employee_id
    BULK COLLECT INTO v_ids
    FROM employees
    WHERE department_id = 10;
    
    -- 批量删除
    FORALL i IN 1..v_ids.COUNT
        DELETE FROM employees
        WHERE employee_id = v_ids(i);
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows');
END;
/

-- 方法1：创建新表保留需要的数据
CREATE TABLE employees_new AS
SELECT * FROM employees
WHERE department_id != 10;

-- 替换原表
DROP TABLE employees;
RENAME employees_new TO employees;

-- 重建索引和约束
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 方法2：使用分区表
-- 删除整个分区
ALTER TABLE sales DROP PARTITION p2023;

-- 截断分区
ALTER TABLE sales TRUNCATE PARTITION p2023;

-- 创建索引加速删除
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 使用索引列删除
DELETE FROM employees WHERE department_id = 10;

-- 查看执行计划
EXPLAIN PLAN FOR
DELETE FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 大批量删除时禁用约束和触发器
-- 禁用触发器
ALTER TABLE employees DISABLE ALL TRIGGERS;

-- 禁用外键
ALTER TABLE orders DISABLE CONSTRAINT fk_customer;

-- 执行删除
DELETE FROM employees WHERE department_id = 10;

-- 启用触发器
ALTER TABLE employees ENABLE ALL TRIGGERS;

-- 启用外键
ALTER TABLE orders ENABLE CONSTRAINT fk_customer;

-- 使用并行提示
DELETE /*+ PARALLEL(employees, 4) */ FROM employees
WHERE department_id = 10;

-- 设置并行度
ALTER SESSION ENABLE PARALLEL DML;

DELETE /*+ PARALLEL(employees, 8) */ FROM employees
WHERE department_id = 10;

COMMIT;

-- 捕获删除异常
BEGIN
    DELETE FROM employees WHERE employee_id = 1;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -2292 THEN
            DBMS_OUTPUT.PUT_LINE('Foreign key constraint violated');
        ELSE
            RAISE;
        END IF;
END;
/

-- 检查删除行数
DECLARE
    v_rows_deleted NUMBER;
BEGIN
    DELETE FROM employees WHERE department_id = 99;
    
    v_rows_deleted := SQL%ROWCOUNT;
    
    IF v_rows_deleted = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No rows deleted');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_rows_deleted || ' rows deleted');
    END IF;
END;
/

-- 创建错误日志表
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('employees', 'err_employees');

-- 使用LOG ERRORS记录错误
DELETE FROM employees
WHERE department_id = 10
LOG ERRORS INTO err_employees REJECT LIMIT UNLIMITED;

-- 查看错误记录
SELECT * FROM err_employees;

-- 查看删除前的数据
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- 恢复误删的数据
INSERT INTO employees
SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR)
WHERE employee_id = 1;

-- 启用行移动
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- 闪回表到指定时间
FLASHBACK TABLE employees
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);

-- 闪回表到指定SCN
FLASHBACK TABLE employees TO SCN 1234567;

-- DROP后的表可以从回收站恢复
DROP TABLE employees;

-- 查看回收站
SELECT * FROM USER_RECYCLEBIN;

-- 闪回恢复
FLASHBACK TABLE employees TO BEFORE DROP;

-- 彻底删除（不进回收站）
DROP TABLE employees PURGE;

-- 创建删除触发器
CREATE OR REPLACE TRIGGER trg_employees_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    -- 记录删除日志
    INSERT INTO employees_audit (
        employee_id,
        first_name,
        last_name,
        action,
        action_date
    ) VALUES (
        :OLD.employee_id,
        :OLD.first_name,
        :OLD.last_name,
        'DELETE',
        SYSDATE
    );
END;
/

-- 测试触发器
DELETE FROM employees WHERE employee_id = 1;
SELECT * FROM employees_audit;

-- 级联删除触发器
CREATE OR REPLACE TRIGGER trg_employees_cascade_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    -- 删除相关订单
    DELETE FROM orders WHERE customer_id = :OLD.employee_id;
    
    -- 删除相关记录
    DELETE FROM employee_history WHERE employee_id = :OLD.employee_id;
END;
/

-- 1. 先查询再删除
SELECT COUNT(*) FROM employees WHERE department_id = 10;
-- 确认数量后执行删除

-- 2. 使用事务
BEGIN
    DELETE FROM employees WHERE department_id = 10;
    -- 检查结果
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows will be deleted');
    -- 确认后提交
    COMMIT;
END;
/

-- 3. 备份重要数据
CREATE TABLE employees_backup AS
SELECT * FROM employees WHERE department_id = 10;

-- 4. 使用软删除
ALTER TABLE employees ADD is_deleted NUMBER(1) DEFAULT 0;

UPDATE employees SET is_deleted = 1 WHERE department_id = 10;

-- 查询时过滤
SELECT * FROM employees WHERE is_deleted = 0;

-- 1. 大批量删除使用分批处理
-- 2. 禁用不必要的索引和约束
-- 3. 使用并行处理
-- 4. 考虑使用TRUNCATE代替DELETE
-- 5. 使用分区表按分区删除