Oracle SQL 語言完全參考手冊：四大類語句與核心 DML 操作

SQL 語言四大分類總覽

Oracle SQL 依功能劃分為四大類，掌握分類邊界是精準授權與審計的基礎：

DML 核心語句詳細指南

INSERT — 資料插入

基本語法

INSERT INTO table_name [(column_list)] 
VALUES (value_list) | subquery;

關鍵規則

• 欄位清單可省略：預設對應所有欄位（依 COLUMN_ID 順序，可查 USER_TAB_COLUMNS）
• 值數量/型別必須匹配：不匹配觸發隱式轉換，可能導致精度損失或報錯
• NULL 處理：VALUES (NULL) 或省略欄位（若有預設值）
• 子查詢插入：INSERT INTO target SELECT ... FROM source WHERE ...

實用範例

-- 明確指定欄位（推薦，防結構變更失效）
INSERT INTO employees (emp_id, first_name, last_name, hire_date, salary)
VALUES (1001, 'John', 'Doe', SYSDATE, 5000);

-- 多筆插入（10g+ 支援）
INSERT ALL
  INTO employees (emp_id, first_name) VALUES (1002, 'Jane')
  INTO employees (emp_id, first_name) VALUES (1003, 'Bob')
SELECT * FROM dual;

-- 子查詢插入（歸檔歷史資料）
INSERT INTO employees_archive
SELECT * FROM employees WHERE hire_date < DATE '2020-01-01';

UPDATE — 資料修改

語法與關鍵點

UPDATE table_name
SET column1 = expr1, column2 = expr2, ...
[WHERE condition];

• 無 WHERE 條件 = 全表更新：極度危險，務必先 SELECT 驗證
• 子查詢賦值：SET col = (SELECT ...) 子查詢必須回傳單一值
• 關聯更新：UPDATE t1 SET t1.col = (SELECT t2.val FROM t2 WHERE t2.id = t1.id)

範例

-- 單欄更新
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 10;

-- 多欄關聯更新
UPDATE employees e
SET (e.salary, e.bonus) = (
    SELECT AVG(salary)*1.2, 500 FROM employees WHERE dept_id = e.dept_id
)
WHERE EXISTS (SELECT 1 FROM employees WHERE dept_id = e.dept_id);

DELETE — 資料刪除

語法與陷阱

DELETE FROM table_name [WHERE condition];

• 無 WHERE = 清空全表：產生大量 UNDO/REDO，考慮 TRUNCATE（DDL，不可回滾）
• 外鍵約束：子表資料存在時無法刪除父表，需先處理子表或設定 ON DELETE CASCADE

範例

-- 條件刪除
DELETE FROM employees WHERE hire_date < DATE '2010-01-01';

-- 關聯刪除（EXISTS 效能優於 IN）
DELETE FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

SELECT FOR UPDATE — 列鎖定

在查詢時對選取列加排他鎖，防止併發修改：

SELECT * FROM employees WHERE dept_id = 10 FOR UPDATE [NOWAIT | WAIT n | SKIP LOCKED];

• NOWAIT：鎖被佔用立即報錯
• WAIT n：等待 n 秒
• SKIP LOCKED（11g+）：跳過已鎖定列

LOCK TABLE — 表級鎖定

LOCK TABLE table_name IN lock_mode [MODE] [NOWAIT];

• 鎖模式：ROW SHARE、ROW EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE
• DDL 操作需 EXCLUSIVE 鎖

NULL 值與 DML 的關鍵互動

• 算術運算遇 NULL 結果為 NULL：salary + bonus 若 bonus 為 NULL → NULL
• 正確做法：salary + NVL(bonus, 0) 或 COALESCE(bonus, 0)
• 比較運算：col = NULL 永遠為 FALSE，必須用 col IS NULL
• 聚合函數自動忽略 NULL（COUNT(*) 除外）

最佳實踐清單

1. 永遠在 INSERT/UPDATE 指定欄位清單
2. UPDATE/DELETE 前先用 SELECT 驗證影響範圍
3. 批次作業使用 FORALL (PL/SQL) 或分批 COMMIT 避免 UNDO 爆滿
4. 大量刪除考慮 CTAS (CREATE TABLE AS SELECT) 保留資料再 TRUNCATE
5. 合併操作優先用 MERGE (UPSERT) 替代分離 INSERT/UPDATE

結語：精通 DML 四大核心語句與 NULL 處理，是資料庫開發與維護的基本功。建議搭配 EXPLAIN PLAN 與 AUTOTRACE 驗證執行計畫，寫出高效能 SQL。