單行函數分類總覽
| 類別 | 代表函數 | 核心用途 |
|---|
| 字符函數 | 大小寫控制、字符操作 | 字串格式化、截取、替換、長度 |
| 數值函數 | ROUND、TRUNC、MOD | 四捨五入、截斷、取餘 |
| 日期函數 | MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY | 日期運算、邊界計算 |
| 轉換函數 | TO_CHAR、TO_NUMBER、TO_DATE | 三大類型互轉 |
| 通用函數 | NVL、DECODE、CASE | 空值處理、條件邏輯 |
1. 字符函數
大小寫控制
SELECT LOWER('SQL COURSE'), UPPER('sql course'), INITCAP('SQL course') FROM dual;
-- sql course | SQL COURSE | Sql Course
- `LOWER`：全轉小寫
- `UPPER`：全轉大寫
- `INITCAP`：每單詞首字母大寫，其餘小寫
實戰：不分大小寫查詢
SELECT * FROM emp WHERE LOWER(ename) = 'king';
SELECT * FROM emp WHERE ename = UPPER('king');
字符操作函數
| 函數 | 語法 | 範例 | 結果 |
|---|
| CONCAT | CONCAT(s1, s2) | CONCAT('Hello ', 'World') | Hello World |
| SUBSTR | SUBSTR(str, pos, len) | SUBSTR('hello', 1, 2) | he |
| LENGTH | LENGTH(str) | LENGTH('world') | 5 |
| REPLACE | REPLACE(str, old, new) | REPLACE('sql', 'q', 'X') | sXl |
反向截取 (負數位置)
SELECT ename, SUBSTR(ename, -3) FROM emp WHERE deptno = 10;
-- CLARK → ARK, KING → ING, MILLER → LER
2. 數值函數
| 函數 | 語法 | 範例 | 結果 | 說明 |
|---|
| ROUND | ROUND(n, [precision]) | ROUND(234.12, 2) | 234.12 | 保留小數位，四捨五入 |
| | ROUND(234.324) | 234 | 預設 precision=0 |
| | ROUND(234.25, -2) | 200 | 負數精度：對小數點左側捨入 |
| TRUNC | TRUNC(n, [precision]) | TRUNC(234.12, 2) | 234.12 | 直接截斷，不四捨五入 |
| | TRUNC(234.324) | 234 | 截去小數 |
| | TRUNC(248.25, -2) | 200 | 截去小數點左兩位 |
| MOD | MOD(m, n) | MOD(10, 3) | 1 | 取餘數 |
差異：`ROUND` 四捨五入，`TRUNC` 直接截斷。
3. 日期函數
日期運算規則
- 日期 ± 數字 = 日期 (數字代表天)
- 日期 - 日期 = 天數
- 日期 + 日期 = 無意義 (不允許)
實戰：員工入職月數
SELECT empno, ename, ROUND((SYSDATE - hiredate)/30) AS months
FROM emp;
專用日期函數
| 函數 | 用途 | 範例 | 結果 |
|---|
| MONTHS_BETWEEN | 兩日期間月數 | MONTHS_BETWEEN(SYSDATE, hiredate) | 399.77... |
| ADD_MONTHS | 加減月份 | ADD_MONTHS(SYSDATE, 4) | 4 個月後同一天 |
| NEXT_DAY | 下一個指定星期幾 | NEXT_DAY(SYSDATE, '星期三') | 下個三日期 |
| LAST_DAY | 該月最後一天 | LAST_DAY(SYSDATE) | 本月 30/31 號 |
4. 轉換函數
三大類型互轉關係
TO_DATE TO_NUMBER
DATE ------------> CHAR ------------> NUMBER
^ TO_CHAR TO_CHAR |
|_________________________________________|
隱式轉換 (不建議依賴)
TO_CHAR 格式化日期
SELECT empno, ename, TO_CHAR(hiredate, 'YYYY-MM-DD') FROM emp;
-- 1980-12-17, 1981-02-20, ...
| 格式代碼 | 含義 | 範例 |
|---|
| YYYY | 四位年 | 2024 |
| MM | 兩位月 | 01-12 |
| DD | 兩位日 | 01-31 |
| HH24 | 24 小時 | 00-23 |
| MI | 分鐘 | 00-59 |
| SS | 秒 | 00-59 |
TO_CHAR 格式化數字
SELECT sal, TO_CHAR(sal, '$99,999') FROM emp;
-- 800 → $800, 1600 → $1,600, 5000 → $5,000
- `9`：數字位 (前導空格)
- `0`：強制顯示 0
- `$`：美元符號
- `L`：本地貨幣符號 (由 NLS_CURRENCY 決定)
- `,`：千分位分隔
- `.`：小數點
- `FM`：去除前導/尾隨空格 (`FM$99,999`)
TO_NUMBER / TO_DATE
SELECT TO_NUMBER('34') + 46 FROM dual; -- 80
SELECT TO_DATE('2014-04-09', 'YYYY-MM-DD') FROM dual; -- 2014/04/09
5. 通用函數
NVL：空值替換
SELECT empno, (NVL(comm, 0) + sal) * 12 AS annual_sal FROM emp;
-- comm 為 NULL 視為 0 參與運算
DECODE：條件分支 (類似 switch-case)
SELECT DECODE(val, 1, '一', 2, '二', 3, '三', '其他') FROM dual;
-- val=1 → '一', val=2 → '二', val=3 → '三', 其他 → '其他'
CASE：標準 SQL 條件表達式
SELECT ename,
CASE ename
WHEN 'KING' THEN 'Hello'
WHEN 'FORD' THEN 'World'
WHEN 'MILLER' THEN 'HELLO WORLD'
ELSE ename
END AS greeting
FROM emp WHERE deptno = 10;
ENAME GREETING
------ ------------
CLARK CLARK
KING Hello
MILLER HELLO WORLD
兩種語法：
- 簡單 CASE：`CASE expr WHEN v1 THEN r1 ... ELSE re END`
- 搜尋 CASE：`CASE WHEN cond1 THEN r1 WHEN cond2 THEN r2 ... ELSE re END`
效能建議
- 避免在 `WHERE` 條件欄位上使用函數 (如 `WHERE UPPER(name) = 'KING`)，導致索引失效；改用函數索引 `CREATE INDEX idx_upper_name ON emp (UPPER(name))`。
- 大量資料轉換時，`TO_CHAR`/`TO_DATE` 耗 CPU，考慮在應用層格式化。
- `NVL`/`COALESCE` 在索引欄位使用同樣可能導致全表掃描。
