← SQL开发设计

Oracle SQL 函数大全

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

1. 概述

Oracle SQL 函数总览[1]：

详细见：Oracle SQL 函数大全。

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. 最佳实践

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

17. 参考资料

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