Oracle SQL 函数大全

适用版本：Oracle Database 12c / 19c / 23ai
文档版本：v1.0 / 2026-07

---

1. 概述

Oracle 提供丰富的内置函数[1]：

类别	数量	说明
字符函数	30+	字符串处理
数值函数	20+	数学计算
日期函数	20+	日期处理
转换函数	10+	类型转换
NULL 函数	5+	NULL 处理
聚合函数	10+	聚合
分析函数	20+	分析
XML 函数	10+	XML

---

2. 字符函数

2.1 大小写

UPPER('hello')        -- HELLO
LOWER('HELLO')        -- hello
INITCAP('hello world') -- Hello World
NLS_UPPER('hello', 'NLS_SORT=XDUTCH')  -- 特定语言

2.2 字符串操作

LENGTH('Hello')        -- 5
LENGTHB('Hello')       -- 5（字节）
SUBSTR('Hello', 2, 3)  -- ell
SUBSTRB('Hello', 2, 3) -- ell（字节）
INSTR('Hello', 'l')    -- 3
INSTR('Hello', 'l', 1, 2) -- 4
CONCAT('Hello', ' World') -- Hello World
'Hello' || ' World'    -- Hello World
CHR(65)                -- A
ASCII('A')             -- 65

2.3 补齐

LPAD('5', 3, '0')      -- 005
RPAD('5', 3, ' ')      -- 5  
LTRIM('  Hello  ')     -- Hello  
RTRIM('  Hello  ')     --   Hello
TRIM('  Hello  ')      -- Hello
TRIM(LEADING ' ' FROM '  Hello')  -- Hello
TRIM(BOTH 'x' FROM 'xxxHelloxxx') -- Hello

2.4 替换

REPLACE('Hello World', 'o', '0')  -- Hell0 W0rld
TRANSLATE('Hello', 'el', 'ip')    -- Hippo
REVERSE('Hello')                   -- olleH

2.5 查找

SUBSTR('Hello World', 1, INSTR('Hello World', ' ') - 1)  -- Hello
REGEXP_SUBSTR('Order 12345', '[0-9]+')  -- 12345

---

3. 数值函数

3.1 基本数学

ABS(-5)        -- 5
MOD(10, 3)     -- 1
SIGN(-5)       -- -1
SIGN(5)        -- 1
POWER(2, 3)    -- 8
SQRT(16)       -- 4
EXP(1)         -- 2.71828183
LN(10)         -- 2.30258509
LOG(10, 100)   -- 2

3.2 取整

CEIL(5.3)      -- 6
FLOOR(5.7)     -- 5
ROUND(5.567, 2) -- 5.57
TRUNC(5.567, 2) -- 5.56
TRUNC(5.567)    -- 5
TRUNC(SYSDATE)  -- 日期去掉时间

3.3 三角函数

SIN(0)         -- 0
COS(0)         -- 1
TAN(0)         -- 0
ASIN(0)        -- 0
ACOS(1)        -- 0
ATAN(0)        -- 0

---

4. 日期函数

4.1 当前日期

SYSDATE         -- 当前日期时间
SYSTIMESTAMP    -- 带时区时间戳
CURRENT_DATE    -- 会话时区日期
CURRENT_TIMESTAMP -- 会话时区时间戳
LOCALTIMESTAMP  -- 会话时区时间戳

4.2 日期运算

SYSDATE + 1          -- 明天
SYSDATE - 1          -- 昨天
SYSDATE + 1/24       -- 1 小时后
SYSDATE + 1/24/60    -- 1 分钟后
SYSDATE + 1/24/60/60 -- 1 秒后

4.3 日期函数

ADD_MONTHS(SYSDATE, 3)  -- 3 个月后
MONTHS_BETWEEN(SYSDATE, hire_date)  -- 月差
LAST_DAY(SYSDATE)       -- 月末
NEXT_DAY(SYSDATE, 'MONDAY')  -- 下周一
TRUNC(SYSDATE, 'MM')    -- 月初
TRUNC(SYSDATE, 'YYYY')  -- 年初
ROUND(SYSDATE, 'MM')    -- 月四舍五入
EXTRACT(YEAR FROM SYSDATE)  -- 提取年

4.4 间隔

-- INTERVAL
INTERVAL '1' YEAR       -- 1 年
INTERVAL '3' MONTH      -- 3 月
INTERVAL '1' DAY        -- 1 天
INTERVAL '1' HOUR       -- 1 小时
INTERVAL '1' MINUTE     -- 1 分钟
INTERVAL '1' SECOND     -- 1 秒
INTERVAL '1-3' YEAR TO MONTH  -- 1 年 3 月
INTERVAL '1 12:00:00' DAY TO SECOND  -- 1 天 12 小时

-- 使用
SYSDATE + INTERVAL '1' DAY

---

5. 转换函数

5.1 TO_CHAR

-- 日期转字符串
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"')
TO_CHAR(SYSDATE, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN')

-- 数字转字符串
TO_CHAR(1234.56, '999,999.99')  -- 1,234.56
TO_CHAR(1234.56, '$999,999.99') -- $1,234.56
TO_CHAR(1234.56, '0000.00')     -- 1234.56
TO_CHAR(0.85, 'FM990.00%')      -- 85.00%

5.2 TO_DATE

TO_DATE('2026-07-21', 'YYYY-MM-DD')
TO_DATE('2026/07/21 14:30:00', 'YYYY/MM/DD HH24:MI:SS')
TO_DATE('21-JUL-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
TO_TIMESTAMP('2026-07-21 14:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')

5.3 TO_NUMBER

TO_NUMBER('1234.56')
TO_NUMBER('$1,234.56', '$9,999.99')
TO_NUMBER('FF', 'XX')  -- 255（16 进制）

5.4 CAST

CAST('123' AS NUMBER)
CAST(SYSDATE AS TIMESTAMP)
CAST(1234.56 AS NUMBER(10,2))

5.5 格式说明

日期格式	说明
YYYY	4 位年
YY	2 位年
MM	月
DD	日
HH24	24 小时
HH	12 小时
MI	分钟
SS	秒
FF	毫秒
DAY	星期
MON	月缩写
MONTH	月全名

---

6. NULL 函数

6.1 NVL

NVL(NULL, 'default')  -- default
NVL('value', 'default')  -- value

6.2 NVL2

NVL2(NULL, 'not_null', 'is_null')  -- is_null
NVL2('value', 'not_null', 'is_null')  -- not_null

6.3 COALESCE

COALESCE(NULL, NULL, 'first', 'second')  -- first
-- 返回第一个非 NULL

6.4 NULLIF

NULLIF('a', 'a')  -- NULL（相等返回 NULL）
NULLIF('a', 'b')  -- a（不等返回第一个）

6.5 DECODE

DECODE(NULL, NULL, 'is_null', 'not_null')  -- is_null
DECODE('a', 'a', 1, 'b', 2, 0)  -- 1

---

7. 条件函数

7.1 CASE

-- 简单 CASE
CASE grade
  WHEN 'A' THEN 4.0
  WHEN 'B' THEN 3.0
  WHEN 'C' THEN 2.0
  ELSE 0
END

-- 搜索 CASE
CASE 
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  WHEN score >= 70 THEN 'C'
  ELSE 'F'
END

7.2 DECODE

DECODE(dept_id, 10, 'IT', 20, 'HR', 30, 'Sales', 'Other')

---

8. 聚合函数

COUNT(*)           -- 行数
COUNT(column)      -- 非 NULL 数
SUM(salary)        -- 求和
AVG(salary)        -- 平均
MIN(salary)        -- 最小
MAX(salary)        -- 最大
STDDEV(salary)     -- 标准差
VARIANCE(salary)   -- 方差
MEDIAN(salary)     -- 中位数
STATS_MODE(salary) -- 众数

---

9. LISTAGG（字符串聚合）

9.1 基本用法

SELECT 
  dept_id,
  LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) AS employees
FROM employees
GROUP BY dept_id;
-- 10: Alice,Bob,Charlie

9.2 去重（19c+）

SELECT 
  dept_id,
  LISTAGG(DISTINCT last_name, ',') WITHIN GROUP (ORDER BY last_name) AS employees
FROM employees
GROUP BY dept_id;

9.3 处理超长（12c R2+）

LISTAGG(last_name, ',' ON OVERFLOW TRUNCATE '...' WITH COUNT) WITHIN GROUP (ORDER BY last_name)

---

10. 分析函数

详细见：Oracle 分析函数（Analytic Functions）详解

ROW_NUMBER() OVER (ORDER BY salary DESC)
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
LAG(salary, 1) OVER (ORDER BY hire_date)
LEAD(salary, 1) OVER (ORDER BY hire_date)
SUM(salary) OVER (PARTITION BY dept_id)

---

11. XML 函数

XMLELEMENT("employee", XMLATTRIBUTES(last_name AS name, salary))
XMLFOREST(last_name, salary)
XMLAGG(XMLELEMENT("name", last_name) ORDER BY last_name)
XMLQUERY('/root/employee' PASSING xml_col RETURNING CONTENT)
EXTRACT(xml_col, '/root/employee/text()')

---

12. JSON 函数（12c+）

-- 解析 JSON
JSON_VALUE('{"name":"Alice"}', '$.name')  -- Alice
JSON_QUERY('{"emp":{"name":"Alice"}}', '$.emp')
JSON_EXISTS('{"name":"Alice"}', '$.name')

-- 生成 JSON
JSON_OBJECT('name' VALUE 'Alice', 'age' VALUE 30)
JSON_ARRAY('Alice', 'Bob', 'Charlie')

-- 表数据转 JSON
SELECT JSON_OBJECT('id' VALUE employee_id, 'name' VALUE last_name) 
FROM employees;

---

13. 常用场景

13.1 字符串拼接

-- LISTAGG
LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)

-- 不同分隔符
REPLACE(LISTAGG(name, '|') WITHIN GROUP (ORDER BY name), '|', ', ')

13.2 日期范围

-- 本月
WHERE hire_date >= TRUNC(SYSDATE, 'MM')
  AND hire_date < ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)

-- 上月
WHERE hire_date >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
  AND hire_date < TRUNC(SYSDATE, 'MM')

-- 本季度
WHERE hire_date >= TRUNC(SYSDATE, 'Q')
  AND hire_date < ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3)

13.3 排名

-- Top 3
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees e
) WHERE rn <= 3;

---

14. 常见坑

14.1 NULL 与聚合

-- AVG 忽略 NULL
AVG(salary)  -- 平均非 NULL

-- 包含 NULL
AVG(NVL(salary, 0))  -- 平均所有

14.2 字符串与数字

-- 隐式转换慢
WHERE string_col = 123  -- 转换为数字

-- 显式转换
WHERE string_col = TO_CHAR(123)
-- 或
WHERE TO_NUMBER(string_col) = 123

14.3 日期格式

-- 注意 NLS 设置
TO_DATE('2026-07-21', 'YYYY-MM-DD')  -- 显式格式

---

15. 最佳实践

1. 使用合适函数：性能好
2. 显式转换：避免隐式
3. NULL 处理：NVL/COALESCE
4. 日期格式明确：避免 NLS 依赖
5. 使用 CASE 而非 DECODE：可读性
6. LISTAGG 处理字符串聚合：高效
7. 分析函数替代自连接：性能
8. 避免函数阻止索引：列上不加函数
9. 测试边界：NULL/空字符串
10. 参考文档：新函数

---

16. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Functions”
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html