Oracle 入門第一課：SQL 分類、DDL/DML/DCL/TCL 核心語法與權限控制速成

Oracle SQL 指令依功能分為四大類：DQL（查詢）、DDL（定義）、DML（操作）、DCL（控制）、TCL（交易）。本課程以標準語法、實用範例、權限模型，帶你在 15 分鐘建立完整 SQL 認知框架。

一、SQL 五大分類總覽

二、DQL：SELECT 核心語法

SELECT [DISTINCT] {* | column [alias], expr [alias] ...}
FROM   table [alias]
[WHERE  condition]
[GROUP BY expr [, expr ...]]
[HAVING condition]
[ORDER BY expr [ASC|DESC] [NULLS FIRST|LAST] [, ...]];

• 不區分大小寫；關鍵字不可縮寫/斷行；可跨行書寫。
• 支援算術運算子 + - * /、串接 ||、字面值、NULL 處理。
• JOIN：INNER/LEFT/RIGHT/FULL OUTER JOIN、CROSS JOIN、NATURAL JOIN。

三、DDL：物件建立/修改/刪除

-- 建表
CREATE TABLE emp (
  empno NUMBER(4) PRIMARY KEY,
  ename VARCHAR2(10) NOT NULL,
  job   VARCHAR2(9),
  mgr   NUMBER(4) REFERENCES emp(empno),
  hiredate DATE DEFAULT SYSDATE,
  sal   NUMBER(7,2) CHECK (sal > 0),
  comm  NUMBER(7,2),
  deptno NUMBER(2) REFERENCES dept(deptno)
);

-- 修改表
ALTER TABLE emp ADD (email VARCHAR2(50));
ALTER TABLE emp MODIFY (ename VARCHAR2(30));
ALTER TABLE emp DROP COLUMN email;
ALTER TABLE emp RENAME COLUMN ename TO emp_name;

-- 刪除/截斷/重命名
DROP TABLE emp PURGE;
TRUNCATE TABLE emp;          -- 保留結構、釋放空間、不產生 UNDO、不可回滾
RENAME emp TO employee;

四、DML：資料增刪改

-- INSERT
INSERT INTO emp (empno, ename, job, sal) VALUES (1001, 'Wang', 'CLERK', 3000);
INSERT INTO emp SELECT 1002, 'Li', 'CLERK', 2500 FROM dual;

-- UPDATE
UPDATE emp SET sal = sal * 1.1 WHERE job = 'CLERK';

-- DELETE
DELETE FROM emp WHERE empno = 1001;

-- MERGE (UPSERT)
MERGE INTO emp e
USING (SELECT 1003 empno, 'Zhang' ename, 'CLERK' job, 2800 sal FROM dual) s
ON (e.empno = s.empno)
WHEN MATCHED THEN UPDATE SET e.sal = s.sal
WHEN NOT MATCHED THEN INSERT (empno, ename, job, sal) VALUES (s.empno, s.ename, s.job, s.sal);

五、DCL：權限授予與回收

1. 系統權限（System Privileges）

常用角色組合：

• CONNECT：建立會話、建立表/視圖/序列/同義詞等基本開發權限。
• RESOURCE：建立觸發器、程序、類型、索引等進階開發權限。
• DBA：所有系統權限、管理資料庫。

GRANT CONNECT, RESOURCE TO dev_user;
GRANT CREATE VIEW, CREATE PROCEDURE TO dev_user;
REVOKE CREATE PROCEDURE FROM dev_user;

2. 物件權限（Object Privileges）

GRANT SELECT, INSERT, UPDATE, DELETE ON emp TO dev_user;
GRANT ALL ON emp TO dev_user;           -- 所有物件權限
GRANT SELECT ON emp TO role_readonly;   -- 授予角色
REVOKE DELETE ON emp FROM dev_user;

六、TCL：交易控制

-- 交易隱含開始於第一條 DML
INSERT INTO emp ...;
UPDATE emp ...;
SAVEPOINT before_delete;
DELETE FROM emp WHERE ...;
-- 發現錯誤
ROLLBACK TO SAVEPOINT before_delete;
-- 確認無誤
COMMIT;  -- 永久生效
-- 或完全放棄
ROLLBACK;  -- 回到交易起點

• DDL/DCL 執行前會隱含 COMMIT，結束前一筆交易。
• SET AUTOCOMMIT ON/OFF 控制 SQL*Plus 行為；JDBC 預設 autoCommit=true。

七、SELECT 執行順序（邏輯 vs 物理）

1. FROM / JOIN（確定資料來源）
2. WHERE（列過濾）
3. GROUP BY（分組）
4. HAVING（分組過濾）
5. SELECT（投影、運算、別名）
6. ORDER BY（排序）
7. OFFSET / FETCH / ROWNUM（分頁）

理解邏輯順序能避免「WHERE 不能用別名」「HAVING 必須跟 GROUP BY」等常見錯誤。

八、實戰練習建議

1. 建立練習 Schema：CREATE USER learn IDENTIFIED BY learn DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CONNECT, RESOURCE TO learn;
2. 依序練習：建表 → 插資料 → 查詢 (WHERE/ORDER BY/JOIN/GROUP BY) → 修改/刪除 → 加索引/約束 → 授權/回收 → 交易控制。
3. 使用 EXPLAIN PLAN 觀察執行計畫，養成效能意識。

掌握五大分類核心語法與權限模型，是 Oracle 開發、管理、調優的基石。建議將本文列印為單頁速查表，貼在螢幕旁，寫 SQL 時隨時對照，養成標準、規範的編碼習慣。