Oracle SQL 函数大全

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

1. 概述

Oracle SQL 函数总览[1]：

2. 字符函数

2.1 字符处理

函数	说明
UPPER(s)	大写
LOWER(s)	小写
INITCAP(s)	首字母大写
LENGTH(s)	长度
SUBSTR(s, m, n)	子串
INSTR(s, sub)	位置
REPLACE(s, a, b)	替换
TRANSLATE(s, a, b)	翻译
TRIM(s)	去空格
LTRIM(s)	去左空格
RTRIM(s)	去右空格
LPAD(s, n, c)	左填充
RPAD(s, n, c)	右填充
CONCAT(a, b)	连接
CHR(n)	字符
ASCII(s)	ASCII

2.2 示例

SELECT UPPER('smith'), LOWER('SMITH'), INITCAP('john smith') FROM dual;
SELECT SUBSTR('Hello World', 1, 5), INSTR('Hello', 'l') FROM dual;
SELECT REPLACE('abc', 'b', 'B'), TRANSLATE('abc', 'ab', 'AB') FROM dual;
SELECT LPAD('5', 5, '0'), RPAD('5', 5, '-') FROM dual;

3. 数值函数

3.1 数学

函数	说明
ROUND(n, m)	四舍五入
TRUNC(n, m)	截断
MOD(a, b)	模
ABS(n)	绝对值
CEIL(n)	上取整
FLOOR(n)	下取整
POWER(a, b)	幂
SQRT(n)	平方根
EXP(n)	e^n
LN(n)	自然对数
LOG(a, n)	对数
SIGN(n)	符号
GREATEST(…)	最大
LEAST(…)	最小

3.2 三角

SELECT SIN(0), COS(0), TAN(0), ASIN(0), ACOS(0), ATAN(0) FROM dual;

3.3 示例

SELECT ROUND(3.14159, 2), TRUNC(3.14159, 2), MOD(10, 3) FROM dual;
SELECT CEIL(3.1), FLOOR(3.9), POWER(2, 10), SQRT(16) FROM dual;

4. 日期函数

4.1 日期

函数	说明
SYSDATE	当前日期
SYSTIMESTAMP	当前时间戳
CURRENT_DATE	会话日期
CURRENT_TIMESTAMP	会话时间戳
ADD_MONTHS(d, n)	加月
MONTHS_BETWEEN(d1, d2)	月差
LAST_DAY(d)	月末
NEXT_DAY(d, day)	下一个
EXTRACT(field FROM d)	提取
TRUNC(d, fmt)	截断
ROUND(d, fmt)	四舍五入
TO_DATE(s, fmt)	转换
TO_CHAR(d, fmt)	字符
NUMTODSINTERVAL(n, unit)	间隔
NUMTOYMINTERVAL(n, unit)	月间隔

4.2 示例

SELECT SYSDATE, SYSTIMESTAMP FROM dual;
SELECT ADD_MONTHS(DATE '2026-01-01', 6) FROM dual;
SELECT MONTHS_BETWEEN(DATE '2026-12-01', DATE '2026-01-01') FROM dual;
SELECT LAST_DAY(DATE '2026-07-21'), NEXT_DAY(DATE '2026-07-21', 'MONDAY') FROM dual;
SELECT EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE) FROM dual;

5. 转换函数

5.1 类型

函数	说明
TO_CHAR(n, fmt)	数字转字符
TO_CHAR(d, fmt)	日期转字符
TO_DATE(s, fmt)	字符转日期
TO_NUMBER(s, fmt)	字符转数字
TO_TIMESTAMP(s, fmt)	时间戳
CAST(x AS type)	类型转换
CONVERT(s, d, s)	字符集
CHARTOROWID(s)	ROWID
ROWIDTOCHAR(r)	字符

5.2 示例

SELECT TO_CHAR(1234.56, '99,999.99') FROM dual;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_DATE('2026-07-21', 'YYYY-MM-DD') FROM dual;
SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;
SELECT CAST(123 AS VARCHAR2(10)) FROM dual;

6. NULL 函数

函数	说明
NVL(a, b)	a NULL 返回 b
NVL2(a, b, c)	a NULL 返回 c，否则 b
COALESCE(…)	第一个非 NULL
NULLIF(a, b)	相等返回 NULL
DECODE(…)	条件
LNNVL(cond)	NULL 或 false 返回 true

SELECT NVL(NULL, 0), NVL2(NULL, 1, 0) FROM dual;
SELECT COALESCE(NULL, NULL, 'a', 'b') FROM dual;
SELECT NULLIF(1, 1), NULLIF(1, 2) FROM dual;
SELECT DECODE(1, 1, 'one', 2, 'two', 'other') FROM dual;

7. 聚合函数

函数	说明
COUNT(…)	计数
SUM(…)	求和
AVG(…)	平均
MAX(…)	最大
MIN(…)	最小
STDDEV(…)	标准差
VARIANCE(…)	方差
MEDIAN(…)	中位数
STATS_MODE(…)	众数
LISTAGG(…)	字符串聚合

SELECT COUNT(*), SUM(salary), AVG(salary), MAX(salary), MIN(salary) 
FROM employees;

SELECT dept_id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) 
FROM employees GROUP BY dept_id;

详细见：Oracle 高级分析函数。

8. 分析函数

8.1 排名

函数	说明
ROW_NUMBER()	行号
RANK()	排名（同并列，跳）
DENSE_RANK()	排名（同并列，不跳）
NTILE(n)	分组
PERCENT_RANK()	百分比排名
CUME_DIST()	累积分布

8.2 偏移

函数	说明
LAG(…)	之前
LEAD(…)	之后
FIRST_VALUE(…)	第一
LAST_VALUE(…)	最后
NTH_VALUE(…)	第 n

8.3 窗口

函数	说明
SUM(…) OVER	累积
AVG(…) OVER	移动平均
COUNT(…) OVER	计数
RATIO_TO_REPORT()	比率

8.4 示例

SELECT name, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
  RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
  LAG(salary) OVER (PARTITION BY dept_id ORDER BY salary) AS prev_sal
FROM employees;

SELECT name, salary,
  SUM(salary) OVER (PARTITION BY dept_id) AS dept_total,
  RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id) AS pct
FROM employees;

详细见：Oracle 高级分析函数。

9. 字符串聚合

9.1 LISTAGG（11g+）

SELECT dept_id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) 
FROM employees 
GROUP BY dept_id;

-- 12c+ DISTINCT
SELECT LISTAGG(DISTINCT name, ',') WITHIN GROUP (ORDER BY name) ...

9.2 XMLAgg

SELECT dept_id, 
  RTRIM(XMLAGG(XMLELEMENT(e, name || ',').EXTRACT('//text()')).GETSTRINGVAL(), ',')
FROM employees GROUP BY dept_id;

10. 正则

函数	说明
REGEXP_LIKE(s, p)	匹配
REGEXP_SUBSTR(s, p)	子串
REGEXP_INSTR(s, p)	位置
REGEXP_REPLACE(s, p, r)	替换
REGEXP_COUNT(s, p)	计数

SELECT * FROM employees WHERE REGEXP_LIKE(name, '^S.*h$');
SELECT REGEXP_SUBSTR('a1b2c3', '[0-9]+') FROM dual;
SELECT REGEXP_REPLACE('abc123', '[0-9]+', 'X') FROM dual;

详细见：Oracle 正则表达式详解。

11. 条件

11.1 CASE

-- 简单
SELECT name, 
  CASE dept_id 
    WHEN 10 THEN 'IT' 
    WHEN 20 THEN 'Sales' 
    ELSE 'Other' 
  END AS dept
FROM employees;

-- 搜索
SELECT name,
  CASE 
    WHEN salary > 10000 THEN 'High'
    WHEN salary > 5000 THEN 'Medium'
    ELSE 'Low'
  END AS level
FROM employees;

11.2 DECODE

SELECT name, DECODE(dept_id, 10, 'IT', 20, 'Sales', 'Other') FROM employees;

12. JSON 函数（12c+）

函数	说明
JSON_VALUE	标量
JSON_QUERY	片段
JSON_EXISTS	存在
JSON_TABLE	表
JSON_OBJECT	对象
JSON_ARRAYAGG	聚合

详细见：Oracle JSON 处理详解。

13. XML 函数

函数	说明
XMLAGG	聚合
XMLELEMENT	元素
XMLFOREST	多元素
XMLATTRIBUTES	属性
XMLCONCAT	连接
XMLSERIALIZE	序列化
XMLTABLE	表
XMLQUERY	XQuery

详细见：Oracle XML 处理详解。

14. 其他

14.1 系统

函数	说明
USER	当前用户
UID	用户 ID
SYS_GUID()	GUID
USERENV(s)	会话信息
SYS_CONTEXT(n, p)	上下文
DBMS_RANDOM	随机

14.2 DUMP

SELECT DUMP('abc'), DUMP(123) FROM dual;

15. 自定义函数

CREATE OR REPLACE FUNCTION calc_bonus(p_salary NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_salary * 0.1;
END;
/

详细见：Oracle 存储过程与函数详解。

16. 最佳实践

正确函数：场景
避免 WHERE 中函数：索引
函数索引：必要
正则：复杂
聚合：性能
分析：复杂统计
类型转换显式：避免隐式
NULL 处理：NVL/COALESCE
测试：验证
文档：说明

17. 参考资料

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