巧用 Oracle 解析函數高效檢測序列間隙

序列間隙產生的原因與傳統檢測方法的不足

數據庫表中常使用值序列（自然產生或由 Oracle Sequence 分配）為每行分配唯一遞增編號。間隙產生原因多樣：如存儲過程從序列取值賦給局部變量卻未使用，該值即丟失且無法回歸序列。雖然關係模型不要求連續無間隙，但業務有時需要知道具體哪些數字缺失。

傳統方法：

• PL/SQL 循環：在已排序游標中循環，用局部變量存儲並比較每行與前一行。缺點：效率低、速度慢。
• 自連接：對表自連接，按標準匹配每行與前一行。缺點：代碼難寫、維護困難。

Oracle 解析函數：高效優雅的解決方案

Oracle 解析函數提供了更快捷的方法，在使用完整面向集合的 SQL 處理時，仍能通過 LEAD（下一行）或 LAG（前一行）訪問相鄰行的值。

語法格式

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

• value_expression：要檢索的列。
• offset：向前/向後讀取的行數。
• default：到達分區開頭/結尾（無匹配行）時返回的值。

實戰案例：傳感器測量值編號間隙檢測

場景：工作現場傳感器自動收集數據，自動為每個測量值編號，需找出缺失的編號。

1. 使用 LAG 函數，按測量值編號排序，偏移量設為 1，使每行與前一行對應。第一行無前行，返回默認值 0（間隙不可能發生在第一行，故通過 WHERE measure_id > 0 過濾）。
2. 得到虛擬行集：顯示前一行編號 (before_gap) 和當前行編號 (after_gap)。
3. 若差值大於 1，則兩行之間存在間隙。
4. 使用 WITH 子句將解析子查詢命名為 aquery，主查詢的 WHERE 子句可直接訪問其列。

核心 SQL 示例

WITH aquery AS (
  SELECT measure_id,
         LAG(measure_id, 1, 0) OVER (ORDER BY measure_id) AS prev_id
  FROM sensor_data
)
SELECT prev_id AS before_gap, measure_id AS after_gap
FROM aquery
WHERE measure_id > 0
  AND measure_id - prev_id > 1
ORDER BY measure_id;

此方法代碼簡潔、性能優異，避免了過程式循環或複雜自連接，是檢測序列間隙的最佳實踐。