Oracle 函数统计大全 本文系统梳理 Oracle 数据库全量函数体系，涵盖从基础到高级的所有函数类别，提供详细语法、示例和实战场景。
一、聚合函数（Aggregate Functions） 对多行数据进行汇总计算，常与GROUP BY子句配合使用。
1.1 基础聚合函数 函数 功能 示例 COUNT 统计行数或非 NULL 值个数 SELECT COUNT(*) FROM employees;SUM 计算数值总和 SELECT SUM(salary) FROM employees;AVG 计算平均值 SELECT AVG(salary) FROM employees;MAX 返回最大值 SELECT MAX(salary) FROM employees;MIN 返回最小值 SELECT MIN(salary) FROM employees;
性能差异 ：
COUNT(*)：统计所有行，需要扫描表，性能一般COUNT(1)：统计行数，不需要扫描具体列，性能较快COUNT(column)：统计非 NULL 值个数，扫描非 NULL 列COUNT(DISTINCT column)：计算不同值数量，使用排序，性能较慢-- 部门薪资统计 SELECT department_id, COUNT ( * ) AS 员工数, SUM ( salary) AS 总薪资, AVG ( salary) AS 平均薪资, MAX ( salary) AS 最高薪资, MIN ( salary) AS 最低薪资FROM employeesGROUP BY department_id; 1.2 高级聚合函数 函数 功能 GROUPING SETS 多维度分组汇总 ROLLUP 分层汇总，生成小计和总计 CUBE 多维立方体汇总 LISTAGG 字符串聚合（11gR2+） WM_CONCAT 字符串聚合（10g-12c，已废弃）
-- ROLLUP 分层汇总 SELECT department_id, manager_id, SUM ( salary) FROM employeesGROUP BY ROLLUP( department_id, manager_id) ; -- 结果：先按 department+manager 汇总，再按 department 汇总，最后总计 -- CUBE 多维汇总 SELECT department_id, job_id, AVG ( salary) FROM employeesGROUP BY CUBE( department_id, job_id) ; -- 结果：所有维度组合（dept+job, dept, job, 总计） -- LISTAGG 字符串聚合 SELECT department_id, LISTAGG( first_name, ',' ) WITHIN GROUP ( ORDER BY salaryDESC ) AS employeesFROM employeesGROUP BY department_id; 二、字符串函数 处理和操作字符数据，是数据处理中最常用的函数类别。
2.1 大小写转换 函数 功能 示例 UPPER 转大写 UPPER('oracle') → 'ORACLE'LOWER 转小写 LOWER('SQL') → 'sql'INITCAP 首字母大写 INITCAP('hello world') → 'Hello World'
2.2 字符串连接与截取 函数 功能 示例 CONCAT 连接两个字符串 CONCAT('A', 'B') → 'AB'|| 连接操作符 'A' || 'B' → 'AB'SUBSTR 截取子串 SUBSTR('Oracle', 1, 3) → 'Ora'LENGTH 返回字符串长度 LENGTH('Oracle') → 6
2.3 查找与替换 函数 功能 示例 INSTR 查找子串位置 INSTR('Oracle SQL', 'SQL') → 8REPLACE 替换子串 REPLACE('SQL*Plus', '*', ' ') → 'SQL Plus'TRANSLATE 字符替换 TRANSLATE('ABC', 'BC', 'XY') → 'AXY'
2.4 填充与修剪 函数 功能 示例 LPAD 左填充 LPAD('123', 5, '0') → '00123'RPAD 右填充 RPAD('456', 5, '*') → '456**'LTRIM 左修剪 LTRIM(' ABC') → 'ABC'RTRIM 右修剪 RTRIM('ABC ') → 'ABC'TRIM 两端修剪 TRIM(' ABC ') → 'ABC'
2.5 其他字符串函数 -- ASCII/CHR：字符与ASCII码互转 SELECT ASCII( 'A' ) FROM dual; -- 65 SELECT CHR( 65 ) FROM dual; -- 'A' -- REGEXP_LIKE：正则表达式匹配 SELECT * FROM employeesWHERE REGEXP_LIKE( email, '^[a-z]+@[a-z]+\.com$' ) ; -- REGEXP_SUBSTR：正则提取 SELECT REGEXP_SUBSTR( 'John,Smith,25' , '[^,]+' , 1 , 2 ) FROM dual; -- 'Smith' 三、数值函数 对数字进行计算和处理，支持数学运算和统计分析。
3.1 四舍五入与截断 函数 功能 示例 ROUND 四舍五入 ROUND(123.456, 2) → 123.46TRUNC 截断 TRUNC(123.456, 2) → 123.45CEIL 向上取整 CEIL(123.1) → 124FLOOR 向下取整 FLOOR(123.9) → 123
3.2 数学运算 函数 功能 示例 MOD 取模 MOD(10, 3) → 1POWER 幂运算 POWER(2, 3) → 8ABS 绝对值 ABS(-123) → 123SIGN 符号函数 SIGN(-123) → -1
3.3 三角函数与对数 -- 三角函数 SELECT SIN( 3.14159 ) , COS( 3.14159 ) , TAN( 3.14159 ) FROM dual; -- 对数 SELECT LOG( 10 , 100 ) FROM dual; -- 以10为底，100的对数 → 2 -- 平方根 SELECT SQRT( 16 ) FROM dual; -- 4 3.4 统计分析函数 -- 标准差与方差 SELECT STDDEV( salary) , VARIANCE( salary) FROM employees; -- 中位数 SELECT MEDIAN( salary) FROM employees; -- 百分位数 SELECT PERCENTILE_CONT( 0.5 ) WITHIN GROUP ( ORDER BY salary) FROM employees; 四、日期时间函数 Oracle 日期处理功能极其强大，支持复杂的时间计算。
4.1 获取当前时间 函数 功能 返回类型 SYSDATE 当前日期时间 DATE SYSTIMESTAMP 当前时间戳 TIMESTAMP CURRENT_DATE 当前会话日期 DATE CURRENT_TIMESTAMP 当前会话时间戳 TIMESTAMP
SELECT SYSDATE, SYSTIMESTAMPFROM dual; 4.2 日期加减 函数 功能 示例 +/- 加减天数 SYSDATE + 1（明天）ADD_MONTHS 加月份 ADD_MONTHS(SYSDATE, 3)MONTHS_BETWEEN 月份差 MONTHS_BETWEEN(SYSDATE, hire_date)
-- 计算明天、一小时前 SELECT SYSDATE+ 1 AS tomorrowFROM dual; SELECT SYSDATE- 1 / 24 AS one_hour_agoFROM dual; -- 加3个月 SELECT ADD_MONTHS( SYSDATE, 3 ) FROM dual; -- 计算工龄（月数） SELECT MONTHS_BETWEEN( SYSDATE, hire_date) FROM employees; 4.3 日期提取与格式化 函数 功能 示例 EXTRACT 提取年月日 EXTRACT(YEAR FROM SYSDATE)TO_CHAR 格式化日期 TO_CHAR(SYSDATE, 'YYYY-MM-DD')NEXT_DAY 下个星期几 NEXT_DAY(SYSDATE, 'MONDAY')LAST_DAY 当月最后一天 LAST_DAY(SYSDATE)
-- 提取年月日 SELECT EXTRACT( YEAR FROM SYSDATE) AS year , EXTRACT( MONTH FROM SYSDATE) AS month , EXTRACT( DAY FROM SYSDATE) AS day FROM dual; -- 格式化日期 SELECT TO_CHAR( SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS' ) FROM dual; -- 下周一 SELECT NEXT_DAY( SYSDATE, 'MONDAY' ) FROM dual; -- 本月最后一天 SELECT LAST_DAY( SYSDATE) FROM dual; 4.4 日期截断与舍入 -- TRUNC 截断到指定单位 SELECT TRUNC( SYSDATE, 'MONTH' ) FROM dual; -- 当月第一天 SELECT TRUNC( SYSDATE, 'YEAR' ) FROM dual; -- 当年第一天 -- ROUND 舍入到指定单位 SELECT ROUND ( SYSDATE, 'MONTH' ) FROM dual; 五、转换函数 实现不同数据类型间的转换，是数据清洗的关键工具。
5.1 字符串转换 函数 功能 示例 TO_CHAR 转字符串 TO_CHAR(12345.67, 'L99,999.99')TO_NUMBER 转数字 TO_NUMBER('123.45')TO_DATE 转日期 TO_DATE('2023-12-25', 'YYYY-MM-DD')CAST 通用转换 CAST('123' AS NUMBER)
-- 数字格式化（货币） SELECT TO_CHAR( salary, 'L99,999.99' ) FROM employees; -- ￥12,345.67 -- 日期格式化 SELECT TO_CHAR( SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) FROM dual; -- 字符串转数字 SELECT TO_NUMBER( '123.45' ) + 100 FROM dual; -- 223.45 -- 字符串转日期 SELECT TO_DATE( '2023-12-25 10:30:00' , 'YYYY-MM-DD HH24:MI:SS' ) FROM dual; 5.2 空值处理 函数 功能 示例 NVL 空值替换 NVL(commission_pct, 0)NVL2 空值判断 NVL2(expr1, expr2, expr3)COALESCE 返回首个非NULL COALESCE(expr1, expr2, ...)NULLIF 相等返回NULL NULLIF(expr1, expr2)
-- NVL 处理空值 SELECT first_name, NVL( commission_pct, 0 ) FROM employees; -- COALESCE 多值判断 SELECT COALESCE ( phone_mobile, phone_home, phone_work, 'N/A' ) FROM contacts; -- NULLIF 避免除零 SELECT NULLIF ( salary, 0 ) FROM employees; 六、条件判断函数 6.1 DECODE 函数 Oracle 特有的条件判断函数，类似 switch-case。
-- 语法：DECODE(expr, search1, result1, search2, result2, ..., default) SELECT employee_id, DECODE( department_id, 10 , 'Admin' , 20 , 'Marketing' , 30 , 'Purchasing' , 'Other' ) AS dept_nameFROM employees; 6.2 CASE 表达式 标准 SQL 条件判断，更灵活。
-- 简单 CASE SELECT employee_id, CASE department_idWHEN 10 THEN 'Admin' WHEN 20 THEN 'Marketing' ELSE 'Other' END AS dept_nameFROM employees; -- 搜索 CASE（支持复杂条件） SELECT employee_id, salary, CASE WHEN salary> 10000 THEN 'High' WHEN salary> 5000 THEN 'Medium' ELSE 'Low' END AS salary_levelFROM employees; 6.3 NULL 判断函数 -- NULLIF SELECT NULLIF ( first_name, last_name) FROM employees; -- 相同返回NULL -- LNNVL（否定条件） SELECT * FROM employeesWHERE LNNVL( salary> 5000 ) ; -- 等价于 salary <= 5000 或 salary IS NULL 七、分析函数（窗口函数） 分析函数是 Oracle 高级特性，用于在结果集上执行计算，不改变行数。
7.1 排序函数 函数 功能 区别 RANK 排名，相同值并列，有间隔 1,1,3 DENSE_RANK 密集排名，相同值并列，无间隔 1,1,2 ROW_NUMBER 唯一序号，相同值也区分 1,2,3
-- 员工薪水排名 SELECT employee_id, salary, RANK( ) OVER ( ORDER BY salaryDESC ) AS rank, DENSE_RANK( ) OVER ( ORDER BY salaryDESC ) AS dense_rank, ROW_NUMBER( ) OVER ( ORDER BY salaryDESC ) AS row_numFROM employees; 7.2 聚合分析函数 -- 部门内薪水占比 SELECT employee_id, department_id, salary, SUM ( salary) OVER ( PARTITION BY department_id) AS dept_total, salary/ SUM ( salary) OVER ( PARTITION BY department_id) AS ratioFROM employees; -- 累计求和 SELECT employee_id, hire_date, salary, SUM ( salary) OVER ( ORDER BY hire_date) AS running_totalFROM employees; 7.3 取值函数 -- LAG/LEAD：前后行取值 SELECT employee_id, hire_date, salary, LAG( salary, 1 ) OVER ( ORDER BY hire_date) AS prev_salary, LEAD( salary, 1 ) OVER ( ORDER BY hire_date) AS next_salaryFROM employees; -- FIRST_VALUE/LAST_VALUE：窗口首尾值 SELECT employee_id, department_id, salary, FIRST_VALUE( salary) OVER ( PARTITION BY department_idORDER BY salaryDESC ) AS highest_salaryFROM employees; 7.4 窗口定义 -- ROWS 窗口（物理行） SELECT employee_id, salary, AVG ( salary) OVER ( ORDER BY employee_idROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS moving_avgFROM employees; -- RANGE 窗口（逻辑范围） SELECT employee_id, salary, SUM ( salary) OVER ( ORDER BY salary RANGEBETWEEN 1000 PRECEDING AND 1000 FOLLOWING ) AS range_sumFROM employees; 八、高级函数 8.1 随机数生成 -- DBMS_RANDOM 包 SELECT DBMS_RANDOM. VALUE ( 0 , 100 ) FROM dual; -- 0-100随机数 SELECT DBMS_RANDOM. STRING( 'U' , 10 ) FROM dual; -- 10位大写随机字符串 8.2 层次查询函数 -- CONNECT_BY_ROOT：根节点值 SELECT employee_id, last_name, CONNECT_BY_ROOT last_nameAS root_nameFROM employeesSTART WITH manager_idIS NULL CONNECT BY PRIOR employee_id= manager_id; -- SYS_CONNECT_BY_PATH：路径字符串 SELECT employee_id, SYS_CONNECT_BY_PATH( last_name, '/' ) AS pathFROM employeesSTART WITH manager_idIS NULL CONNECT BY PRIOR employee_id= manager_id; 8.3 其他实用函数 -- UID：当前用户ID SELECT UIDFROM dual; -- USER：当前用户名 SELECT USER FROM dual; -- GREATEST/LEAST：多值比较 SELECT GREATEST( 10 , 20 , 30 ) , LEAST( 10 , 20 , 30 ) FROM dual; -- 30, 10 -- BIN_TO_NUM：二进制转数字 SELECT BIN_TO_NUM( 1 , 0 , 1 ) FROM dual; -- 5 九、实战场景与性能提示 9.1 场景示例 -- 场景1：员工绩效评级 SELECT employee_id, salary, CASE WHEN salary> PERCENTILE_CONT( 0.9 ) WITHIN GROUP ( ORDER BY salary) OVER ( ) THEN 'S' WHEN salary> PERCENTILE_CONT( 0.7 ) WITHIN GROUP ( ORDER BY salary) OVER ( ) THEN 'A' WHEN salary> PERCENTILE_CONT( 0.5 ) WITHIN GROUP ( ORDER BY salary) OVER ( ) THEN 'B' ELSE 'C' END AS performance_gradeFROM employees; -- 场景2：新员工留存率 SELECT EXTRACT( YEAR FROM hire_date) AS hire_year, COUNT ( * ) AS hired, COUNT ( CASE WHEN termination_dateIS NULL THEN 1 END ) AS retained, ROUND ( COUNT ( CASE WHEN termination_dateIS NULL THEN 1 END ) / COUNT ( * ) * 100 , 2 ) AS retention_rateFROM employeesGROUP BY EXTRACT( YEAR FROM hire_date) ; 9.2 性能优化建议 聚合函数 ：在 WHERE 子句中过滤数据后再聚合，减少计算量分析函数 ：避免在大数据集上滥用ORDER BY导致排序溢出，可配合PARTITION BY缩小窗口字符串函数 ：在 WHERE 子句中对列使用函数会导致索引失效，可考虑函数索引日期函数 ：优先使用EXTRACT而非TO_CHAR进行日期判断，性能更好空值处理 ：NVL和COALESCE在大量数据上有性能差异，COALESCE 更优十、总结 函数分类速查表 类别 核心函数 使用频率 难度 聚合函数 COUNT, SUM, AVG, MAX, MIN ⭐⭐⭐⭐⭐ 低 字符串函数 SUBSTR, LENGTH, INSTR, TO_CHAR ⭐⭐⭐⭐⭐ 中 数值函数 ROUND, TRUNC, MOD, ABS ⭐⭐⭐⭐ 中 日期函数 SYSDATE, ADD_MONTHS, MONTHS_BETWEEN ⭐⭐⭐⭐⭐ 中 转换函数 TO_CHAR, TO_DATE, TO_NUMBER, NVL ⭐⭐⭐⭐⭐ 中 条件函数 CASE, DECODE, COALESCE ⭐⭐⭐⭐ 中 分析函数 RANK, DENSE_RANK, ROW_NUMBER, SUM(…) OVER ⭐⭐⭐ 高 高级函数 DBMS_RANDOM, CONNECT_BY_ROOT, LISTAGG ⭐⭐ 高
学习建议 ：
新手 ：掌握聚合、字符串、日期、转换函数，覆盖 80% 日常需求进阶 ：熟练使用 CASE 和窗口函数，解决复杂业务场景专家 ：深入理解分析函数执行原理，优化大数据量查询性能Oracle 函数体系庞大且功能强大，建议结合实际业务场景反复练习，方能融会贯通。
