[Oracle數據庫] Oracle 常用函數

一、先搞懂這些基礎約定

二、數值函數：處理數字的 “小幫手”

1??MOD (n1, n2)：取余數

2??ROUND (n1 [, n2])：四舍五入

3??TRUNC (n1 [, n2])：截斷（不四舍五入）

其他常用數值函數

三、字符型函數：字符串處理的 “利器”

1??大小寫轉換：LOWER (c) 與 UPPER (c)

2??字符串填充：LPAD (c1, n [, c2]) 與 RPAD (c1, n [, c2])

3??去除首尾字符：TRIM、LTRIM、RTRIM

4??字符串替換：REPLACE (c1, c2 [, c3])

5??字符串截取：SUBSTR (c1, n1 [, n2])

其他常用字符函數

四、日期函數：玩轉時間的 “魔法”

1??獲取當前時間：SYSDATE 與 CURRENT_DATE

2??日期加減：ADD_MONTHS (d, n)

3??月份相關：LAST_DAY 與 MONTHS_BETWEEN

4??查找下一個星期幾：NEXT_DAY (d, n)

5??日期舍入與截斷：ROUND (d [, fmt]) 與 TRUNC (d [, fmt])

五、轉換函數：數據類型的 “轉換器”

1??TO_CHAR：將其他類型轉為字符串

2??TO_DATE：將字符串轉為日期

3??TO_NUMBER：將字符串轉為數值

六、其他輔助函數：實用工具集合

1??DECODE：增強型 “if-else”

2??NVL 與 NVL2：處理 NULL 值

3??GREATEST 與 LEAST：取最大 / 最小值

在 Oracle 數據庫操作中，函數是簡化數據處理、提升查詢效率的重要工具。無論是數值計算、字符串處理，還是日期轉換，掌握常用函數能讓你在 SQL 編寫中事半功倍。

一、先搞懂這些基礎約定

在學習函數前，先明確 PPT 中的通用約定，幫你快速理解函數參數：

N：表示數字型參數（如整數、小數）
C：表示字符型參數（如字符串）
D：表示日期型參數（如系統時間、自定義日期）
[]：括號內的參數為可選參數（可省略）
fmt：表示格式符（用于指定日期、數值的轉換格式）
||：表示 “任選其一”（如LEADING||TRAILING表示選LEADING或TRAILING）

二、數值函數：處理數字的 “小幫手”

數值函數用于對數字型數據進行計算或處理，返回結果仍為數字。以下是最常用的幾個：

1??MOD (n1, n2)：取余數

功能：返回n1除以n2的余數；若n2=0，則直接返回n1。

示例：

sql

SELECT MOD(24, 5) FROM DUAL; -- 24÷5余4，結果為4
SELECT MOD(10, 0) FROM DUAL; -- n2=0，返回n1，結果為10

2??ROUND (n1 [, n2])：四舍五入

功能：將n1四舍五入到小數點后n2位；n2默認值為 0（即四舍五入為整數）；若n2為負數，則舍入到小數點左側對應位數。

示例：

sql

SELECT ROUND(23.56),      -- n2默認0，四舍五入為整數，結果24ROUND(23.56, 1),   -- 保留1位小數，結果23.6ROUND(23.56, -1)   -- 舍入到十位（左側1位），結果20
FROM DUAL;

3??TRUNC (n1 [, n2])：截斷（不四舍五入）

功能：將n1截斷到小數點后n2位（直接去掉多余位數，不四舍五入）；n2默認 0（截斷為整數）；n2為負數時，截斷到小數點左側對應位數。
與 ROUND 的區別：TRUNC 是 “硬截斷”，ROUND 是 “四舍五入”。

示例：

sql

SELECT TRUNC(23.56),      -- 截斷為整數，結果23TRUNC(23.56, 1),   -- 保留1位小數，結果23.5TRUNC(23.56, -1)   -- 截斷到十位，結果20
FROM DUAL;

其他常用數值函數

函數	功能	示例
`ABS(n)`	返回 n 的絕對值	`ABS(-100) → 100`
`CEIL(n)`	返回大于等于 n 的最小整數	`CEIL(18.2) → 19`；`CEIL(-18.2) → -18`
`FLOOR(n)`	返回小于等于 n 的最大整數	`FLOOR(2.2) → 2`；`FLOOR(-2.2) → -3`
`SQRT(n)`	返回 n 的平方根（n≥0）	`SQRT(9) → 3`

三、字符型函數：字符串處理的 “利器”

字符型函數用于對字符串（CHAR、VARCHAR2等類型）進行轉換、截取、替換等操作，返回結果多為字符串。

1??大小寫轉換：LOWER (c) 與 UPPER (c)

LOWER(c)：將字符串c中所有字符轉為小寫。
UPPER(c)：將字符串c中所有字符轉為大寫。

示例：

sql

SELECT LOWER('WhaT is tHis') FROM DUAL; -- 結果：'what is this'
SELECT UPPER('WhaT is tHis') FROM DUAL; -- 結果：'WHAT IS THIS'

2??字符串填充：LPAD (c1, n [, c2]) 與 RPAD (c1, n [, c2])

功能：將字符串c1處理為長度為n的新字符串，不足時用指定字符補充（默認用空格），超出時截斷。
區別：LPAD從左側補充，RPAD從右側補充。
規則：
- 若n < c1的長度：從左（LPAD）或右（RPAD）截斷到n位。
- 若n > c1的長度：c2不為空時用c2補充，為空時用空格補充。

示例：

sql

-- LPAD示例：原字符串為'WhaT is tHis'（長度11）
SELECT LPAD('WhaT is tHis', 5),       -- n=5 < 11，左側截斷5位 → 'WhaT 'LPAD('WhaT is tHis', 25),      -- n=25 > 11，用空格左側補充至25位LPAD('WhaT is tHis', 25, '-')  -- 用'-'左側補充至25位 → '--------------WhaT is tHis'
FROM DUAL;

3??去除首尾字符：TRIM、LTRIM、RTRIM

這三個函數都用于移除字符串中的指定字符，但適用場景不同：

TRIM([[LEADING|TRAILING|BOTH] c2 FROM] c1)：
- 功能：移除c1中首尾的c2（c2必須是單個字符）。
- 可選參數：
  - LEADING：只移除左側的c2；
  - TRAILING：只移除右側的c2；
  - BOTH（默認）：移除兩側的c2；
  - 若不指定c2：默認移除首尾空格。
- 示例：
  sql
```
SELECT TRIM(' WhaT is tHis '), -- 移除首尾空格 → 'WhaT is tHis'TRIM('W' FROM 'WhaT is tHis w W'), -- 移除兩側'W' → 'haT is tHis w 'TRIM(LEADING 'W' FROM 'WhaT is tHis w W') -- 只移除左側'W' → 'haT is tHis w W'
FROM DUAL;
```
LTRIM(c1[, c2])：移除c1左側所有與c2匹配的字符（c2可以是多個字符），默認移除左側空格。
- 示例：LTRIM('WWhhhhhaT is tHis', 'Wh')?→ 移除左側所有 'W' 和 'h'，結果為'aT is tHis'。
RTRIM(c1[, c2])：與 LTRIM 類似，但移除右側匹配字符。
- 示例：RTRIM('WhaT is tHis w W', 'W w')?→ 移除右側所有 'W'、' '、'w'，結果為'WhaT is tHis'。

4??字符串替換：REPLACE (c1, c2 [, c3])

功能：將c1中所有c2替換為c3；若c3為空，則刪除所有c2。

示例：

sql

SELECT REPLACE('WWhhhhhaT', 'W', '-'), -- 將'W'替換為'-' → '--hhhhhaT'REPLACE('WWhhhhhaT', 'h')       -- 不指定c3，刪除所有'h' → 'WWhaT'
FROM DUAL;

5??字符串截取：SUBSTR (c1, n1 [, n2])

功能：從c1的n1位置開始，截取長度為n2的子串（n2默認截取到末尾）。
關鍵規則：
- n1=0：等價于n1=1（從第一個字符開始）；
- n1>0：從左向右數第n1位開始；
- n1<0：從右向左數第|n1|位開始；
- 若n1超過c1長度：返回空。

示例：

sql

SELECT SUBSTR('What is this', 5, 3), -- 從左數第5位開始，取3位 → 'is 'SUBSTR('What is this', -5, 3), -- 從右數第5位開始，取3位 → 'thi'SUBSTR('What is this', 50, 3)  -- n1超出長度，返回空
FROM DUAL;

其他常用字符函數

函數	功能	示例
`LENGTH(c)`	返回字符串 c 的長度（包括空格）	`LENGTH('A123中') → 5`（1 個字母 + 2 個數字 + 1 個漢字）
`INSTR(c1, c2[, n1[, n2]])`	返回`c2`在`c1`中第`n2`次出現的位置（`n1`為起始查找位置，正數從左，負數從右）	`INSTR('abcdefg', 'e', -3) → 5`（從右數第 3 位開始找 'e'，位置為 5）

四、日期函數：玩轉時間的 “魔法”

日期函數用于處理日期型數據（如獲取當前時間、計算日期差等），返回結果多為日期或數值。

1??獲取當前時間：SYSDATE 與 CURRENT_DATE

功能：均返回當前會話所在時區的系統時間。
細微區別：特殊情況下，CURRENT_DATE可能比SYSDATE快 1 秒（因時區處理機制不同）。

示例：

sql

SELECT SYSDATE, CURRENT_DATE FROM DUAL; -- 通常返回相同時間，格式為默認日期格式

2??日期加減：ADD_MONTHS (d, n)

功能：返回日期d加上n個月后的日期（n可為負數，即減月份）。

示例：

sql

SELECT ADD_MONTHS(SYSDATE, 12),  -- 當前時間加12個月（1年后）ADD_MONTHS(SYSDATE, -3)   -- 當前時間減3個月（3個月前）
FROM DUAL;

3??月份相關：LAST_DAY 與 MONTHS_BETWEEN

LAST_DAY(d)：返回d所在月份的最后一天。
- 示例：LAST_DAY(SYSDATE)?→ 若當前是 2024-08-15，返回 2024-08-31。
MONTHS_BETWEEN(d1, d2)：返回d1與d2之間的月份差（d1>d2為正，反之為負）。
- 示例：MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, -1)) → 1（相差 1 個月）。

4??查找下一個星期幾：NEXT_DAY (d, n)

功能：返回d之后第一個星期n的日期（n可為數字 1-7 或星期的中文名稱）。
注意：n的格式必須與當前會話的默認星期格式一致（如中文環境下用 “星期四”，不能用 “Thursday”）。

示例：

sql

SELECT NEXT_DAY(SYSDATE, 5),    -- 5表示星期五（不同環境可能有差異，建議用中文）NEXT_DAY(SYSDATE, '星期四') -- 直接用中文“星期四”更穩妥
FROM DUAL;

5??日期舍入與截斷：ROUND (d [, fmt]) 與 TRUNC (d [, fmt])

功能與數值型的ROUND、TRUNC類似，但操作對象是日期，fmt指定舍入 / 截斷的單位（如'HH24'表示小時）。

示例：

sql

SELECT ROUND(SYSDATE, 'HH24'),  -- 舍入到最近的小時（如15:30→16:00，15:29→15:00）TRUNC(SYSDATE, 'HH24')   -- 截斷到當前小時（如15:30→15:00）
FROM DUAL;

五、轉換函數：數據類型的 “轉換器”

轉換函數用于在不同數據類型（如字符、日期、數值）之間轉換，是 Oracle 中最常用的函數之一。

1??TO_CHAR：將其他類型轉為字符串

功能：可將日期、數值或字符型數據轉為VARCHAR2類型，支持自定義格式。
三種用法：
- 轉換日期為字符串：TO_CHAR(d[, fmt])，fmt指定日期格式（如'yyyy-mm-dd hh24:mi:ss'）。
  - 示例：TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') → '2024-08-15 14:30:25'。
- 轉換數值為字符串：TO_CHAR(n[, fmt])，fmt可指定貨幣符號、千位分隔符等。
  - 示例：TO_CHAR(-100, 'L99G999D99MI') → '￥ 100.00-'（L 為本地貨幣符號，G 為千位分隔符，D 為小數點，MI 表示負數在右側加 '-'）。
- 轉換字符為字符：TO_CHAR(c)，將其他字符類型（如CLOB）轉為CHAR。

2??TO_DATE：將字符串轉為日期

功能：將字符串c按fmt格式轉為日期類型（DATE）。
注意：fmt必須與c的格式一致，否則會報錯。
特殊格式：若fmt='J'，則c必須是 Julian 日（公元制天數，1 表示公元前 4712 年 1 月 1 日）。

示例：

sql

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss'), -- 按指定格式轉換TO_DATE(2454336, 'J') -- Julian日轉換（結果為2008-01-01左右）
FROM DUAL;

3??TO_NUMBER：將字符串轉為數值

功能：將字符串c按fmt格式轉為數值類型（NUMBER）。

示例：

sql

SELECT TO_NUMBER('-100.00', '9G999D99'), -- 按格式轉換 → -100TO_NUMBER('00000100.00')          -- 自動忽略前導0 → 100
FROM DUAL;

六、其他輔助函數：實用工具集合

這些函數雖不常用，但在特定場景下能大幅簡化代碼，值得掌握。

1??DECODE：增強型 “if-else”

功能：類似多條件判斷，語法為DECODE(exp, s1, r1, s2, r2, ..., def)。
邏輯：若exp = s1則返回r1，若exp = s2則返回r2…… 否則返回默認值def（無def則返回空）。

示例：

sql

SELECT DECODE('a2', 'a1', 'true1', 'a2', 'true2', 'default') FROM DUAL; -- 結果：'true2'

2??NVL 與 NVL2：處理 NULL 值

NVL(c1, c2)：若c1為NULL，則返回c2；否則返回c1。
- 示例：NVL(NULL, '12') → '12'；NVL('a', 'b') → 'a'。
NVL2(c1, c2, c3)：若c1不為NULL，返回c2；否則返回c3。
- 示例：NVL2('a', 'b', 'c') → 'b'；NVL2(NULL, 'b', 'c') → 'c'。