字元函數
| 函數 | 說明 | 範例 | 結果 |
|---|
| ASCII(c) | 回傳字元十進位編碼 | ASCII('A') | 65 |
| CHR(n) | 回傳編碼對應字元 | CHR(65) | A |
| CONCAT(s1,s2) | 串接兩字串（同 ||） | CONCAT('010-','88888888') | 010-88888888 |
| INITCAP(s) | 首字大寫，其餘小寫 | INITCAP('smith') | Smith |
| INSTR(s,sub,pos,occ) | 搜尋子字串位置 | INSTR('oracle training','ra',1,2) | 9 |
| LENGTH(s) | 字串長度（字元數） | LENGTH('高乾競') | 3 |
| LOWER/UPPER(s) | 轉小寫/大寫 | UPPER('aBc') | ABC |
| LPAD/RPAD(s,len,pad) | 左/右填充至指定長度 | LPAD(RPAD('gao',10,'*'),17,'*') | *******gao******* |
| LTRIM/RTRIM/TRIM(s,chars) | 移除首/尾/兩側指定字元 | TRIM(' ' FROM ' gao ') | gao |
| SUBSTR(s,pos,len) | 擷取子字串 | SUBSTR('13088888888',3,8) | 08888888 |
| REPLACE(s,old,new) | 替換字串 | REPLACE('he love you','he','I') | I love you |
| SOUNDEX(s) | 回傳發音相似代碼 | SOUNDEX('weather')=SOUNDEX('wether') | TRUE |
數值函數
| 函數 | 說明 | 範例 | 結果 |
|---|
| ABS(n) | 絕對值 | ABS(-100) | 100 |
| CEIL(n) | 大於等於 n 的最小整數 | CEIL(3.14) | 4 |
| FLOOR(n) | 小於等於 n 的最大整數 | FLOOR(3.99) | 3 |
| ROUND(n,[prec]) | 四捨五入 | ROUND(55.5) | 56 |
| TRUNC(n,[prec]) | 截斷（捨去） | TRUNC(-55.5) | -55 |
| MOD(m,n) | 餘數 | MOD(10,3) | 1 |
| POWER(m,n) | m 的 n 次方 | POWER(2,10) | 1024 |
| SIGN(n) | 正負號：1/0/-1 | SIGN(-100) | -1 |
| EXP(n) | e 的 n 次方 | EXP(1) | 2.71828 |
| LN(n) | 自然對數 | LN(2.71828) | 1 |
| LOG(b,n) | 以 b 為底對數 | LOG(10,100) | 2 |
| SQRT(n) | 平方根 | SQRT(16) | 4 |
日期函數
| 函數 | 說明 | 範例 | 結果 |
|---|
| SYSDATE | 資料庫當前日期時間 | TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') | 2024-01-15 14:30:00 |
| ADD_MONTHS(d,n) | 加減月份 | ADD_MONTHS(TO_DATE('199912','YYYYMM'),2) | 2000-02 |
| LAST_DAY(d) | 該月最後一天 | LAST_DAY(SYSDATE) | 2024-01-31 |
| MONTHS_BETWEEN(d1,d2) | 兩日期月份差 | MONTHS_BETWEEN('2000-05-20','1999-05-20') | 12 |
| NEXT_DAY(d,weekday) | 下一個指定週幾 | NEXT_DAY('2001-05-18','FRIDAY') | 2001-05-25 |
| NEW_TIME(d,zone1,zone2) | 時區轉換 | NEW_TIME(SYSDATE,'PDT','GMT') | 對應 GMT 時間 |
| ROUND(d,fmt) | 日期四捨五入 | ROUND(SYSDATE,'MONTH') | 月初/月中依規則 |
| TRUNC(d,fmt) | 日期截斷 | TRUNC(SYSDATE,'HH') | 當前整點 |
轉換函數
| 函數 | 說明 | 關鍵格式元素 |
|---|
| TO_CHAR(d/n,fmt) | 日期/數值轉字串 | YYYY,MM,DD,HH24,MI,SS,FM,999G990D00 |
| TO_DATE(s,fmt) | 字串轉日期 | YYYY-MM-DD,HH24:MI:SS |
| TO_NUMBER(s,fmt) | 字串轉數值 | 999G990D00 |
| CAST(expr AS type) | ANSI 標準轉型 | CAST('123' AS NUMBER) |
通用/條件/彙總/分析函數精選
- NVL(expr1,expr2)：expr1 為 NULL 回傳 expr2。
- NVL2(expr1,expr2,expr3)：expr1 不為 NULL 回傳 expr2，否則 expr3。
- COALESCE(e1,e2,...)：回傳第一個非 NULL 參數（ANSI 標準）。
- NULLIF(e1,e2)：兩值相等回傳 NULL，否則 e1。
- DECODE(expr,val1,res1,...,default)：Oracle 專用條件函數，類似 CASE WHEN。
- CASE WHEN cond THEN res ... ELSE def END：ANSI 標準，支援搜尋條件與運算式比較。
- ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：分組排序編號，去重利器。
- RANK()/DENSE_RANK()：排名函數，處理並列差異。
- LAG/LEAD(expr,offset,default) OVER (...)：存取前後行數值，同比環比必備。
- LISTAGG(expr,delim) WITHIN GROUP (ORDER BY ...)：行轉列字串彙總（11gR2+）。
- PIVOT/UNPIVOT：行列互轉（11g+）。
實用技巧：DBMS_OUTPUT 輸出非字串
SET SERVEROUTPUT ON
DECLARE
v_num NUMBER := 10 + 3*4 - 20 + POWER(5,2); -- 5**2 非 PL/SQL 語法
BEGIN
DBMS_OUTPUT.PUT_LINE('運算結果：' || TO_CHAR(v_num));
END;
|| 為串接運算子，TO_CHAR 強制轉字串輸出。
總結
掌握核心 80 函數，日常開發覆蓋率超 95%。重點記憶：日期格式元素、轉換函數陷阱（NLS 影響）、分析函數視窗語法、條件函數 ANSI 與 Oracle 方言差異。遇生僻函數查官方文檔即可。
