萬盛學電腦網

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

Oracle DELETE 刪除語句完整用法指南

基本語法

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','YYYY-MM-DD')
  AND CLASS_LOCATION_FK = 'Seattle-Training Room 1';

完整實戰演示

1. 建立 Employee 表並填入 8 筆資料

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)
);
-- INSERT 8 筆 (略)

2. 刪除薪資 > 3000 的員工

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

SQL> SELECT * FROM Employee;
ID  FIRST_NAME LAST_NAME START_DAT END_DATE   SALARY CITY       DESCRIPTION
01  Jason      Martin    25-JUL-96 25-JUL-06  1234.56 Toronto    Programmer
04  Celia      Rice      24-OCT-82 21-APR-99  2344.78 Vancouver  Manager
05  Robert     Black     15-JAN-84 08-AUG-98  2334.78 Vancouver  Tester
08  James      Cat       17-SEP-96 15-APR-02  1232.78 Vancouver  Tester

DELETE vs TRUNCATE vs DROP

指令類別可回滾觸發 Trigger高水位線速度
DELETEDML是是不重置慢
TRUNCATEDDL否否重置極快
DROPDDL否否表結構連同刪除極快

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

DELETE FROM project WHERE pro_id = 1006;
-- 0 rows deleted,不報錯

最佳實務

  • 生產環境 DELETE 必加 WHERE,防誤刪全表
  • 大量刪除考慮 PARTITION 裁剪或 TRUNCATE PARTITION
  • 需保留表結構清空資料用 TRUNCATE;不再需要表用 DROP
copyright © 萬盛學電腦網 all rights reserved