Oracle 內建豐富函數與資料型態,善用可簡化應用端邏輯。本文整理主要資料型態特性、單列/多列函數分類、日期運算、隱含/顯式轉換、通用函數 (NVL/NVL2/NULLIF/COALESCE/CASE/DECODE) 與群組函數處理 NULL 行為。
主要資料型態
| 型態 | 特性 | 備註 |
| VARCHAR2(n) | 變長字串,最大 4000 位元組 (12c+ MAX_STRING_SIZE=EXTENDED 可 32KB) | 不補空格,建議優先使用 |
| CHAR(n) | 定長字串,不足右補空格,比較時需注意 | 固定長度場景 (如代碼) |
| NUMBER(p,s) | 精度 p ≤ 38,刻度 s ∈ [-84,127] | 整數 s=0 或省略;浮點數不指定 p,s |
| DATE | 含世紀/年/月/日/時/分/秒,範圍 BC 4712 - AD 9999 | 預設格式 DD-MON-RR |
| TIMESTAMP[(fsp)] | 含小數秒 (最高 9 位),可帶時區 (TZ/LTZ) | 9i+ 新增,精度更高 |
| BLOB/CLOB/NCLOB | 大物件,至 4GB-1,LOB Locator 機制 | 應用層透過 JDBC/OCI 讀寫 |
| LONG | 舊型大字串,單表僅 1 欄,**不支援 WHERE 條件查詢** | 已棄用,改用 CLOB |
| NCHAR/NVARCHAR2 | Unicode 型態 (AL16UTF16/UTF8),按字元計長 | 多語言環境使用 |
單列函數 (Single-Row Functions)
字元函數
UPPER/LOWER/INITCAP, LENGTH, SUBSTR, INSTR, LPAD/RPAD, TRIM/LTRIM/RTRIM, REPLACE, TRANSLATE, CONCAT (||)
數值函數
ROUND(m,n), TRUNC(m,n), MOD(m,n), ABS, SIGN, FLOOR/CEIL, POWER, SQRT, EXP, LN, LOG
日期函數
SYSDATE, SYSTIMESTAMP, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ROUND/TRUNC(date,'MM'), EXTRACT(YEAR FROM dt)
轉換函數 (顯式轉換建議)
TO_CHAR(date|num, fmt [, nls])
TO_DATE(char, fmt [, nls])
TO_NUMBER(char, fmt [, nls])
CAST(expr AS type) -- ANSI 標準
通用函數 (NULL 處理與條件邏輯)
| 函數 | 語法 | 行為 |
| NVL | NVL(expr1, expr2) | expr1 為 NULL 回傳 expr2,否則 expr1 |
| NVL2 | NVL2(expr1, expr2, expr3) | expr1 不為 NULL 回傳 expr2,否則 expr3 |
| NULLIF | NULLIF(expr1, expr2) | 兩值相等回傳 NULL,否則 expr1 |
| COALESCE | COALESCE(expr1, expr2, ...) | 回傳第一個非 NULL 運算式 (ANSI 標準) |
| CASE | CASE expr WHEN v1 THEN r1 ... ELSE re END | 簡單/搜尋式 CASE,支援多路分支 |
| DECODE | DECODE(col, s1, r1, s2, r2, ..., def) | Oracle 專有,等值比較多路分支 |
範例
-- NVL 處理提成 NULL
SELECT ename, sal, comm, sal+NVL(comm,0) total FROM emp;
-- NVL2 區分有無提成
SELECT ename, NVL2(comm, '有提成', '無提成') FROM emp;
-- CASE 部門名稱對照
SELECT ename, CASE deptno WHEN 10 THEN '財務' WHEN 20 THEN '研發' ELSE '其他' END dept_name FROM emp;
-- DECODE 同樣功能
SELECT ename, DECODE(deptno, 10, '財務', 20, '研發', '其他') dept_name FROM emp;
多列/群組函數 (Aggregate Functions)
AVG, COUNT, MAX, MIN, SUM, STDDEV, VARIANCE, MEDIAN (10g+), STATS_MODE (10g+)
- **預設忽略 NULL 值** (COUNT(*) 例外,計算列數含 NULL)。
- 可搭配
DISTINCT 去重:COUNT(DISTINCT deptno)。
- 嵌套限制:群組函數最多嵌套兩層 (如
MAX(AVG(sal)))。
群組函數強制處理 NULL
SELECT AVG(NVL(comm,0)) FROM emp; -- 將 NULL 視為 0 參與平均
日期運算規則
- Date ± Number = Date (單位:天)。
- Date - Date = Number (天數差,含小數代表時分秒)。
- Date + Date **無意義** (報錯)。
字符集與 NLS 參數
SELECT * FROM v$nls_parameters;
-- NLS_CHARACTERSET: 資料庫字符集 (如 ZHS16GBK, AL32UTF8)
-- NLS_NCHAR_CHARACTERSET: 國字字符集 (如 AL16UTF16)
-- NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT 等顯示格式參數
- 匯入匯出 (exp/imp/expdp/impdp) 字符集不同時會自動轉換,可能導致資料損壞,建議來源/目標字符集一致或為超集關係。