萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 常用函數與資料型態完整參考:單列/多列函數、日期運算、轉換、通用函數 (NVL/CASE/DECODE)

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/NVARCHAR2Unicode 型態 (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 處理與條件邏輯)

函數語法行為
NVLNVL(expr1, expr2)expr1 為 NULL 回傳 expr2,否則 expr1
NVL2NVL2(expr1, expr2, expr3)expr1 不為 NULL 回傳 expr2,否則 expr3
NULLIFNULLIF(expr1, expr2)兩值相等回傳 NULL,否則 expr1
COALESCECOALESCE(expr1, expr2, ...)回傳第一個非 NULL 運算式 (ANSI 標準)
CASECASE expr WHEN v1 THEN r1 ... ELSE re END簡單/搜尋式 CASE,支援多路分支
DECODEDECODE(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) 字符集不同時會自動轉換,可能導致資料損壞,建議來源/目標字符集一致或為超集關係。
copyright © 萬盛學電腦網 all rights reserved