大数据开发面试必背：Oracle函数核心考点（附实战示例）

在大数据开发/数据仓库建设中，Oracle是企业级核心数据库之一，其函数体系是面试高频考点。本文梳理Oracle常见数据类型、核心函数及面试常问对比，结合实战示例帮你快速掌握。

一、面试高频：Oracle常见数据类型

数据类型	用途	实战示例
Integer/Number	存储整数/数字（Number可指定精度，如NUMBER(10,2)表示10位数字、2位小数）	CREATE TABLE score (id INTEGER, score NUMBER(5,2));（存储学生分数，保留2位小数）
CHAR	固定长度字符（不足补空格，最大2000字符）	CREATE TABLE user_info (sex CHAR(1));（性别仅存1位，固定长度更高效）
VARCHAR2	可变长度字符（按需占用空间，最大4000字符）	CREATE TABLE user_info (name VARCHAR2(50));（用户名长度不固定，节省空间）
DATE	存储日期（包含年月日时分秒）	CREATE TABLE order_info (create_time DATE);（订单创建时间）
TIMESTAMP	时间戳（精度更高，可到纳秒）	CREATE TABLE log (operate_time TIMESTAMP(3));（操作日志，精确到毫秒）
BLOB	二进制大对象（存储文件、图片等）	CREATE TABLE file_store (file BLOB);（存储用户上传的图片/文档）
CLOB	字符大对象（存储大文本，如文章、日志）	CREATE TABLE article (content CLOB);（存储长篇文章内容）

二、面试必答：VARCHAR vs VARCHAR2 核心区别

这是Oracle面试最常考的对比题，核心差异如下：

维度	VARCHAR	VARCHAR2
标准归属	标准SQL定义	Oracle独有
字符存储	汉字占2字节，数字/英文占1字节（依字符集）	统一按字符集处理（GBK：汉字2字节/英文1字节；UTF8：汉字3字节/英文1字节）
空串处理	空串≠NULL	空串被当做NULL处理
长度限制	最大2000字符	最大4000字符
实际应用	几乎不用（Oracle已逐步废弃）	开发首选（兼容Oracle生态）

实战示例：

-- VARCHAR2空串处理示例
SELECT NVL('', '空串被当NULL') FROM DUAL; -- 输出：空串被当NULL
-- VARCHAR2长度示例
INSERT INTO user_info (name) VALUES (RPAD('A', 4000, 'A')); -- 成功（最大4000）
INSERT INTO user_info (name) VALUES (RPAD('A', 4001, 'A')); -- 报错（超出长度）

三、核心函数：面试必考+实战示例

1. 聚合函数（最基础，高频使用）

用于统计分析，需结合GROUP BY使用：

-- 示例：统计各班级的最高分、最低分、平均分、总分、人数
SELECT 
  class_id,
  MAX(score) AS max_score,  -- 最高分
  MIN(score) AS min_score,  -- 最低分
  AVG(score) AS avg_score,  -- 平均分
  SUM(score) AS sum_score,  -- 总分
  COUNT(*) AS student_count -- 人数
FROM score
GROUP BY class_id;

2. 数字函数（数据清洗/计算必备）

函数	功能	实战示例	输出结果
ABS(x)	绝对值	SELECT ABS(-10.5) FROM DUAL;	10.5
ROUND(x,d)	四舍五入	SELECT ROUND(10.234, 2) FROM DUAL;	10.23
TRUNC(x,d)	截断（不四舍五入）	SELECT TRUNC(10.239, 2) FROM DUAL;	10.23
FLOOR(x)	向下取整	SELECT FLOOR(10.9) FROM DUAL;	10
CEIL(x)	向上取整	SELECT CEIL(10.1) FROM DUAL;	11
POWER(x,y)	x的y次幂	SELECT POWER(2, 3) FROM DUAL;	8
MOD(x,y)	取余数	SELECT MOD(10, 3) FROM DUAL;	1

3. 字符函数（数据清洗高频）

-- 1. SUBSTR：截取子串（start从1开始）
SELECT SUBSTR('大数据开发面试', 1, 3) FROM DUAL; -- 输出：大数据
-- 2. REPLACE：替换字符
SELECT REPLACE('Oracle函数', '函数', '核心考点') FROM DUAL; -- 输出：Oracle核心考点
-- 3. CONCAT/||：拼接字符串
SELECT CONCAT('大数据', '开发') FROM DUAL; -- 输出：大数据开发
SELECT '大数据' || '面试' FROM DUAL; -- 输出：大数据面试（更常用）
-- 4. UPPER/LOWER：大小写转换
SELECT UPPER('oracle') FROM DUAL; -- 输出：ORACLE
SELECT LOWER('ORACLE') FROM DUAL; -- 输出：oracle
-- 5. LENGTH：字符长度
SELECT LENGTH('大数据开发') FROM DUAL; -- 输出：4（按字符数，非字节）
-- 6. INSTR：查找子串位置
SELECT INSTR('大数据开发面试', '开发') FROM DUAL; -- 输出：3（开发在第3位）
-- 7. LTRIM/RTRIM：去除左右指定字符
SELECT LTRIM('  abc  ', ' ') FROM DUAL; -- 输出：abc  （去左空格）
SELECT RTRIM('  abc  ', ' ') FROM DUAL; -- 输出：  abc（去右空格）

4. 开窗函数（面试重难点，大数据开发核心）

区别于聚合函数，开窗函数不分组，可保留原表所有行，返回每行的统计结果。

（1）聚合开窗函数

-- 示例：统计每个学生的分数，及所在班级的平均分
SELECT 
  student_id,
  class_id,
  score,
  AVG(score) OVER(PARTITION BY class_id) AS class_avg_score -- 按班级开窗算平均分
FROM score;

（2）排名函数（面试高频）

-- 示例：按分数给每个班级的学生排名
SELECT 
  student_id,
  class_id,
  score,
  ROW_NUMBER() OVER(PARTITION BY class_id ORDER BY score DESC) AS rn, -- 唯一排名 1,2,3,4
  RANK() OVER(PARTITION BY class_id ORDER BY score DESC) AS rk,       -- 跳级排名 1,2,2,4
  DENSE_RANK() OVER(PARTITION BY class_id ORDER BY score DESC) AS drk -- 密集排名 1,2,2,3
FROM score;

（3）平移函数（LAG/LEAD，数仓同步常用）

-- 示例：获取每个用户上一次/下一次的下单时间
SELECT 
  user_id,
  order_time,
  LAG(order_time, 1) OVER(PARTITION BY user_id ORDER BY order_time) AS last_order_time, -- 上一次下单时间
  LEAD(order_time, 1) OVER(PARTITION BY user_id ORDER BY order_time) AS next_order_time  -- 下一次下单时间
FROM order_info;

5. 逻辑函数（处理NULL/条件判断）

-- 1. NVL：NULL替换（nvl(字段, 替换值)）
SELECT NVL(score, 0) FROM score; -- 分数为NULL则显示0
-- 2. NVL2：NULL分支处理（nvl2(字段, 非NULL值, NULL值)）
SELECT NVL2(score, '有分数', '无分数') FROM score;
-- 3. DECODE：简单条件判断（类似if-else）
SELECT DECODE(sex, '1', '男', '2', '女', '未知') FROM user_info;
-- 4. CASE WHEN：复杂条件判断（更灵活）
SELECT 
  CASE 
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格' 
  END AS score_level
FROM score;

6. 转换函数（数据类型转换，必掌握）

-- 1. TO_CHAR：日期/数字转字符串
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 日期转指定格式字符串
SELECT TO_CHAR(1234.56, '9999.99') FROM DUAL; -- 数字转字符串
-- 2. TO_NUMBER：字符串转数字
SELECT TO_NUMBER('1234.56') FROM DUAL; -- 输出：1234.56
-- 3. TO_DATE：字符串转日期
SELECT TO_DATE('2026-03-13', 'YYYY-MM-DD') FROM DUAL; -- 输出：2026/3/13

7. 日期函数（时间处理高频）

-- 1. SYSDATE：当前系统时间
SELECT SYSDATE FROM DUAL; -- 输出：当前日期时间
-- 2. ADD_MONTHS：增减月份
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL; -- 输出：1个月后的今天
-- 3. LAST_DAY：当月最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL; -- 输出：本月最后一天
-- 4. MONTHS_BETWEEN：两个日期的月份差
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('2026-01-01', 'YYYY-MM-DD')) FROM DUAL;
-- 5. EXTRACT：提取日期部分
SELECT EXTRACT(YEAR FROM SYSDATE) AS year, EXTRACT(MONTH FROM SYSDATE) AS month FROM DUAL;

8. 行列转换（面试压轴题，数仓必备）

（1）行转列（PIVOT/UNION ALL）

-- 示例：将学生各科目分数从行转列
-- 原表（行数据）：score_info(student_id, subject, score)
-- 转换后（列数据）：student_id, 语文, 数学, 英语
SELECT * FROM score_info
PIVOT (
  MAX(score) FOR subject IN ('语文' AS 语文, '数学' AS 数学, '英语' AS 英语)
);

（2）列转行（UNPIVOT/CASE WHEN）

-- 示例：将学生列格式分数转行
-- 原表（列数据）：score_pivot(student_id, 语文, 数学, 英语)
-- 转换后（行数据）：student_id, subject, score
SELECT * FROM score_pivot
UNPIVOT (
  score FOR subject IN (语文, 数学, 英语)
);

总结

1. Oracle面试中，VARCHAR2是字符存储首选，需掌握其与VARCHAR的核心差异（空串处理、长度限制）；
2. 开窗函数（排名/平移）、行列转换是大数据开发面试重难点，需结合实战示例理解；
3. 转换函数（TO_CHAR/TO_DATE/TO_NUMBER）和日期函数是日常开发高频，需熟记格式和用法。