單行函數 vs 群組函數
- 單行函數：逐行運算，回傳單值，可用於 SELECT、WHERE、ORDER BY、SET、VALUES 子句。
- 群組函數：多行聚合回傳單值，用於 SELECT、HAVING，常搭配 GROUP BY。
空值處理核心：NVL / NVL2 / COALESCE
NVL(expr1, expr2) -- expr1 為 NULL 回傳 expr2
NVL2(expr1, expr2, expr3) -- expr1 不為 NULL 回傳 expr2，否則 expr3
COALESCE(e1, e2, ...) -- 回傳第一個非 NULL 參數（ANSI 標準）
範例：salary + NVL(bonus,0) 避免 NULL 導致整式為 NULL。
字串函數精選
| 函數 | 用途 | 範例 |
|---|
| INITCAP | 單字首字大寫 | INITCAP('oracle db') → 'Oracle Db' |
| INSTR / INSTRB | 尋找子字串位置（字元/位元組） | INSTR('Mississippi','i',3,3) → 11 |
| LENGTH / LENGTHB | 長度 | LENGTH('中文') → 2 / LENGTHB → 6 (UTF8) |
| LPAD / RPAD | 左/右填充至指定長度 | LPAD('9',5,'0') → '00009' |
| LTRIM / RTRIM / TRIM | 移除前導/尾隨/兩側字元 | TRIM(' abc ') → 'abc' |
| REPLACE | 字串替換 | REPLACE('jack','j','J') → 'Jack' |
| SUBSTR / SUBSTRB | 子字串擷取 | SUBSTR('abcdef',2,3) → 'bcd' |
| TRANSLATE | 字元級轉換 | TRANSLATE('abc','ab','12') → '12c' |
| SOUNDEX | 發音相似比對 | SOUNDEX('Smith') = SOUNDEX('Smythe') |
數值函數精選
- ROUND/TRUNC：四捨五入/截斷（支援負數位數，如 ROUND(12345,-2)=12300）。
- CEIL/FLOOR：向上/向下取整。
- MOD：餘數。POWER：冪次。SQRT：平方根。
- 三角函數：SIN/COS/TAN/ASIN/ACOS/ATAN（參數為弧度）。
- 對數：LN (自然)、LOG(base, n)。
- SIGN：正負號（-1/0/1）。
日期函數精選
| 函數 | 說明 | 範例 |
|---|
| SYSDATE | 當前日期時間 | |
| ADD_MONTHS(d, n) | 加減月份 | ADD_MONTHS(SYSDATE, -3) |
| LAST_DAY(d) | 該月最後一天 | LAST_DAY('2024-02-10') → 2024-02-29 |
| MONTHS_BETWEEN(d1, d2) | 月份差（含小數） | |
| NEXT_DAY(d, '星期') | 下一個指定週幾 | NEXT_DAY(SYSDATE, 'FRIDAY') |
| ROUND(d, 'fmt') / TRUNC(d, 'fmt') | 日期捨入/截斷 | TRUNC(SYSDATE,'MM') → 本月 1 號 00:00 |
| NEW_TIME(d, tz1, tz2) | 時區轉換 | |
轉換函數
- TO_CHAR(date/num, 'fmt', 'nls')：日期/數值 → 字串。
- TO_DATE(char, 'fmt', 'nls')：字串 → 日期。
- TO_NUMBER(char, 'fmt', 'nls')：字串 → 數值。
- CAST(expr AS type)：ANSI 標準轉型，支援集合型別。
- HEXTORAW / RAWTOHEX / CHARTOROWID / ROWIDTOCHAR：二進制/ROWID 互轉。
群組函數
| 函數 | 說明 |
|---|
| AVG / SUM / MIN / MAX | 基本聚合，支援 DISTINCT/ALL |
| COUNT(*) / COUNT(expr) | * 計列數（含 NULL），expr 計非 NULL 個數 |
| STDDEV / VARIANCE | 標準差 / 變異數 |
| GROUPING / GROUPING_ID | 識別 ROLLUP/CUBE 超級聚合行 |
進階：GROUP BY / HAVING / 巢狀函數
-- 先 WHERE 過濾再 GROUP BY，效率遠優於 HAVING
SELECT job, AVG(sal)
FROM emp
WHERE job IN ('PRESIDENT','MANAGER')
GROUP BY job;
-- 巢狀：群組函數內嵌單行函數
SELECT deptno, GREATEST(COUNT(DISTINCT job), COUNT(DISTINCT mgr)) max_distinct
FROM emp
GROUP BY deptno;
常用格式模板
-- 日期
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
-- 數值千分位、貨幣
TO_CHAR(12345.6, 'FM99,999.00', 'NLS_NUMERIC_CHARACTERS=''.,'' NLS_CURRENCY=''$''')
- 上一篇文章： 善用 Oracle 動態效能視圖採集 SQL 調優數據：V$SQL、V$SQLAREA、V$SQLSTATS 實戰指南
- 下一篇文章： 打造可重用 PL/SQL 訊息輸出套件：從簡單 DBMS_OUTPUT 到企業級日誌庫
Copyright ©
程式碼之家 All Rights Reserved
