Oracle SQL/PL-SQL 重點整理

一、子查詢與運算符

關鍵字 意義 用法重點 範例說明
Subquery 查詢中的查詢 可放在 SELECT、WHERE、FROM 中 找出薪水高於平均者
ANY / SOME 任意一個符合即成立 通常搭配 =, >, <
> ANY ≈ > MIN(...)
< ANY ≈ < MAX(...)		 薪水高於某部門任意員工
ALL 必須全部符合 通常搭配 =, >, <
> ALL ≈ > MAX(...)
< ALL ≈ < MIN(...)		 薪水高於某部門所有員工
EXISTS 檢查子查詢是否有結果 通常搭配關聯子查詢
只關心「有沒有結果」		 判斷是否是主管

二、JOIN vs SET OPERATOR 效能比較

類型 處理邏輯 效能特性
JOIN 同時從多張表取資料，依條件比對 ✅ 通常比較快（有索引的話）
集合查詢 (Set Operator) 執行多個 SELECT，再合併結果 ⚠️ 通常比較慢（重複掃表 + 合併 + 去重）

集合查詢效能分析

運算子 效能 原因說明
UNION ALL ✅ 最快 不做排序、不去重，只單純把結果疊起來
UNION ⚠️ 較慢 需「排序 + 去重」整個結果集
INTERSECT ⚠️ 慢 需「排序 + 比對」兩邊的完整列
MINUS ⚠️ 慢 同樣要排序與比對整列資料

JOIN 類型效能

JOIN 類型 效能 特點
INNER JOIN ✅ 通常最快 有索引時最有效率
LEFT/RIGHT JOIN ⚠️ 中等 要保留一邊所有資料
FULL OUTER JOIN ❌ 較慢 兩邊全掃描再合併
CROSS JOIN ❌ 非常慢 產生笛卡兒積（乘法爆炸）

三、UNKNOWN（三值邏輯）

狀態 意義
TRUE 條件為真
FALSE 條件為假
UNKNOWN 無法判定真或假（通常因為涉及 NULL）

表達式結果範例

表達式 結果 原因說明
5 = 5 ✅ TRUE 兩邊相同
5 = 6 ❌ FALSE 不同
5 = NULL ❓ UNKNOWN 無法判定
NULL = NULL ❓ UNKNOWN 兩個未知值不能確定是否相等
salary > NULL ❓ UNKNOWN 無法比較
NULL IS NULL ✅ TRUE 唯一可確定的 NULL 判斷法

重點：WHERE 只會選出 TRUE 的資料，FALSE 和 UNKNOWN 都會被排除

四、PL/SQL 基礎

PL/SQL 程式結構

區塊 功能
DECLARE 宣告變數、常數、游標等（可省略）
BEGIN ... END; 程式主要執行區塊
EXCEPTION 錯誤發生時的處理（可省略）

PL/SQL 物件類型

類型 說明
Procedure（儲存程序） 執行一段邏輯（不一定回傳值）
Function（函數） 執行後回傳一個值
Trigger（觸發器） 當事件發生（如 INSERT）自動執行
Package（套件） 一組 Procedure + Function 的集合
Anonymous Block（匿名區塊） 一次性執行、不儲存的 PL/SQL 區塊

各資料庫的程式語言

資料庫系統 程式語言名稱 特性
Oracle PL/SQL 原始版本，功能最完整
MySQL SQL/PSM (Stored Program) 支援 DECLARE、IF、LOOP、CURSOR 等，語法略不同
PostgreSQL PL/pgSQL 模仿 PL/SQL，功能與 Oracle 類似
Microsoft SQL Server T-SQL (Transact-SQL) 微軟版本的擴充 SQL，有同樣的流程控制功能

五、PL/SQL 實務建議

類型 建議實作層 理由
商業邏輯（折扣規則、流程判斷） ✅ 應用程式層 容易版本控制、可重用、可水平擴充
資料操作（CRUD、JOIN） ✅ SQL 查詢層 資料庫本來就擅長查詢
批次資料處理、大量更新、彙整 ⚙️ 可考慮 PL/SQL 減少網路傳輸、效能較好
系統整體邏輯 ❌ 不建議全寫在資料庫 維護困難、難以橫向擴展、DevOps 不友好

產業使用現況

產業/系統 使用狀況 理由
銀行、金融 ✅ 高度使用 PL/SQL 數據安全、交易邏輯封裝於資料庫內部
政府、傳產 ERP ⚙️ 中度使用 舊系統多以 Oracle 為主，重用既有程式
新創/雲端服務 🚫 低使用率 採用微服務架構，邏輯分散在 API 層
電商、Web App 🚫 幾乎不用 改以 Redis / API / ORM 處理邏輯

六、異常處理（Exception）

異常分類

類型 說明 範例 捕捉方式
程序異常 由程式邏輯主動觸發，用 RAISE RAISE ex_salary_too_low; EXCEPTION WHEN ex_salary_too_low THEN ...
運行異常 程式執行過程自動發生 NO_DATA_FOUND、ZERO_DIVIDE EXCEPTION WHEN NO_DATA_FOUND THEN ...

PL/SQL vs 其他語言異常處理

PL/SQL Java/C#
BEGIN ... EXCEPTION ... END; try { ... } catch { ... }
RAISE throw
WHEN <exception> catch (<exception>)
WHEN OTHERS catch (Exception e)

七、存儲過程 vs 觸發器

特性 存儲過程 (Procedure) 觸發器 (Trigger)
執行方式 由應用程式或其他程序呼叫 自動觸發（事件驅動）
呼叫控制 可多次呼叫 無法直接呼叫，依事件發生
用途 封裝商業邏輯、批次處理、可重複運算 強制資料完整性、審計、事件自動處理
參數 可以有輸入/輸出參數 通常用 :NEW / :OLD 讀取資料
效能 由使用頻率決定，通常可控 過多或複雜會降低效能
可測試性 容易測試、除錯 不易測試，需模擬事件

觸發器觸發事件

事件類型 說明
INSERT 當有新資料新增到資料表時觸發
UPDATE 當資料表的資料被修改時觸發
DELETE 當資料被刪除時觸發
SELECT ❌ 不會觸發（只讀取資料不改變資料）

觸發器使用建議

情況 建議使用觸發器嗎？ 說明
審計/記錄歷史 ✅ 可以 例如記錄誰修改了哪筆資料
資料驗證（簡單檢查） ⚠️ 小心 複雜邏輯盡量放應用程式
批次資料處理 ❌ 不建議 用單一 SQL / 存儲過程效率高
核心商業邏輯 ❌ 不建議 應放在應用層，方便測試與維護

八、索引（Index）

索引適用情境

情境 建索引建議 說明
查詢頻繁的欄位 ✅ 建索引 用來加速查詢、JOIN、排序、聚合
高選擇性欄位（值多樣） ✅ 建索引 如 employee_id、order_id
經常更新（UPDATE）的欄位 ❌ 不建索引（或慎用） 每次更新都要重建或調整索引
低基數欄位（值少） ⚠️ 慎用 如性別（男/女）、狀態（Y/N），B-tree 索引效率低
極少查詢的欄位 ❌ 不建索引 不查的欄位建索引是浪費空間
主鍵、唯一值 ✅ 建索引 唯一索引自動維護

九、使用者與權限管理

使用者 vs 角色

特性 使用者（User） 角色（Role）
是什麼 資料庫帳號 權限集合
可以登入資料庫嗎 ✅ 可以 ❌ 一般情況不能登入
擁有物件嗎 ✅ 可以擁有表格、視圖、程序等 ❌ 沒有物件，只存權限
授權方式 可直接授權，也可透過角色授權 將權限授給角色，再授給使用者
管理方式 個別管理帳號和權限 集中管理權限，方便多帳號授權

Profile（資源配置）

參數類型 參數名稱 說明
資源限制 SESSIONS_PER_USER 同時允許登入的會話數量
CPU_PER_SESSION 單個會話最多使用的 CPU 時間（秒）
CPU_PER_CALL 單次 SQL 執行最多使用 CPU 時間
CONNECT_TIME 單次連線允許的最長時間（分鐘）
IDLE_TIME 連線空閒時間限制（分鐘）
LOGICAL_READS_PER_SESSION 單次會話可讀取的邏輯資料塊數量
PRIVATE_SGA 每個使用者可使用的 SGA 資源大小（KB）
密碼策略 PASSWORD_LIFE_TIME 密碼有效期（天）
PASSWORD_GRACE_TIME 密碼過期後寬限天數
PASSWORD_REUSE_TIME 密碼多久不能重複使用（天）
PASSWORD_REUSE_MAX 密碼最多能重複使用次數
FAILED_LOGIN_ATTEMPTS 登入失敗次數限制
PASSWORD_LOCK_TIME 密碼鎖定時間（分鐘/天）

查詢 Profile 資訊

查詢目的 SQL 語句
查看使用者所屬 Profile SELECT username, profile FROM dba_users WHERE username = 'APP_USER';
查看 Profile 資源限制 SELECT * FROM dba_profiles WHERE profile = 'APP_PROFILE';

十、事務與鎖

ACID 特性

特性 說明
Atomicity（原子性） 全部成功或全部失敗
Consistency（一致性） 資料庫從一個一致狀態轉換到另一個一致狀態
Isolation（隔離性） 同時多個事務不會互相干擾
Durability（持久性） 提交後資料不會消失

鎖的類型

類型 作用 同時可持有 可更新資料 說明
共享鎖 (S Lock) 保護資料被讀取 ✅ 多個 session ❌ 不能修改 允許多個使用者同時讀取資料，但不能修改（讀鎖）
獨佔鎖 (X Lock) 保護資料被修改 ❌ 一個 session ✅ 可以修改 持有者可以修改資料，其他人不能讀也不能改（寫鎖）
Oracle 自動加在 DML 操作上
死鎖 (Deadlock) 互相等待鎖，無法前進 ❌ - ❌ - 兩個或以上的 session 互相等待對方釋放鎖
Oracle 會自動偵測並終止其中一個 session

避免死鎖建議

十一、重要概念總結

概念 重點說明
JOIN vs SET OPERATOR JOIN 把表連起來（關係型）；SET OPERATOR 把查詢疊起來（結果型）
JOIN 通常效能更好（有索引時）
UNKNOWN SQL 是三值邏輯（TRUE/FALSE/UNKNOWN）
比較中有 NULL → 結果是 UNKNOWN
WHERE 只選出 TRUE，UNKNOWN 會被排除
PL/SQL Oracle 的「可編程 SQL」語言
用來在資料庫內寫流程控制、封裝商業邏輯、觸發事件
不是用來取代程式邏輯，而是「輔助資料處理」
存儲過程 需明確呼叫，用於封裝邏輯、批次處理
容易測試、除錯
觸發器 自動觸發（事件驅動），無法直接呼叫
用於資料完整性、審計
業界不建議濫用（維護困難、效能影響）
索引 為「查詢」加速設計的，不是為「寫入」優化
常查詢、高選擇性欄位適合建索引
經常更新、低基數欄位不適合
使用者與角色 使用者 = 帳號；角色 = 權限包
用角色集中管理權限，方便多帳號授權
Profile 使用者的資源/安全設定
控制資源限制（CPU、會話、連線時間）和密碼策略
事務 一組 SQL 操作，要麼全部成功，要麼全部失敗
遵循 ACID 特性
用 COMMIT 提交，ROLLBACK 回滾
鎖 控制同時存取資料的機制
共享鎖（多人可讀）、獨佔鎖（一人可寫）、死鎖（互相等待）

十二、效能優化重點

項目 建議
索引 JOIN 條件欄位若有索引會非常快
若沒有索引，JOIN 可能反而比集合查詢慢
UNION vs UNION ALL 若不需要去重，用 UNION ALL 效能好 5～10 倍
避免 MINUS / INTERSECT 可改寫為 LEFT JOIN + IS NULL 或 INNER JOIN，效能更高
PL/SQL 使用時機 適合批次型任務（大量資料處理）
不適合業務邏輯（應放應用層）
觸發器 FOR EACH ROW 對大量資料會大幅拖慢效能
避免過多或複雜的觸發器

十三、SQL 查詢檢查項目

檢查項目 方法
UPDATE 是否成功 用 SQL%ROWCOUNT 查看影響的行數
或程式語言對應 API（如 JDBC 的 executeUpdate()）
NULL 值處理 用 IS NULL 或 IS NOT NULL
用 NVL / COALESCE 將 NULL 轉換成具體值
查看使用者 Profile SELECT username, profile FROM dba_users
查看 Profile 設定 SELECT * FROM dba_profiles WHERE profile = 'PROFILE_NAME'