萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle DELETE 敘述完整語法與實戰範例:條件刪除、日期比對、TRUNCATE 對比

基本語法

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 vs TRUNCATE 核心差異

特性DELETETRUNCATE
可帶 WHERE是否 (全表)
觸發 Trigger是 (FOR EACH ROW)否
產生 Undo/Redo大量極少 (僅資料字典)
高水位線 (HWM)不下降重置為 0
可回滾是否 (隱性 Commit)
速度慢 (逐列)極快 (釋放 Segment)

大量刪除最佳實踐

  1. **分批刪除 + COMMIT**:每 5,000~10,000 列 Commit 一次,避免 UNDO 爆滿、鎖升級。
  2. **改用 CTAS 保留資料**:`CREATE TABLE keep AS SELECT * FROM big WHERE keep_cond; DROP TABLE big; RENAME keep TO big;` (需權限、索引、約束重建)。
  3. **Partition Table**:按時間分區,直接 `DROP PARTITION` 秒級完成。

結語

DELETE 靈活但重;TRUNCATE 輕快但粗暴。掌握複合條件寫法、日期轉換、大量刪除分批策略,並善用分區表設計,才能在生產環境游刃有餘。

copyright © 萬盛學電腦網 all rights reserved