Oracle 單列函式 (Single-Row Functions) 逐列運算、逐列回傳，是 SQL 查詢與 PL/SQL 運算的基石。本文依官方分類整理六大類函式：數值、字元、日期、轉換、通用比較/邏輯、字串長度/位置，附語法、參數說明與實戰範例。
1. 數值函式
| 函式 | 功能 | 範例 | 結果 |
|---|
| ABS | 絕對值 | ABS(-15) | 15 |
| ROUND | 四捨五入 (支援日期) | ROUND(45.926, 2) / ROUND(SYSDATE, 'MONTH') | 45.93 / 月初 |
| TRUNC | 截斷 (支援日期) | TRUNC(45.926, 2) / TRUNC(SYSDATE, 'YEAR') | 45.92 / 年初 |
| CEIL / FLOOR | 向上/向下取整 | CEIL(3.1) / FLOOR(3.9) | 4 / 3 |
| MOD | 餘數 | MOD(10, 3) | 1 |
| POWER / SQRT / EXP / LN / LOG | 冪/根/指數/自然對數/對數 | POWER(2,3)=8 | - |
| SIGN | 正負號 | SIGN(-5) | -1 |
| 三角函式 | SIN/COS/TAN/ASIN/ACOS/ATAN/ATAN2/SINH/COSH/TANH | - | - |
2. 字元函式
| 函式 | 功能 | 範例 | 結果 |
|---|
| CONCAT / \|\| | 串接 | CONCAT('A','B') / 'A'\|\|'B' | 'AB' |
| SUBSTR | 子字串 (m=0 視同 1，負數從右算) | SUBSTR('ABCDE', 2, 3) / SUBSTR('ABCDE', -3, 2) | 'BCD' / 'CD' |
| INSTR | 搜尋位置 (回傳 0 表不存在) | INSTR('ABCDE', 'C') | 3 |
| REPLACE | 取代 | REPLACE('JACK', 'J', 'B') | 'BACK' |
| TRANSLATE | 字元級轉換 (一對一) | TRANSLATE('123', '12', 'AB') | 'AB3' |
| LTRIM / RTRIM / TRIM | 移除左/右/兩側指定字元 (預設空白) | TRIM(' AB ') | 'AB' |
| LPAD / RPAD | 左/右填充至指定長度 | LPAD('5', 3, '0') | '005' |
| UPPER / LOWER / INITCAP | 大寫/小寫/首字母大寫 | INITCAP('hello world') | 'Hello World' |
| CHR / ASCII | 字碼↔字元 | CHR(65)='A' | - |
| LENGTH / LENGTHB | 字元長度 / 位元組長度 (視字元集) | LENGTH('中文')=2 | - |
3. 日期函式
| 函式 | 功能 | 範例 | 結果 |
|---|
| SYSDATE | 目前日期時間 (Session 時區) | - | - |
| ADD_MONTHS | 加減月份 (自動處理月末) | ADD_MONTHS('2024-01-31', 1) | '2024-02-29' |
| MONTHS_BETWEEN | 兩日期月份差 (可為負) | MONTHS_BETWEEN('2024-12-31','2024-01-01') | ~11.97 |
| LAST_DAY | 該月最後一天 | LAST_DAY(SYSDATE) | - |
| NEXT_DAY | 指定星期的下一天 | NEXT_DAY(SYSDATE, 'FRIDAY') | - |
| ROUND / TRUNC | 日期四捨五入/截斷 (單位: YEAR, MONTH, DAY, HH, MI) | ROUND(SYSDATE, 'MONTH') | 月初 00:00 |
| EXTRACT | 擷取欄位 (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR...) | EXTRACT(MONTH FROM SYSDATE) | 數值 |
4. 轉換函式
| 函式 | 方向 | 語法 | 關鍵格式元素 |
|---|
| TO_CHAR | 日期/數值 → 字串 | TO_CHAR(dt, 'fmt') / TO_CHAR(num, 'fmt') | YYYY, MM, DD, HH24, MI, SS, FF / 9, 0, ., ,, $, L, MI, PR |
| TO_DATE | 字串 → 日期 | TO_DATE(str, 'fmt') | 同 TO_CHAR 日期部份 |
| TO_TIMESTAMP | 字串 → Timestamp | TO_TIMESTAMP(str, 'fmt') | 含 FF, TZH:TZM |
| TO_NUMBER | 字串 → 數值 | TO_NUMBER(str, 'fmt') | 同 TO_CHAR 數值部份 |
| TO_LOB | LONG → LOB | TO_LOB(long_col) | 僅用於 CREATE TABLE ... AS SELECT |
日期格式元素速查
YYYY / YEAR / YY / Y -- 年
MM / MON / MONTH -- 月
DD / DAY / DY -- 日/星期
HH24 / HH12 / HH -- 時
MI -- 分
SS -- 秒
FF[1-9] -- 小數秒
Q -- 季度
WW / IW / W -- 週數 (ISO/月內)
D / DD / DDD -- 週內/月內/年內第幾天
固定 Session 日期格式 (優先序高→低)
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';- 環境變數
NLS_DATE_FORMAT (Linux) / 登錄 (Windows)
- 參數檔
NLS_DATE_FORMAT
5. 通用比較/邏輯函式
| 函式 | 語法 | 邏輯等價 | 範例 |
|---|
| NVL | NVL(e1, e2) | IF e1 IS NULL THEN e2 ELSE e1 | NVL(comm, 0) |
| NVL2 | NVL2(e1, e2, e3) | IF e1 IS NOT NULL THEN e2 ELSE e3 | NVL2(comm, 'Y', 'N') |
| COALESCE | COALESCE(e1, e2, ...) | 第一個非 NULL | COALESCE(comm, bonus, 0) |
| NULLIF | NULLIF(e1, e2) | IF e1=e2 THEN NULL ELSE e1 | NULLIF(len, 0) |
| DECODE | DECODE(expr, s1, r1, s2, r2, ..., default) | CASE WHEN expr=s1 THEN r1 ... ELSE default | DECODE(deptno, 10, 'ACCT', 20, 'RES', 'OTH') |
| CASE | 標準 SQL 條件表達式 | CASE WHEN cond THEN result ... ELSE default END | 靈活、可讀性佳 |
6. 實戰組合範例
-- 顯示員工姓名、薪資(千分位)、入職年月、傭金狀態
SELECT last_name,
TO_CHAR(salary, 'L999,999.99') AS salary_fmt,
TO_CHAR(hire_date, 'YYYY-MM') AS hire_ym,
DECODE(SIGN(NVL(commission_pct,0)), 1, 'Y', 'N') AS has_comm
FROM employees
WHERE hire_date >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)
ORDER BY salary DESC;
單列函式是 SQL 表達力的核心。熟練掌握六大類函式語法、參數與組合技巧，能寫出簡潔、高效、可維護的查詢與 PL/SQL 程式碼。
