Oracle 数据库提供了丰富的内置函数，可以用于数据处理、转换、计算和聚合等操作。以下是 Oracle 函数的分类和常用函数用法说明。
单行函数对查询返回的每一行进行处理，为每行返回一个结果。
UPPER/LOWER/INITCAP: 大小写转换
SELECT UPPER('hello'), LOWER('WORLD'), INITCAP('oracle database') FROM dual;
-- 结果: HELLO, world, Oracle Database
SUBSTR: 截取子字符串
SELECT SUBSTR('Oracle', 2, 3) FROM dual; -- 结果: rac
INSTR: 查找子字符串位置
SELECT INSTR('Oracle', 'a') FROM dual; -- 结果: 4
LENGTH: 字符串长度
SELECT LENGTH('Oracle') FROM dual; -- 结果: 6
TRIM/LTRIM/RTRIM: 去除空格或指定字符
SELECT TRIM(' Oracle ') FROM dual; -- 结果: Oracle
REPLACE: 字符串替换
SELECT REPLACE('Oracle', 'a', 'A') FROM dual; -- 结果: OrAcle
ROUND: 四舍五入
SELECT ROUND(45.926, 2) FROM dual; -- 结果: 45.93
TRUNC: 截断数值
SELECT TRUNC(45.926, 2) FROM dual; -- 结果: 45.92
MOD: 取余数
SELECT MOD(10, 3) FROM dual; -- 结果: 1
CEIL/FLOOR: 向上/向下取整
SELECT CEIL(3.14), FLOOR(3.14) FROM dual; -- 结果: 4, 3
SYSDATE: 当前系统日期和时间
SELECT SYSDATE FROM dual;
ADD_MONTHS: 增加月份
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
MONTHS_BETWEEN: 月份差
SELECT MONTHS_BETWEEN('01-JAN-2023', '01-MAR-2023') FROM dual; -- 结果: -2
LAST_DAY: 月份最后一天
SELECT LAST_DAY(SYSDATE) FROM dual;
NEXT_DAY: 下一个指定星期几的日期
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
EXTRACT: 提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
TO_CHAR: 转换为字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
SELECT TO_CHAR(1234.56, 'L9,999.99') FROM dual; -- 结果: ￥1,234.56
TO_NUMBER: 转换为数字
SELECT TO_NUMBER('1234.56') FROM dual;
TO_DATE: 转换为日期
SELECT TO_DATE('2023-01-15', 'YYYY-MM-DD') FROM dual;
NVL/NVL2: 处理NULL值
SELECT NVL(commission_pct, 0) FROM employees;
SELECT NVL2(commission_pct, '有佣金', '无佣金') FROM employees;
COALESCE: 返回第一个非NULL表达式
SELECT COALESCE(commission_pct, salary, 0) FROM employees;
NULLIF: 比较两个表达式
SELECT NULLIF(length('hello'), length('world')) FROM dual;
DECODE: 条件判断
SELECT DECODE(department_id, 10, '财务部', 20, '研发部', '其他部门') FROM employees;
CASE: 条件表达式
SELECT CASE
WHEN salary > 10000 THEN '高薪'
WHEN salary > 5000 THEN '中等'
ELSE '低薪'
END AS salary_level
FROM employees;
聚合函数对多行数据进行计算，返回单个结果。
AVG: 平均值
SELECT AVG(salary) FROM employees;
COUNT: 计数
SELECT COUNT(*) FROM employees;
SELECT COUNT(DISTINCT department_id) FROM employees;
MAX/MIN: 最大值/最小值
SELECT MAX(salary), MIN(salary) FROM employees;
SUM: 求和
SELECT SUM(salary) FROM employees;
LISTAGG: 字符串聚合
SELECT department_id, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name)
FROM employees
GROUP BY department_id;
分析函数在分组的基础上进行计算，但不会减少行数。
ROW_NUMBER: 行号
SELECT employee_id, last_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;
RANK/DENSE_RANK: 排名
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
LEAD/LAG: 访问前后行数据
SELECT employee_id, last_name, salary,
LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
LEAD(salary, 1, 0) OVER (ORDER BY salary) AS next_salary
FROM employees;
FIRST_VALUE/LAST_VALUE: 获取首尾值
SELECT employee_id, last_name, salary,
FIRST_VALUE(salary) OVER (ORDER BY salary) AS lowest_salary,
LAST_VALUE(salary) OVER (ORDER BY salary) AS current_salary
FROM employees;
Oracle 允许用户创建自定义函数：
CREATE OR REPLACE FUNCTION calculate_bonus(
p_salary IN NUMBER,
p_commission_pct IN NUMBER
) RETURN NUMBER
IS
v_bonus NUMBER;
BEGIN
v_bonus := p_salary * NVL(p_commission_pct, 0.1);
RETURN v_bonus;
END calculate_bonus;
/
-- 使用自定义函数
SELECT employee_id, last_name, salary, calculate_bonus(salary, commission_pct) AS bonus
FROM employees;
函数嵌套：可以嵌套多个函数
SELECT UPPER(SUBSTR(last_name, 1, 3)) FROM employees;
函数与GROUP BY结合：
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
函数在WHERE子句中使用：
SELECT * FROM employees
WHERE UPPER(last_name) LIKE 'SMITH%';
函数在ORDER BY中使用：
SELECT * FROM employees
ORDER BY LENGTH(last_name) DESC;
Oracle 函数功能强大，合理使用可以大大提高SQL语句的表达能力和数据处理效率。