萬盛學電腦網

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

Oracle 常用單行函數速查手冊:字串、日期、轉換、NULL 處理與 NVL/COALESCE 實戰

Oracle 內建數百個單行函數,掌握高頻核心集即能應對 90% 開發場景。本文分類整理字串、日期、轉換、NULL 處理等實戰常用函數,附範例與避坑指南。

日期函數

函數用途範例結果
SYSDATE資料庫伺服器當前日期時間SELECT SYSDATE FROM dual2024-03-15 14:30:00
TO_CHAR(dt, fmt)日期→字串格式化TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')2024-03-15 14:30:00
TO_DATE(str, fmt)字串→日期TO_DATE('2024-03-15', 'YYYY-MM-DD')DATE
ADD_MONTHS(dt, n)加減月份(自動處理月末)ADD_MONTHS(SYSDATE, 3)2024-06-15
LAST_DAY(dt)該月最後一天LAST_DAY(SYSDATE)2024-03-31
NEXT_DAY(dt, 'FRIDAY')下一個指定週幾NEXT_DAY(SYSDATE, 'FRIDAY')2024-03-22
MONTHS_BETWEEN(d1, d2)月份差(含小數)MONTHS_BETWEEN('2024-06-15', '2024-03-15')3
TRUNC(dt, 'MM')截斷至月/日/年TRUNC(SYSDATE, 'MM')2024-03-01 00:00:00
ROUND(dt, 'MM')四捨五入至月/日/年ROUND(SYSDATE, 'MM')2024-03-01 或 2024-04-01

字串函數

函數用途範例
SUBSTR(str, pos, len)擷取子字串(位置從 1 起,負數從尾算)SUBSTR('abcdef', 2, 3) → 'bcd'
INSTR(str, sub, pos, nth)搜尋子字串位置INSTR('a.b.c', '.', 1, 2) → 4
REPLACE(str, old, new)取代所有出現REPLACE('a_b_c', '_', '-') → 'a-b-c'
TRIM([BOTH|LEADING|TRAILING] c FROM str)去除首尾字元TRIM(' abc ') → 'abc'
LPAD/RPAD(str, n, pad)左/右填充至長度 nLPAD('7', 3, '0') → '007'
UPPER/LOWER/INITCAP大小寫轉換INITCAP('hello world') → 'Hello World'
CONCAT(s1, s2)串接(等同 ||,但自動處理 NULL)CONCAT('a', NULL) → 'a'
DECODE(expr, s1, r1, s2, r2, ..., def)條件比對回傳(Oracle 專有)DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown')
CASE WHEN ... THEN ... ELSE ... ENDANSI 標準條件表達式支援範圍比較、多條件

數值函數

  • ROUND(n, d)、TRUNC(n, d)、MOD(m, n)、POWER(m, n)、SQRT(n)、SIGN(n)、ABS(n)、CEIL/FLOOR。

NULL 處理:NVL vs NVL2 vs COALESCE

實戰避坑

  1. 日期格式不匹配:TO_DATE 格式模板必須與輸入字串完全對應,含分隔符。
  2. 隱式轉換殺索引:WHERE id = '123'(id 為 NUMBER)導致全表掃描,改用 TO_NUMBER 或繫結變數。
  3. NVL(col, 0) 在索引欄位上使用會導致索引失效,改寫為 col IS NULL OR col = 0 或使用 COALESCE 配合函數索引。
  4. SUBSTR 位置參數為 0 視同 1,負數從尾部計算,SUBSTR('abc', -1) → 'c'。

熟練這組核心函數組合,能以最簡潔 SQL 完成複雜資料轉換、清洗與報表邏輯。

copyright © 萬盛學電腦網 all rights reserved
函數語法行為適用場景
NVLNVL(expr1, expr2)expr1 非 NULL 回傳 expr1,否則 expr2單一預設值
NVL2NVL2(expr1, expr2, expr3)expr1 非 NULL 回傳 expr2,否則 expr3非 NULL/NULL 給不同值
COALESCECOALESCE(e1, e2, ..., en)回傳第一個非 NULL 運算式(短路求值)多備選值、效能較好
NULLIFNULLIF(e1, e2)e1=e2 回傳 NULL,否則 e1避免除零:sal/NULLIF(comm,0)