Oracle DELETE 刪除語句完整語法與實戰範例

基本語法

DELETE FROM table_name
[WHERE condition];

無 WHERE 則刪除全表資料，極度危險，生產環境嚴禁。

實戰範例 1：條件刪除

-- 刪除特定課程
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';

完整演示：Employee 表批次刪除

建表與測試資料

CREATE TABLE Employee (
  ID VARCHAR2(4) NOT NULL,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Start_Date DATE,
  End_Date DATE,
  Salary NUMBER(8,2),
  City VARCHAR2(10),
  Description VARCHAR2(15)
);

-- 插入 8 筆資料 (薪資範圍 1232 ~ 7897)
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');
COMMIT;

刪除薪資 > 3000 的員工

DELETE FROM Employee WHERE Salary > 3000;
-- 4 rows deleted.

SELECT * FROM Employee;
-- 剩餘 4 筆：薪資 ≤ 3000 的員工

DELETE vs TRUNCATE vs DROP

刪除不存在的記錄 (無報錯)

DELETE FROM project WHERE pro_id = 1006;
-- 0 rows deleted. (不報錯，SQL%ROWCOUNT = 0)

效能與安全建議

1. 永遠加 WHERE：養成習慣，即使要全刪也先 `SELECT COUNT(*)` 確認。
2. 大量刪除分批次：

   DELETE FROM big_table WHERE created < SYSDATE - 365 AND ROWNUM <= 10000;

   循環執行並 `COMMIT`，避免 Undo 爆滿與鎖升級。
3. 刪除前備份：`CREATE TABLE backup AS SELECT * FROM target WHERE ...`
4. 利用 `RETURNING` 子句記錄刪除內容：

   DELETE FROM emp WHERE deptno = 10
   RETURNING empno, ename BULK COLLECT INTO l_ids, l_names;