Oracle 循序漸進講解（第三講）：常用系統函數全攻略——日期、字符、數學與上下文函數

Oracle 循序漸進講解（第三講）：常用系統函數全攻略

一、日期類函數實戰速查

Oracle 日期運算是開發與調優高頻場景，掌握核心函數組合可大幅簡化邏輯：

1. 字符串轉日期：TO_DATE

SELECT TO_DATE('2014-10-11 12:13:14', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 結果：2014-10-11 12:13:14

注意：格式模型中小時建議用 HH24（24小時制），避免上午/下午歧義。

2. 獲取當月第一天：TRUNC(SYSDATE, 'MM')

SELECT TRUNC(SYSDATE, 'MM') FROM dual;  -- 2014-11-01

3. 獲取當月最後一天：TRUNC(LAST_DAY(SYSDATE))

SELECT TRUNC(LAST_DAY(SYSDATE)) FROM dual;  -- 2014-11-30

4. 獲取當年第一天：TRUNC(SYSDATE, 'YYYY')

SELECT TRUNC(SYSDATE, 'YYYY') FROM dual;  -- 2014-01-01

5. 獲取當年最後一天：ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-1

SELECT ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),12)-1 FROM dual;  -- 2014-12-31

6. 當月天數：CAST(TO_CHAR(LAST_DAY(SYSDATE),'DD') AS INT)

SELECT CAST(TO_CHAR(LAST_DAY(SYSDATE),'DD') AS INT) FROM dual;  -- 30

7. 當月剩餘天數：LAST_DAY(SYSDATE) - SYSDATE

SELECT LAST_DAY(SYSDATE) - SYSDATE FROM dual;  -- 8

8. 下一個指定週幾：NEXT_DAY(SYSDATE, 1)（1=週日）

SELECT NEXT_DAY(SYSDATE, 1) FROM dual;  -- 2014-11-23

9. 兩日期間月份差：MONTHS_BETWEEN

SELECT MONTHS_BETWEEN(
  TO_DATE('2014-11-01','YYYY-MM-DD'),
  TO_DATE('2014-01-01','YYYY-MM-DD')
) FROM dual;  -- 10

二、字符串處理函數精要

1. 日期轉字符：TO_CHAR(SYSDATE, 'YYYY-MM')

SELECT TO_CHAR(SYSDATE, 'YYYY-MM') FROM dual;  -- 2014-11

2. 大小寫、長度、拼接：UPPER/LOWER/LENGTH/||

SELECT UPPER('a') || LOWER('B') || LENGTH('aaa') FROM dual;  -- Ab3

3. 填充函數：LPAD/RPAD

SELECT LPAD('aaa',5,'*'), RPAD('aaa',5,'*') FROM dual;  -- **aaa | aaa**

4. 去除字符：LTRIM/RTRIM/TRIM

SELECT LTRIM('abbb','a'), RTRIM('1234','4'), TRIM(' ABC ') FROM dual;  -- bbb | 123 | ABC

5. 子串提取：SUBSTR(str, start[, len])

SELECT SUBSTR('abcde',1,2) FROM dual;  -- ab

關鍵點：起始位置從 1 開始；負數表示從末尾倒數；省略長度則截取至末尾。

6. 字符定位：INSTR(str, substr[, pos[, nth]])

SELECT INSTR('abcad','a',1), INSTR('abcad','a',2,1) FROM dual;  -- 1 | 4

POS 為正時從左向右，為負時從右向左；NTH 指定第 N 次出現位置。

三、數學運算函數

1. 四捨五入：ROUND(n[, m])

SELECT ROUND(12.12), ROUND(12.12,1), ROUND(12.12,-1) FROM dual;  -- 12 | 12.1 | 10

M 為正保留小數位，為負則向十位、百位捨入。

2. 截斷：TRUNC(n[, m])

SELECT TRUNC(12.12), TRUNC(12.12,1), TRUNC(12.12,-1) FROM dual;  -- 12 | 12.1 | 10

3. 取餘、取整：MOD/CEIL/FLOOR

SELECT MOD(1,3), CEIL(12.56), FLOOR(12.56) FROM dual;  -- 1 | 13 | 12

四、系統上下文函數：SYS_CONTEXT('USERENV', ...)

獲取會話、實例、主機等環境信息，常用於審計、動態 SQL 構建：

SELECT 
  SYS_CONTEXT('USERENV','DB_NAME') DBNAME,
  SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') DATE_FORMAT
FROM dual;
-- DBNAME: ORCL | DATE_FORMAT: DD-MON-RR

實戰提示：SYS_CONTEXT 為非確定性函數，在 WHERE 條件中使用可能影響執行計劃穩定性，建議在 PL/SQL 變量中預先賦值後再引用。