DELETE FROM table_name WHERE condition;
DELETE FROM courses WHERE course_designater = 'Java110';
DELETE FROM classconvenings
WHERE classes_num_fk > 4
AND class_convene_date = TO_DATE('2006-02-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND class_location_fk = 'Seattle-Training Room 1';
-- 1. 建表
CREATE TABLE employee (
id VARCHAR2(4) PRIMARY KEY,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
start_date DATE,
end_date DATE,
salary NUMBER(8,2),
city VARCHAR2(10),
description VARCHAR2(15)
);
-- 2. 插入 8 筆測試資料
INSERT INTO employee VALUES ('01','Jason','Martin', DATE '1996-07-25', DATE '2006-07-25', 1234.56, 'Toronto', 'Programmer');
INSERT INTO employee VALUES ('02','Alison','Mathews', DATE '1976-03-21', DATE '1986-02-21', 6661.78, 'Vancouver', 'Tester');
INSERT INTO employee VALUES ('03','James','Smith', DATE '1978-12-12', DATE '1990-03-15', 6544.78, 'Vancouver', 'Tester');
INSERT INTO employee VALUES ('04','Celia','Rice', DATE '1982-10-24', DATE '1999-04-21', 2344.78, 'Vancouver', 'Manager');
INSERT INTO employee VALUES ('05','Robert','Black', DATE '1984-01-15', DATE '1998-08-08', 2334.78, 'Vancouver', 'Tester');
INSERT INTO employee VALUES ('06','Linda','Green', DATE '1987-07-30', DATE '1996-01-04', 4322.78, 'New York', 'Tester');
INSERT INTO employee VALUES ('07','David','Larry', DATE '1990-12-31', DATE '1998-02-12', 7897.78, 'New York', 'Manager');
INSERT INTO employee VALUES ('08','James','Cat', DATE '1996-09-17', DATE '2002-04-15', 1232.78, 'Vancouver', 'Tester');
-- 3. 刪除薪資 < 2000 的員工
DELETE FROM employee WHERE salary < 2000;
-- 4. 驗證剩餘資料
SELECT * FROM employee;
| 特性 | DELETE | TRUNCATE |
|---|---|---|
| 可帶 WHERE | 是 | 否 (全表) |
| 觸發 Trigger | 是 (FOR EACH ROW) | 否 |
| 產生 Undo/Redo | 大量 | 極少 (僅資料字典) |
| 高水位線 (HWM) | 不下降 | 重置為 0 |
| 可回滾 | 是 | 否 (隱性 Commit) |
| 速度 | 慢 (逐列) | 極快 (釋放 Segment) |
DELETE 靈活但重;TRUNCATE 輕快但粗暴。掌握複合條件寫法、日期轉換、大量刪除分批策略,並善用分區表設計,才能在生產環境游刃有餘。