当前位置：首页 > news >正文

[Oracle数据库] Oracle 常用函数

news 2025/8/16 9:11:19

一、先搞懂这些基础约定

二、数值函数：处理数字的 “小帮手”

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'。