函數分類總覽
Oracle 內建函數分為兩大類：
- 單行函數：逐行操作，返回每行一個結果，可用於 SELECT、WHERE、ORDER BY、SET、VALUES 等子句
- 組函數（多行/聚合函數）：對多行數據操作，返回單一結果，常搭配 GROUP BY、HAVING 使用
單行函數詳解
1. 字符函數
| 函數 | 語法 | 說明 | 示例 |
|---|
| ASCII | ASCII(char) | 返回字符 ASCII 碼 | ASCII('A') → 65 |
| CHR | CHR(n) | 返回數字對應字符 | CHR(65) → 'A' |
| CONCAT | CONCAT(s1,s2) | 拼接字符串，等同 || | CONCAT('AB','C') → 'ABC' |
| INITCAP | INITCAP(s) | 每個單詞首字母大寫 | INITCAP('hello world') → 'Hello World' |
| INSTR | INSTR(s1,s2[,pos[,occur]]) | 查找子串位置，支援反向搜尋 | INSTR('Mississippi','i',3,3) → 11 |
| LENGTH | LENGTH(s) | 返回字符長度 | LENGTH('Oracle') → 6 |
| LOWER/UPPER | LOWER(s)/UPPER(s) | 大小寫轉換 | UPPER('abc') → 'ABC' |
| LPAD/RPAD | LPAD(s,n[,pad]) | 左/右填充至指定長度 | LPAD('5',3,'0') → '005' |
| LTRIM/RTRIM/TRIM | TRIM([LEADING|TRAILING|BOTH] c FROM s) | 修剪指定字符 | TRIM(' ABC ') → 'ABC' |
| REPLACE | REPLACE(s,old,new) | 替換子串 | REPLACE('JACK','J','B') → 'BACK' |
| SUBSTR/SUBSTRB | SUBSTR(s,pos[,len]) | 截取子串，支援負數從末尾計算 | SUBSTR('ABCDEF',2,3) → 'BCD' |
| SOUNDEX | SOUNDEX(s) | 返回發音代碼，模糊匹配利器 | SOUNDEX('SMITH') = SOUNDEX('SMYTHE') |
| TRANSLATE | TRANSLATE(s,from,to) | 字符級逐一替換 | TRANSLATE('123','12','AB') → 'AB3' |
2. 數字函數
| 函數 | 用途 | 示例 |
|---|
| ABS/CEIL/FLOOR/ROUND/TRUNC | 絕對值/向上/向下/四捨五入/截斷取整 | ROUND(123.456,2) → 123.46 |
| MOD | 求餘 | MOD(10,3) → 1 |
| POWER/SQRT/EXP/LN/LOG | 冪/平方根/指數/自然對數/任意底對數 | POWER(2,10) → 1024 |
| SIN/COS/TAN/ASIN/ACOS/ATAN | 三角/反三角函數（弧度制） | ACOS(-1) → 3.14159 |
| SIGN | 符號判斷 | SIGN(-5) → -1 |
3. 日期函數
| 函數 | 說明 | 示例 |
|---|
| SYSDATE | 當前日期時間 | SYSDATE → 2024-05-09 14:30:00 |
| ADD_MONTHS | 加減月份 | ADD_MONTHS(SYSDATE,3) |
| LAST_DAY | 月份最後一天 | LAST_DAY(SYSDATE) |
| MONTHS_BETWEEN | 兩日期月差 | MONTHS_BETWEEN(d1,d2) |
| NEXT_DAY | 下一個指定週幾 | NEXT_DAY(SYSDATE,'FRIDAY') |
| NEW_TIME | 時區轉換 | NEW_TIME(d,'GMT','PST') |
| ROUND/TRUNC | 日期捨入/截斷（按 fmt） | TRUNC(SYSDATE,'MM') → 月首日 |
4. 轉換函數
| 函數 | 用途 | 格式模型關鍵字 |
|---|
| TO_CHAR | 日期/數字 → 字符 | YYYY-MM-DD HH24:MI:SS, 999G999D99 |
| TO_DATE | 字符 → 日期 | YYYY-MM-DD, DD-MON-YYYY |
| TO_NUMBER | 字符 → 數字 | 999G999D99 |
| CHARTOROWID/ROWIDTOCHAR | ROWID ↔ 字符 | — |
| HEXTORAW/RAWTOHEX | 十六進制 ↔ RAW | — |
| CONVERT | 字符集轉換 | CONVERT(s,'US7ASCII','WE8ISO8859P1') |
5. 其他常用單行函數
DECODE(expr, search, result [, search, result]... [, default])：IF-THEN-ELSE 邏輯GREATEST/LEAST(expr1, expr2,...)：最大/最小值NVL(expr1, expr2)：NULL 替換，NVL(comm,0)USER/UID/USERENV('SESSIONID')：會話資訊VSIZE(expr)：內部表示位元組數DUMP(expr, fmt, start, len)：數據結構調試EMPTY_BLOB()/EMPTY_CLOB()：LOB 初始化BFILENAME(dir, file)：外部檔案定位器
組函數（聚合函數）詳解
| 函數 | 語法 | 說明 |
|---|
| AVG | AVG([DISTINCT|ALL] expr) | 平均值，預設 ALL |
| COUNT | COUNT({*|DISTINCT|ALL} expr) | 行數，* 含 NULL |
| MAX/MIN | MAX([DISTINCT|ALL] expr) | 最大/最小值，DISTINCT 無實際影響 |
| SUM | SUM([DISTINCT|ALL] expr) | 求和 |
| STDDEV | STDDEV([DISTINCT|ALL] expr) | 標準差（方差開根號） |
| VARIANCE | VARIANCE([DISTINCT|ALL] expr) | 統計方差 |
分組查詢進階
GROUP BY 基礎
SELECT deptno, COUNT(*), AVG(sal)
FROM emp
GROUP BY deptno;
HAVING 過濾分組
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) > 10000;
注意：WHERE 不能使用組函數，必須用 HAVING。
ORDER BY 排序分組結果
SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
ORDER BY cnt DESC;
函數嵌套與優先級
遵循「由內而外、由左至右」原則。常見嵌套模式：
- 組函數嵌套單行函數：
AVG(NVL(comm,0))
- 單行函數嵌套組函數：
TO_CHAR(SUM(sal),'$99,999')
- 多層嵌套：
GREATEST(COUNT(DISTINCT job), COUNT(DISTINCT mgr))
NULL 值處理核心原則
- 算術運算遇 NULL 結果為 NULL
- 多數單行函數遇 NULL 返回 NULL
- 例外：
NVL、DECODE、CONCAT、REPLACE、DUMP 可處理 NULL
- 組函數自動忽略 NULL（COUNT(*) 除外）
總結
熟練掌握單行函數與組函數，是編寫高效 SQL 的基石。重點記憶：字符處理（SUBSTR/REPLACE/TRIM）、日期運算（ADD_MONTHS/TRUNC）、類型轉換（TO_CHAR/TO_DATE）、NULL 處理（NVL/COALESCE）、聚合分組（GROUP BY/HAVING）。實戰中善用函數嵌套簡化邏輯，避免在 WHERE 中濫用函數導致索引失效。
