善用 Oracle 解析函數快速偵測序列間隙

資料庫表格中常使用數值序列，無論是自然產生的業務流水號，還是由 Oracle Sequence 物件分配的代理鍵，其核心目的皆為賦予每一筆資料唯一且遞增的身分識別。然而，在實際操作過程中，序列間隙（Gap）的產生卻在所難免。例如，預存程序從 Sequence 取得數值存入區域變數卻未實際使用，該數值便永久流失，無法歸還 Sequence，導致序列出現斷號。

雖然關聯式資料庫模型不強制要求序列連續無縫，但業務稽核或法規遵循往往需要追蹤缺失號碼。傳統做法多採用 PL/SQL 迴圈逐行比對，或是撰寫複雜的自我聯結（Self-Join）語句，前者效能低落，後者程式碼難以維護。

Oracle 解析函數提供了更優雅、高效的集合式解決方案。透過 LAG 或 LEAD 函數，我們能在單一 SQL 查詢中，同時存取當前列與前一列（或後一列）的值，輕鬆識別間隙。

核心語法與參數解析

解析函數的標準語法如下：

{LEAD | LAG} (value_expression, offset, default) OVER ([PARTITION BY expr] ORDER BY expr)

• value_expression：欲檢索的目標欄位（如序號欄位）。
• offset：往前/往後讀取的列數，預設為 1。
• default：當到達分區邊界無對應列時返回的預設值。
• PARTITION BY：可選，依指定欄位分組計算。
• ORDER BY：必要，定義排序基準，確保序列順序正確。

實戰範例：偵測感測器測量編號缺漏

假設有一張 sensor_log 表，記錄自動感測器產生的測量序號 measure_id。我們要找出是否有編號遺失。

WITH aquery AS (
  SELECT
    measure_id AS after_gap,
    LAG(measure_id, 1, 0) OVER (ORDER BY measure_id) AS before_gap
  FROM sensor_log
  WHERE measure_id > 0  -- 排除第一行的預設值 0
)
SELECT before_gap, after_gap
FROM aquery
WHERE after_gap - before_gap > 1
ORDER BY before_gap;

邏輯說明

1. WITH 子查詢：先建立虛擬結果集 aquery，包含當前行 after_gap 與前一行 before_gap 的序號。
2. LAG 函數：LAG(measure_id, 1, 0) 取得前一列的 measure_id，第一列無前驅時回傳 0。
3. 過濾首列：WHERE measure_id > 0 移除第一列產生的虛假間隙（0 → 首號）。
4. 主查詢判斷：若 after_gap - before_gap > 1，表示兩者之間存在至少一個缺號。

此法僅需單一掃描，無需迴圈或自我聯結，效能大幅優於傳統寫法，且程式碼簡潔易讀，極適合大量資料環境下的間隙稽核作業。