Oracle 函數庫龐大，本文按類別整理高頻函數，附帶語法、參數、示例與避坑提示，可作桌面速查手冊。
一、運算符
| 類別 | 運算符 | 示例 |
|---|
| 算術 | + - * / | SELECT sal+comm FROM emp |
| 連接 | || | SELECT ename||'_'||job FROM emp |
| 比較 | > >= = != < <= LIKE BETWEEN IS NULL IN | WHERE deptno IN (10,20) |
| 邏輯 | NOT AND OR | WHERE deptno=10 AND sal>2000 |
| 集合 | INTERSECT, UNION, UNION ALL, MINUS | 列數/類型必須一致；ORDER BY 用位置序號 |
二、日期時間函數 (核心)
| 函數 | 用途 | 示例 |
|---|
| SYSDATE | 當前日期時間 | SELECT SYSDATE FROM DUAL |
| TO_DATE | 字符→日期 | TO_DATE('2024-01-15','YYYY-MM-DD') |
| TO_CHAR | 日期→字符 (格式化) | TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') |
| ADD_MONTHS | 加減月份 | ADD_MONTHS(SYSDATE,3) |
| MONTHS_BETWEEN | 月份差 | MONTHS_BETWEEN('2024-02-01','2024-01-01') → 1 |
| LAST_DAY | 月最後一天 | LAST_DAY(SYSDATE) |
| NEXT_DAY | 下一個指定週幾 | NEXT_DAY(SYSDATE,'FRIDAY') 或 NEXT_DAY(SYSDATE,6) |
| TRUNC(date) | 截斷到日/月/年 | TRUNC(SYSDATE,'MONTH') → 本月 1 號 00:00 |
| ROUND(date) | 四捨五入到日/月/年 | ROUND(SYSDATE,'YEAR') → 今年 1/1 |
| EXTRACT | 提取年/月/日/時/分/秒 | EXTRACT(HOUR FROM SYSTIMESTAMP) |
| NEW_TIME | 時區轉換 | NEW_TIME(SYSDATE,'GMT','EST') |
| CURRENT_DATE/TIMESTAMP | 會話時區時間 | 受 SESSIONTIMEZONE 影響 |
實用日期計算模式
-- 本月第一天
TRUNC(SYSDATE,'MONTH')
-- 本月最後一天 23:59:59
LAST_DAY(TRUNC(SYSDATE,'MONTH')) + 1 - 1/86400
-- 上月第一天
TRUNC(ADD_MONTHS(SYSDATE,-1),'MONTH')
-- 兩日期年月日差
SELECT FLOOR(MONTHS_BETWEEN(d2,d1)/12) years,
MOD(FLOOR(MONTHS_BETWEEN(d2,d1)),12) months,
TRUNC(d2 - ADD_MONTHS(d1, FLOOR(MONTHS_BETWEEN(d2,d1)/12)*12 + MOD(FLOOR(MONTHS_BETWEEN(d2,d1)),12))) days
FROM DUAL;
-- 閏年判斷
TO_CHAR(LAST_DAY(TO_DATE('02'||:year,'MMYYYY')),'DD') = '29' → 閏年
三、字符函數
| 函數 | 用途 | 示例 |
|---|
| SUBSTR | 截取子串 | SUBSTR('abcdef',1,3) → 'abc' |
| INSTR | 查找子串位置 | INSTR('abcfdg','fd') → 4 |
| || / CONCAT | 連接 | 'HELLO'||'WORLD' |
| LTRIM/RTRIM/TRIM | 去空格/指定字符 | TRIM(' abc ') → 'abc' |
| ASCII/CHR | 字符↔ASCII | ASCII('A')=65, CHR(65)='A' |
| LENGTH/LENGTHB | 字符/字節長度 | LENGTH('中文')=2, LENGTHB=6 (UTF8) |
| LOWER/UPPER/INITCAP | 大小寫轉換 | INITCAP('hello world') → 'Hello World' |
| REPLACE | 替換所有匹配 | REPLACE('abc','b','xy') → 'axyc' |
| TRANSLATE | 字符級替換 (1:1) | TRANSLATE('abc','b','xy') → 'axc' (只取 x) |
| LPAD/RPAD | 左/右填充 | LPAD('func',10,'=') → '======func' |
| DECODE | 等值分支 (Switch) | DECODE(deptno,10,'A',20,'B','OTHER') |
| CASE | 條件分支 (If-Else) | 支援範圍/複合條件，見專文對比 |
四、數字函數
| 函數 | 用途 | 示例 |
|---|
| CEIL/FLOOR | 向上/向下取整 | CEIL(66.6)=67, FLOOR(66.6)=66 |
| ROUND/TRUNC | 四捨五入/截斷 | ROUND(66.667,2)=66.67, TRUNC(66.667,2)=66.66 |
| POWER/SQRT | 冪/平方根 | POWER(3,2)=9, SQRT(9)=3 |
| MOD | 取餘 | MOD(9,5)=4 |
| SIGN | 符號 | SIGN(-5)=-1, SIGN(0)=0, SIGN(5)=1 |
| ABS | 絕對值 | ABS(-5)=5 |
五、轉換函數
| 函數 | 轉換方向 | 示例 |
|---|
| TO_CHAR | 日期/數字 → 字符 | TO_CHAR(12345,'$99,999') → '$12,345' |
| TO_DATE | 字符 → 日期 | TO_DATE('2024-01-15','YYYY-MM-DD') |
| TO_NUMBER | 字符 → 數字 | TO_NUMBER('$12,345','$99,999') → 12345 |
| CAST | 通用類型轉換 | CAST('123' AS NUMBER) |
六、其他單行函數
| 函數 | 用途 | 示例 |
|---|
| USER | 當前登錄用戶 | SELECT USER FROM DUAL |
| VSIZE | 表達式字節數 | VSIZE('HELLO')=5 |
| NVL(ex1,ex2) | ex1 為 NULL 返回 ex2 | NVL(comm,0) |
| NULLIF(ex1,ex2) | 相等返回 NULL | NULLIF(sal,comm) |
| COALESCE | 返回首個非 NULL | COALESCE(comm,sal,sal*10) |
| NVL2(ex1,ex2,ex3) | ex1 非 NULL 返回 ex2 否則 ex3 | NVL2(comm,ename,'NO COMM') |
七、分組聚合函數
| 函數 | 說明 |
|---|
| MAX/MIN/AVG/SUM/COUNT | 基礎聚合 |
| COUNT(DISTINCT col) | 去重計數 |
| STDDEV/VARIANCE | 標準差/方差 |
| GROUP BY ROLLUP/CUBE | 多維小計/總計 |
分組查詢模式
-- 單組
SELECT MAX(sal), MIN(sal), AVG(sal), COUNT(*), COUNT(job), COUNT(DISTINCT job), SUM(sal)
FROM emp WHERE deptno=30;
-- 多組 + HAVING
SELECT deptno, MAX(sal), MIN(sal), AVG(sal), COUNT(*), SUM(sal)
FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;
-- ROLLUP (按第一列層級小計)
SELECT deptno, job, SUM(sal) FROM emp GROUP BY ROLLUP(deptno,job);
-- CUBE (所有維度組合小計)
SELECT deptno, job, SUM(sal) FROM emp GROUP BY CUBE(deptno,job);
八、全局臨時表 (GTT)
-- 事務級 (COMMIT 即清空)
CREATE GLOBAL TEMPORARY TABLE temp_dept (
dno NUMBER, dname VARCHAR2(10)
) ON COMMIT DELETE ROWS;
-- 會話級 (斷開連接清空)
CREATE GLOBAL TEMPORARY TABLE temp_dept (
dno NUMBER, dname VARCHAR2(10)
) ON COMMIT PRESERVE ROWS;
-- 特性：定義元數據持久，數據隔離 (各會話只見自己數據)，不產生 REDO (極快)
九、除法與取整技巧
-- 取餘
MOD(65,50) → 15
-- 向下取整 (截斷)
TRUNC(65/33) → 1
-- 向上取整 (CEIL)
CEIL(65/60) → 2
-- 模擬整除
FLOOR(65/33) → 1
