| 類別 | 特性 | 適用子句 |
|---|---|---|
| 單行函數 | 逐列運算，輸入一列回傳一值 | SELECT, WHERE, ORDER BY, SET, VALUES |
| 群組函數 | 多列聚合，輸入多列回傳單一值 | SELECT, HAVING (搭配 GROUP BY) |
| 函數 | 語法 | 說明 | 範例 |
|---|---|---|---|
| ASCII | ASCII(char) | 回傳首字元 ASCII 碼 | ASCII('A')=65 |
| CHR | CHR(n) | ASCII 碼轉字元 | CHR(65)='A' |
| CONCAT | CONCAT(s1,s2) | 串接字串 (等同 ||) | CONCAT('AB','C')='ABC' |
| INITCAP | INITCAP(s) | 每字首字母大寫 | INITCAP('hello world')='Hello World' |
| INSTR | INSTR(s, sub, [pos], [occ]) | 尋找子字串位置 | INSTR('Mississippi','i',3,3)=11 |
| LENGTH | LENGTH(s) | 字串長度 (字元數) | LENGTH('中文')=2 |
| LENGTHB | LENGTHB(s) | 字串長度 (位元組數) | LENGTHB('中文')=6 (UTF8) |
| LOWER/UPPER | LOWER(s)/UPPER(s) | 大小寫轉換 | UPPER('abc')='ABC' |
| LPAD/RPAD | LPAD(s,n,[pad]) | 左/右填充至指定長度 | LPAD('5',3,'0')='005' |
| LTRIM/RTRIM | LTRIM(s,[trim_set]) | 移除左/右側指定字元 | LTRIM('xyxHELLO','xy')='HELLO' |
| REPLACE | REPLACE(s, old, [new]) | 替換子字串 | REPLACE('JACK','J','B')='BACK' |
| SUBSTR | SUBSTR(s, pos, [len]) | 擷取子字串 | SUBSTR('ABCDE',2,3)='BCD' |
| TRANSLATE | TRANSLATE(s, from, to) | 字元級對照替換 | TRANSLATE('123','12','AB')='AB3' |
| TRIM | TRIM([BOTH|LEADING|TRAILING] c FROM s) | 標準修剪 | TRIM(' ABC ')='ABC' |
| SOUNDEX | SOUNDEX(s) | 發音相似度編碼 | SOUNDEX('SMITH')=SOUNDEX('SMYTHE') |
| 函數 | 語法 | 說明 |
|---|---|---|
| ABS | ABS(n) | 絕對值 |
| CEIL/FLOOR | CEIL(n)/FLOOR(n) | 向上/向下取整 |
| ROUND | ROUND(n, [precision]) | 四捨五入 (precision 可負) |
| TRUNC | TRUNC(n, [precision]) | 截斷 (不四捨五入) |
| MOD | MOD(m,n) | 餘數 |
| POWER | POWER(m,n) | m 的 n 次方 |
| SQRT | SQRT(n) | 平方根 |
| SIGN | SIGN(n) | 正負號 (-1/0/1) |
| EXP/LN/LOG | EXP(n), LN(n), LOG(base,n) | 指數/自然對數/指定底對數 |
| 三角函數 | SIN/COS/TAN/ASIN/ACOS/ATAN/SINH/COSH/TANH | 參數為弧度 |
| 函數 | 語法 | 說明 | 範例 |
|---|---|---|---|
| SYSDATE | SYSDATE | 當前日期時間 | |
| ADD_MONTHS | ADD_MONTHS(d, n) | 加減月份 | ADD_MONTHS(SYSDATE, 3) |
| LAST_DAY | LAST_DAY(d) | 該月最後一天 | LAST_DAY('2023-02-10')='2023-02-28' |
| MONTHS_BETWEEN | MONTHS_BETWEEN(d1, d2) | 兩日期月差 | MONTHS_BETWEEN('2023-12-31','2023-01-01')=11.97 |
| NEXT_DAY | NEXT_DAY(d, weekday) | 下一個指定週幾 | NEXT_DAY(SYSDATE, 'FRIDAY') |
| ROUND | ROUND(d, [fmt]) | 日期捨入 (fmt: YEAR/MONTH/DD/DY...) | ROUND(SYSDATE, 'MONTH')=月初 |
| TRUNC | TRUNC(d, [fmt]) | 日期截斷 | TRUNC(SYSDATE, 'DD')=今天 00:00:00 |
| NEW_TIME | NEW_TIME(d, tz1, tz2) | 時區轉換 (已棄用，用 FROM_TZ) |
| 元素 | 意義 | 範例 |
|---|---|---|
| YYYY | 4 位年份 | 2023 |
| MM | 月份 (01-12) | 07 |
| MON / MONTH | 月份縮寫/全名 | JUL / JULY |
| DD | 月份中的第幾天 | 15 |
| DAY / DY | 星期全名/縮寫 | MONDAY / MON |
| HH24 / HH / HH12 | 24/12 小時制 | 14 / 02 |
| MI | 分鐘 | 30 |
| SS | 秒 | 45 |
| AM / PM | 上午/下午標記 | PM |
| WW / IW | 年間第幾週 / ISO 週 | 28 |
| Q | 季度 | 3 |
| RM | 羅馬數字月份 | VII |
| 函數 | 語法 | 說明 |
|---|---|---|
| TO_CHAR | TO_CHAR(n|d, [fmt], [nls]) | 數值/日期 → 字串 |
| TO_NUMBER | TO_NUMBER(s, [fmt], [nls]) | 字串 → 數值 |
| TO_DATE | TO_DATE(s, [fmt], [nls]) | 字串 → 日期 |
| CAST | CAST(expr AS type) | ANSI 標準轉換 |
| HEXTORAW/RAWTOHEX | HEXTORAW(s)/RAWTOHEX(r) | 16 進位 ↔ RAW |
| CHARTOROWID/ROWIDTOCHAR | CHARTOROWID(s)/ROWIDTOCHAR(r) | ROWID ↔ CHAR |
| TO_MULTI_BYTE/TO_SINGLE_BYTE | TO_MULTI_BYTE(s)/TO_SINGLE_BYTE(s) | 單/多位元組字元轉換 |
| CONVERT | CONVERT(s, dest_charset, [src_charset]) | 字符集轉換 |
| 函數 | 語法 | 說明 |
|---|---|---|
| NVL | NVL(expr1, expr2) | expr1 為 NULL 回傳 expr2 |
| NVL2 | NVL2(expr1, expr2, expr3) | expr1 不為 NULL 回傳 expr2 否則 expr3 |
| COALESCE | COALESCE(expr1, expr2, ...) | 回傳第一個非 NULL (ANSI) |
| NULLIF | NULLIF(expr1, expr2) | 相等回傳 NULL 否則 expr1 |
| DECODE | DECODE(expr, search, result, [search, result...], [default]) | Oracle 專用 CASE 簡寫 |
| CASE | CASE WHEN cond THEN result ... [ELSE result] END | ANSI 標準條件 |
| GREATEST/LEAST | GREATEST(expr...)/LEAST(expr...) | 最大/最小值 |
| DUMP | DUMP(expr, [fmt], [start], [len]) | 內部表示資訊 |
| VSIZE | VSIZE(expr) | 內部儲存位元組數 |
| USER/UID | USER / UID | 當前用戶名/ID |
| USERENV | USERENV(parameter) | Session 資訊 (語言、終端、Instance 等) |
| SYS_CONTEXT | SYS_CONTEXT(namespace, parameter) | 增強版環境資訊 |
| EMPTY_BLOB/CLOB | EMPTY_BLOB() / EMPTY_CLOB() | 初始化 LOB 定位器 |
| BFILENAME | BFILENAME(dir, file) | 建立 BFILE 定位器 |
| 函數 | 語法 | 說明 |
|---|---|---|
| AVG | AVG([DISTINCT|ALL] expr) | 平均值 |
| COUNT | COUNT({*|DISTINCT|ALL} expr) | 計數 (* 含 NULL) |
| MAX/MIN | MAX(expr)/MIN(expr) | 最大/最小值 |
| SUM | SUM([DISTINCT|ALL] expr) | 總和 |
| STDDEV | STDDEV([DISTINCT|ALL] expr) | 標準差 |
| VARIANCE | VARIANCE([DISTINCT|ALL] expr) | 變異數 |
| MEDIAN | MEDIAN(expr) | 中位數 (10g+) |
| STATS_MODE | STATS_MODE(expr) | 眾數 (10g+) |
-- 正確：HAVING 過濾群組
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > DATE '2020-01-01' -- 列級過濾
GROUP BY dept_id
HAVING AVG(salary) > 5000 -- 群組級過濾
ORDER BY avg_sal DESC;
-- 巢狀函數：單行函數在群組函數中
SELECT dept_id,
COUNT(DISTINCT UPPER(job_id)) AS job_types,
ROUND(AVG(NVL(commission_pct, 0)), 4) AS avg_comm
FROM employees
GROUP BY dept_id;TRUNC(SYSDATE) - 7 (一週前)、ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) (上月第一天)ROW_NUMBER() OVER (ORDER BY id) (12c+ OFFSET ... FETCH)LISTAGG(col, ',') WITHIN GROUP (ORDER BY col) (11g+)REGEXP_SUBSTR/REGEXP_REPLACE/REGEXP_LIKE (10g+)JSON_VALUE/JSON_QUERY/JSON_TABLE (12c+)NVL / COALESCE 短路求值，參數順序影響效能PARALLEL 提示COUNT(*) vs COUNT(col)：前者含 NULL，後者不含，執行計畫可能不同結語：精通 Oracle 內建函數是寫出簡潔、高效 SQL 的關鍵。建議收藏本速查表，實戰中「查表確認語法」比「憑記憶試錯」快十倍。
