DB / Oracle / SQL 开发

Oracle SQL 函数大全

2026.09.06

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 格式说明

日期格式说明
YYYY4 位年
YY2 位年
MM月
DD日
HH2424 小时
HH12 小时
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