以下是 Oracle 数据库函数的详细分类说明及用法示例，涵盖核心函数及常见应用场景。内容基于 Oracle 19c+ 版本，部分函数在旧版本中可能不可用。
一、字符串函数
1. SUBSTR(str, start, [length])
- 功能: 截取子字符串。
- 示例:sql复制SELECT SUBSTR(‘Oracle Database’, 8, 8) FROM DUAL; — 输出 ‘Database’
2. INSTR(str, substr, [start], [occurrence])
- 功能: 查找子字符串的位置。
- 示例:sql复制SELECT INSTR(‘Hello World’, ‘o’, 1, 2) FROM DUAL; — 第2次出现的位置，输出 8
3. CONCAT(str1, str2) 与 ||
- 功能: 拼接字符串。
- 示例:sql复制SELECT CONCAT(‘Hello’, ‘ Oracle’) FROM DUAL; — 输出 ‘Hello Oracle’ SELECT ‘Hello’ || ‘ ‘ || ‘World’ FROM DUAL; — 输出 ‘Hello World’
4. LENGTH(str)
- 功能: 返回字符串的字符数。
- 示例:sql复制SELECT LENGTH(‘Oracle’) FROM DUAL; — 输出 6
5. REPLACE(str, old_str, new_str)
- 功能: 替换字符串中的内容。
- 示例:sql复制SELECT REPLACE(‘I love Java’, ‘Java’, ‘SQL’) FROM DUAL; — 输出 ‘I love SQL’
二、数值函数
1. ROUND(number, [decimals])
- 功能: 四舍五入到指定小数位。
- 示例:sql复制SELECT ROUND(3.1415, 2) FROM DUAL; — 输出 3.14
2. TRUNC(number, [decimals])
- 功能: 截断数值（不四舍五入）。
- 示例:sql复制SELECT TRUNC(3.1415, 2) FROM DUAL; — 输出 3.14 SELECT TRUNC(123.456, -1) FROM DUAL; — 截断十位，输出 120
3. MOD(numerator, denominator)
- 功能: 取余数。
- 示例:sql复制SELECT MOD(10, 3) FROM DUAL; — 输出 1
三、日期时间函数
1. SYSDATE
- 功能: 返回当前系统日期和时间。
- 示例:sql复制SELECT SYSDATE FROM DUAL; — 输出类似 ‘2023-10-25 14:30:00’
2. TO_DATE(str, format)
- 功能: 将字符串转换为日期。
- 示例:sql复制SELECT TO_DATE(‘2023-10-25’, ‘YYYY-MM-DD’) FROM DUAL; — 输出日期对象
3. ADD_MONTHS(date, num)
- 功能: 添加指定月数。
- 示例:sql复制SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL; — 当前日期加3个月
4. MONTHS_BETWEEN(date1, date2)
- 功能: 计算两个日期之间的月数差。
- 示例:sql复制SELECT MONTHS_BETWEEN(‘2023-12-31’, ‘2023-10-25’) FROM DUAL; — 输出约 2.2
四、聚合函数
1. SUM(column)
- 功能: 计算数值列总和。
- 示例:sql复制SELECT SUM(salary) FROM employees;
2. LISTAGG(column, delimiter)
- 功能: 合并分组中的字符串（Oracle 11g R2+）。
- 示例:sql复制SELECT department_id, LISTAGG(name, ‘, ‘) WITHIN GROUP (ORDER BY name) FROM employees GROUP BY department_id; — 输出部门员工列表（逗号分隔）
五、分析函数（窗口函数）
1. ROW_NUMBER()
- 功能: 为行生成唯一序号。
- 示例:sql复制SELECT name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank FROM employees;
2. RANK() 与 DENSE_RANK()
- 区别:
RANK(): 允许并列排名（如 1,1,3）。DENSE_RANK(): 允许并列排名（如 1,1,2）。
六、转换函数
1. TO_CHAR(expr, format)
- 功能: 将日期或数值转换为字符串。
- 示例:sql复制SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) FROM DUAL; — 格式化日期 SELECT TO_CHAR(1234.56, ‘9999.99’) FROM DUAL; — 输出 ‘ 1234.56’
2. TO_NUMBER(str, format)
- 功能: 将字符串转换为数值。
- 示例:sql复制SELECT TO_NUMBER(‘$1,234.56’, ‘L999G999D99’) FROM DUAL; — 输出 1234.56
七、条件函数
1. DECODE(value, search1, result1, ..., default)
- 功能: 简单条件分支（类似
CASE）。 - 示例:sql复制SELECT DECODE(status, 1, ‘Active’, 0, ‘Inactive’, ‘Unknown’) FROM users;
2. CASE WHEN
- 功能: 复杂条件分支。
- 示例:sql复制SELECT CASE WHEN score >= 90 THEN ‘A’ WHEN score >= 80 THEN ‘B’ ELSE ‘C’ END AS grade FROM exams;
八、高级函数
1. NVL(value, default)
- 功能: 替换
NULL值。 - 示例:sql复制SELECT NVL(middle_name, ‘N/A’) FROM employees;
2. REGEXP_SUBSTR(str, pattern)
- 功能: 正则表达式匹配子字符串。
- 示例:sql复制SELECT REGEXP_SUBSTR(‘Email: user@example.com’, ‘\w+@\w+\.\w+’) FROM DUAL; — 输出 ‘user@example.com’
九、JSON 函数（Oracle 12c+）
1. JSON_VALUE(json, path)
- 功能: 提取 JSON 字段值。
- 示例:sql复制SELECT JSON_VALUE(‘{“name”: “John”, “age”: 30}’, ‘$.name’) FROM DUAL; — 输出 ‘John’
2. JSON_QUERY(json, path)
- 功能: 提取 JSON 对象或数组。
- 示例:sql复制SELECT JSON_QUERY(‘{“data”: [1, 2, 3]}’, ‘$.data’) FROM DUAL; — 输出 ‘[1,2,3]’
注意事项
- 性能优化:
- 避免在
WHERE条件中对字段使用函数（可能导致索引失效）。 - 错误示例：
SELECT * FROM logs WHERE TO_CHAR(create_time, 'YYYY-MM') = '2023-10'。 - 正确优化：
SELECT * FROM logs WHERE create_time >= TO_DATE('2023-10-01', 'YYYY-MM-DD')。
- 避免在
- 空值处理:
- 使用
NVL()或COALESCE()处理NULL值。
- 使用
- 版本兼容性:
LISTAGG需 Oracle 11g R2+。- JSON 函数需 Oracle 12c+。
THE END
