Oracle 110+ 內建函數完全分類速查手冊 (含範例與分析函數詳解)

前言

Oracle 提供豐富的內建函數涵蓋字串、數值、日期、轉換、彙總、分析/視窗等類別。本文將 110+ 常用函數依功能分類整理，附帶語法、範例與實務提示，適合開發者與 DBA 快速查閱。 (原文函數編號 100-110 為重複，已去重合併。)

一、字串函數

二、數值函數

三、日期時間函數

四、轉換與條件函數

五、彙總與分組函數

• AVG, SUM, COUNT, MAX, MIN - 基礎彙總
• STDDEV, STDDEV_POP, STDDEV_SAMP - 標準差 (總體/樣本)
• VARIANCE, VAR_POP, VAR_SAMP - 變異數
• MEDIAN - 中位數 (10g+)
• STATS_MODE - 眾數 (10g+)
• GROUPING / GROUPING_ID - 識別 GROUP BY ROLLUP/CUBE 產生的彙總列
• LISTAGG(expr, delimiter) WITHIN GROUP (ORDER BY ...) - 字串彙總 (11g+)，如 `LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename)` → `'ALLEN,BLAKE,CLARK...'`

六、分析/視窗函數 (Analytic/Window Functions) - 核心進階功能

語法：FUNCTION(...) OVER (PARTITION BY ... ORDER BY ... [WINDOWING_CLAUSE])。
不減少回傳列數，每列都可見群組內彙總/排序結果。

視窗子句 (Windowing Clause) 關鍵語法

OVER (
  PARTITION BY deptno           -- 分組
  ORDER BY sal                  -- 排序 (決定視窗起訖)
  ROWS BETWEEN 3 PRECEDING AND CURRENT ROW   -- 實體列數視窗
  RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING -- 數值範圍視窗
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 全分組
)

實戰範例：移動平均與同比

SELECT deptno, ename, sal,
       AVG(sal) OVER (PARTITION BY deptno ORDER BY hiredate
                      ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS mov_avg_3,
       LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS prev_sal,
       sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS diff_prev
FROM emp;

七、其他實用函數

• USER / UID - 當前用戶名/ID
• USERENV('SESSIONID'/'LANGUAGE'/'TERMINAL'/'ISDBA'...) - 會話環境資訊 (12c+ 建議用 `SYS_CONTEXT('USERENV',...)`)
• DUMP(expr, fmt, start, len) - 內部儲存結構診斷
• EMPTY_BLOB() / EMPTY_CLOB() - LOB 初始化
• BFILENAME(dir, file) - 外部二進位檔案定位器
• HEXTORAW / RAWTOHEX - 十六進位↔原始二進位
• CHARTOROWID / ROWIDTOCHAR - ROWID 轉換
• GREATEST / LEAST - 多參數最大/最小值 (字串比較依編碼)
• VSIZE(expr) - 內部儲存位元組數

使用建議與效能提示

1. 索引友善： `WHERE UPPER(name) = 'ABC'` 無法使用 `name` 索引 → 改用 `WHERE name = 'ABC'` (資料庫大小寫不敏感設定) 或建立函數索引 `CREATE INDEX idx_upper_name ON t(UPPER(name))`。
2. 隱式轉換陷阱： `WHERE date_col = '2024-01-01'` 依賴 `NLS_DATE_FORMAT` → **永遠用 `TO_DATE` 或 `DATE'2024-01-01'` 常值**。
3. 分析函數 vs GROUP BY： 需同時顯示明細與彙總 → 分析函數；僅需彙總 → `GROUP BY` 效能更好。
4. 視窗框架預設： `ORDER BY` 存在時預設 `RANGE UNBOUNDED PRECEDING AND CURRENT ROW` (累加)；無 `ORDER BY` 時為全分組。明確指定 `ROWS/RANGE` 避免誤解。
5. 大量資料排序： 分析函數需排序，大表建議先 `WHERE` 過濾或使用物化視圖預計算。