前言
Oracle 提供豐富的單列函數，掌握核心函數能大幅簡化 SQL、提升效能。本文系統整理六大類函數：語法、參數、回傳值、實戰範例與避坑指南。
一、數值函數
| 函數 | 語法 | 說明 | 範例 |
|---|
| ABS | ABS(n) | 絕對值 | ABS(-1000000.01) → 1000000.01 |
| ROUND | ROUND(n[,m]) | 四捨五入，m 為小數位 (負數向左) | ROUND(23.56,1)=23.6, ROUND(23.56,-1)=20 |
| TRUNC | TRUNC(n[,m]) | 截斷，不四捨五入 | TRUNC(23.56,1)=23.5, TRUNC(23.56,-1)=20 |
| CEIL | CEIL(n) | ≥n 的最小整數 | CEIL(18.2)=19, CEIL(-18.2)=-18 |
| FLOOR | FLOOR(n) | ≤n 的最大整數 | FLOOR(2.2)=2, FLOOR(-2.2)=-3 |
| MOD | MOD(n1,n2) | 餘數，n2=0 回傳 n1 | MOD(24,5)=4 |
| POWER | POWER(n1,n2) | n1 的 n2 次方 | POWER(2,3)=8 |
| SQRT | SQRT(n) | 平方根 (n≥0) | SQRT(0.1)=0.316... |
| EXP | EXP(n) | e 的 n 次方 | EXP(1)=2.718... |
| LN | LN(n) | 自然對數 (n>0) | LN(0.9)=-0.105... |
| LOG | LOG(b,n) | 以 b 為底 n 的對數 | LOG(10,100)=2 |
| SIGN | SIGN(n) | 正負號：1/0/-1 | SIGN(-14)=-1 |
| BITAND | BITAND(n1,n2) | 位元 AND 運算 | BITAND(3,9)=1 (0011 & 1001 = 0001) |
| BIN_TO_NUM | BIN_TO_NUM(b1,b2...) | 二進位位元轉十進位 | BIN_TO_NUM(1,0,1)=5 |
| 三角函數 | SIN/COS/TAN/ASIN/ACOS/ATAN/ATAN2/SINH/COSH/TANH | 弧度制 | SIN(10), ACOS(0.9) |
二、字元函數 (回傳字元)
| 函數 | 語法 | 說明 | 範例 |
|---|
| LOWER/UPPER | LOWER(c), UPPER(c) | 大小寫轉換 | UPPER('abc')='ABC' |
| INITCAP | INITCAP(c) | 每單字首字大寫 | INITCAP('hello world')='Hello World' |
| NLS_INITCAP | NLS_INITCAP(c[,nls]) | 支援多語言排序規則 | NLS_INITCAP('中華minzhu') |
| LPAD | LPAD(c1,n[,c2]) | 左填充至長度 n | LPAD('abc',6,'-')='---abc' |
| RPAD | RPAD(c1,n[,c2]) | 右填充至長度 n | RPAD('abc',6,'-')='abc---' |
| LTRIM | LTRIM(c1[,c2]) | 移除左側字元 (預設空格) | LTRIM(' abc')='abc', LTRIM('xyabc','xy')='abc' |
| RTRIM | RTRIM(c1[,c2]) | 移除右側字元 | RTRIM('abc ' )='abc' |
| TRIM | TRIM([LEADING|TRAILING|BOTH] c2 FROM c1) | 移除首/尾/兩側指定字元 | TRIM('W' FROM 'WabcW')='abc' |
| REPLACE | REPLACE(c1,c2[,c3]) | 替換 c2 為 c3 (c3 為空則刪除) | REPLACE('abc','b','X')='aXc' |
| TRANSLATE | TRANSLATE(c1,c2,c3) | 字元對字元對應替換 (非字串替換) | TRANSLATE('abc','ab','12')='12c' |
| SUBSTR | SUBSTR(c,pos[,len]) | 擷取子字串，pos<0 從右算起 | SUBSTR('abcdef',-3,2)='de' |
| CONCAT | CONCAT(c1,c2) | 串接 (等同 ||) | CONCAT('a','b')='ab' |
| CHR | CHR(n [USING NCHAR_CS]) | 數值對應字元集字元 | CHR(65)='A' |
| SOUNDEX | SOUNDEX(c) | 語音編碼 (同音異字比對) | SOUNDEX('dog')='D200', SOUNDEX('boy')='B000' |
SUBSTR 陷阱總結
pos=0 視同 pos=1pos>0：從左往右；pos<0：從右往左pos > LENGTH(c) 回傳 NULLlen < 1 回傳 NULL
三、字元函數 (回傳數值)
| 函數 | 語法 | 說明 | 範例 |
|---|
| INSTR | INSTR(c1,c2[,pos[,occur]]) | 搜尋 c2 在 c1 位置，pos<0 從右找 | INSTR('abcdef','c',-1)=3 |
|---|
| LENGTH | LENGTH(c) | 字串長度 (字元數) | LENGTH('中文')=2 |
| ASCII | ASCII(c) | 首字元對應數值 | ASCII('_')=95 |
| VSIZE | VSIZE(c) | 位元組數 | VSIZE('abc中')=7 (UTF8) |
四、日期函數
| 函數 | 語法 | 說明 | 範例 |
|---|
| SYSDATE | SYSDATE | 資料庫伺服器當前日期時間 | 2024-01-15 14:30:00 |
| CURRENT_DATE | CURRENT_DATE | Session 時區當前日期 | 可能與 SYSDATE 差 1 秒 |
| SYSTIMESTAMP | SYSTIMESTAMP([prec]) | 資料庫時區 + 毫秒精度 | SYSTIMESTAMP(3) |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([prec]) | Session 時區 + 毫秒 | CURRENT_TIMESTAMP(6) |
| LOCALTIMESTAMP | LOCALTIMESTAMP([prec]) | Session 時區 (無時區資訊) | LOCALTIMESTAMP(3) |
| ADD_MONTHS | ADD_MONTHS(d,n) | 加減月份 (n 可負) | ADD_MONTHS(SYSDATE,-12) |
| MONTHS_BETWEEN | MONTHS_BETWEEN(d1,d2) | 月份差 (可負、含小數) | MONTHS_BETWEEN(d1,d2) |
| LAST_DAY | LAST_DAY(d) | 該月最後一天 | LAST_DAY(SYSDATE) |
| NEXT_DAY | NEXT_DAY(d,day) | 下一個指定週幾 (需符合 NLS_LANGUAGE) | NEXT_DAY(SYSDATE,'星期一') |
| ROUND | ROUND(d[,fmt]) | 日期四捨五入 (fmt: YEAR, MONTH, DAY, HH...) | ROUND(SYSDATE,'MONTH') |
| TRUNC | TRUNC(d[,fmt]) | 日期截斷 | TRUNC(SYSDATE,'HH24') |
| EXTRACT | EXTRACT(field FROM d) | 擷取年/月/日/時/分/秒 | EXTRACT(YEAR FROM SYSDATE) |
| TO_TIMESTAMP | TO_TIMESTAMP(c[,fmt]) | 字串轉 TIMESTAMP | TO_TIMESTAMP('2024-01-15','YYYY-MM-DD') |
NEXT_DAY 時區陷阱
-- Session NLS_LANGUAGE = SIMPLIFIED CHINESE
NEXT_DAY(SYSDATE, '星期一') -- 正確
NEXT_DAY(SYSDATE, 'Monday') -- ORA-01846: not a valid day of the week
五、轉換函數
| 函數 | 語法 | 說明 | 範例 |
|---|
| TO_CHAR | TO_CHAR(n|d[,fmt[,nls]]) | 數值/日期 → 字串 | TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') |
| TO_NUMBER | TO_NUMBER(c[,fmt[,nls]]) | 字串 → 數值 | TO_NUMBER('-100.00','9G999D99') |
| TO_DATE | TO_DATE(c[,fmt[,nls]]) | 字串 → DATE | TO_DATE('2024-01-15','YYYY-MM-DD') |
| TO_TIMESTAMP | TO_TIMESTAMP(c[,fmt]) | 字串 → TIMESTAMP | TO_TIMESTAMP('2024-01-15 14:30','YYYY-MM-DD HH24:MI') |
| CAST | CAST(expr AS type) | 標準 SQL 轉型 | CAST('123' AS NUMBER(5)) |
| BIN_TO_NUM | BIN_TO_NUM(b1,b2...) | 位元向量轉數值 | BIN_TO_NUM(1,1,0)=6 |
| CHARTOROWID | CHARTOROWID(c) | 字串 → ROWID | CHARTOROWID('AAAR...') |
| ROWIDTOCHAR | ROWIDTOCHAR(rowid) | ROWID → VARCHAR2(18) | ROWIDTOCHAR(rowid) |
| TO_MULTI_BYTE | TO_MULTI_BYTE(c) | 半形 → 全形 | TO_MULTI_BYTE('ABC') |
| TO_SINGLE_BYTE | TO_SINGLE_BYTE(c) | 全形 → 半形 | TO_SINGLE_BYTE('ＡＢＣ') |
TO_DATE 'J' 格式 (Julian Day)
-- Julian Day 範圍：1 (BC 4712-01-01) ~ 5373484 (AD 9999-12-31)
SELECT TO_DATE(2454336, 'J') FROM DUAL; -- 2007-08-23
SELECT TO_CHAR(TO_DATE('9999-12-31','YYYY-MM-DD'), 'J') FROM DUAL; -- 5373484
六、雜項單列函數
| 函數 | 語法 | 說明 | 範例 |
|---|
| NVL | NVL(expr1, expr2) | expr1 為 NULL 回傳 expr2 | NVL(NULL, 'N/A')='N/A' |
| NVL2 | NVL2(expr1, expr2, expr3) | expr1 不為 NULL 回傳 expr2，否則 expr3 | NVL2('x','Y','N')='Y' |
| COALESCE | COALESCE(e1,e2...) | 回傳第一個非 NULL | COALESCE(NULL,5,NULL,9)=5 |
| NULLIF | NULLIF(e1,e2) | e1=e2 回傳 NULL，否則 e1 | NULLIF('a','a')=NULL |
| DECODE | DECODE(exp, s1,r1, s2,r2..., def) | 類比 IF-ELSE 鏈 | DECODE(x,'A',1,'B',2,0) |
| CASE | CASE WHEN...THEN...ELSE...END | 標準 SQL 條件表達式 | CASE WHEN x>0 THEN 'P' ELSE 'N' END |
| GREATEST | GREATEST(e1,e2...) | 最大值 | GREATEST(15,5,75,8)=75 |
| LEAST | LEAST(e1,e2...) | 最小值 | LEAST(15,5,75,8)=5 |
| SYS_CONTEXT | SYS_CONTEXT('USERENV', param[,len]) | 取得 Session 環境資訊 | SYS_CONTEXT('USERENV','SESSION_USER') |
| SYS_CONNECT_BY_PATH | SYS_CONNECT_BY_PATH(col, sep) | 階層查詢路徑串接 | 需搭配 CONNECT BY |
| UID | UID | 當前使用者唯一整數 ID | UID |
| USER | USER | 當前 Session 使用者名 | USER |
| USERENV | USERENV(param) | 舊版函數，建議改用 SYS_CONTEXT | USERENV('LANGUAGE') |
| DUMP | DUMP(expr[,fmt[,start[,len]]]) | 內部儲存表示 (8/10/16/17 進制) | DUMP('abc',17,2,4) |
| EMPTY_BLOB/CLOB | EMPTY_BLOB(), EMPTY_CLOB() | 初始化 LOB 定位器 | INSERT ... VALUES (EMPTY_BLOB()) |
| NLS_CHARSET_NAME/ID/DECL_LEN | ... | 字元集元資料查詢 | NLS_CHARSET_ID('US7ASCII') |
| SYS_EXTRACT_UTC | SYS_EXTRACT_UTC(ts) | 轉換為 UTC (格林威治時間) | SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) |
| SYS_TYPEID | SYS_TYPEID(obj) | 物件類型 ID | 自定義類型使用 |
七、常用格式模型速查
| 類別 | 常用格式元素 |
|---|
| 日期 | YYYY, MM, DD, HH24, MI, SS, MON, DY, DAY, J, RM, Q, WW, IW |
| 數值 | 9 (數字), 0 (前導零), . (小數點), G (群組分隔), D (小數字元), L (貨幣符號), MI (負號後綴), PR (括號負數), FM (填充模式關閉) |
結語
熟練掌握 Oracle 內建函數，能將複雜邏輯下推至資料庫層執行，減少網路傳輸、簡化應用程式碼。建議：優先使用標準 SQL 函數 (CASE, CAST, COALESCE) 以利跨平台遷移；Oracle 專有函數 (DECODE, NVL, SYS_CONTEXT) 用於效能敏感或平台綁定場景。隨手查閱 V$SQLFN_METADATA (12c+) 可獲取完整函數元資料。
