Oracle 常用函数

作者：IvanCodes
日期：2025年7月1日
专栏：Oracle教程

Oracle SQL 提供了极其丰富的内置函数库，这些函数是数据处理、查询和分析的强大武器。本教程将系统地介绍各类常用函数，并为每个函数提供独立的示例和注释结果。

思维导图

一、字符函数

1.1 UPPER(string)

• 功能：将字符串转换为大写。

SELECT UPPER('Hello Oracle') FROM dual;
-- 返回: 'HELLO ORACLE'

1.2 LOWER(string)

• 功能：将字符串转换为小写。

SELECT LOWER('Hello Oracle') FROM dual;
-- 返回: 'hello oracle'

1.3 INITCAP(string)

• 功能：将字符串中每个单词的首字母大写。

SELECT INITCAP('hello oracle world') FROM dual;
-- 返回: 'Hello Oracle World'

1.4 LENGTH(string)

• 功能：返回字符串的字符长度。

SELECT LENGTH('Oracle SQL') FROM dual;
-- 返回: 10

1.5 INSTR(string, substring, [start_position], [nth_appearance])

• 功能：返回子字符串在字符串中的位置。

SELECT INSTR('oracle sql is cool sql', 'sql', 1, 2) FROM dual;
-- 返回: 21 (从第1个字符开始查找，第2次出现的'sql'的位置)

1.6 SUBSTR(string, start_position, [length])

• 功能：从指定位置开始截取子字符串。

SELECT SUBSTR('Oracle Database', 8, 8) FROM dual;
-- 返回: 'Database' (从第8个字符开始，截取8个字符)

1.7 REPLACE(string, search_string, [replacement_string])

• 功能：替换字符串中所有出现的子字符串。

SELECT REPLACE('black cat and blue cat', 'cat', 'dog') FROM dual;
-- 返回: 'black dog and blue dog'

1.8 CONCAT(string1, string2)

• 功能：连接两个字符串。更常用的是 || 操作符。

SELECT CONCAT('Hello', ' World') FROM dual;
-- 返回: 'Hello World'

SELECT 'Oracle' || ' ' || 'SQL' FROM dual;
-- 返回: 'Oracle SQL'

1.9 LPAD(string, length, [pad_string])

• 功能：左侧填充字符到指定长度。

SELECT LPAD('123', 5, '0') FROM dual;
-- 返回: '00123'

1.10 RPAD(string, length, [pad_string])

• 功能：右侧填充字符到指定长度。

SELECT RPAD('abc', 5, '*') FROM dual;
-- 返回: 'abc**'

1.11 TRIM(string)

• 功能：去除字符串两边的空格。

SELECT TRIM('  Oracle  ') FROM dual;
-- 返回: 'Oracle'

1.12 LTRIM(string, [set])

• 功能：去除字符串左侧的指定字符集。

SELECT LTRIM('$$$100', '$') FROM dual;
-- 返回: '100'

1.13 RTRIM(string, [set])

• 功能：去除字符串右侧的指定字符集。

SELECT RTRIM('abc##', '#') FROM dual;
-- 返回: 'abc'

二、数值函数

2.1 ROUND(number, [decimal_places])

• 功能：对数字进行四舍五入。

SELECT ROUND(123.456, 2) FROM dual;
-- 返回: 123.46

2.2 TRUNC(number, [decimal_places])

• 功能：对数字进行截断。

SELECT TRUNC(123.456, 2) FROM dual;
-- 返回: 123.45

2.3 CEIL(number)

• 功能：返回大于或等于该数字的最小整数 (向上取整)。

SELECT CEIL(99.1) FROM dual;
-- 返回: 100

2.4 FLOOR(number)

• 功能：返回小于或等于该数字的最大整数 (向下取整)。

SELECT FLOOR(99.9) FROM dual;
-- 返回: 99

2.5 MOD(m, n)

• 功能：返回 m 除以 n 的余数。

SELECT MOD(10, 3) FROM dual;
-- 返回: 1

2.6 ABS(number)

• 功能：返回数字的绝对值。

SELECT ABS(-123) FROM dual;
-- 返回: 123

三、日期函数

3.1 SYSDATE

• 功能：返回当前数据库服务器的日期和时间。

SELECT SYSDATE FROM dual;
-- 返回: (当前日期和时间，例如 2024-03-22 10:30:00)

3.2 SYSTIMESTAMP

• 功能：返回当前数据库服务器的日期、时间，并包含小数秒和时区。

SELECT SYSTIMESTAMP FROM dual;
-- 返回: (当前日期时间+小数秒+时区，例如 22-MAR-24 10.30.00.123456 AM +08:00)

3.3 ADD_MONTHS(date, integer)

• 功能：增加或减少指定的月份数。

SELECT ADD_MONTHS(TO_DATE('2024-01-31', 'YYYY-MM-DD'), 1) FROM dual;
-- 返回: 29-FEB-24 (会自动处理月末日期)

3.4 MONTHS_BETWEEN(date1, date2)

• 功能：返回两个日期之间的月份数。

SELECT MONTHS_BETWEEN(TO_DATE('2024-07-15', 'YYYY-MM-DD'), TO_DATE('2024-01-15', 'YYYY-MM-DD')) FROM dual;
-- 返回: 6

3.5 LAST_DAY(date)

• 功能：返回指定日期所在月份的最后一天。

SELECT LAST_DAY(TO_DATE('2024-02-10', 'YYYY-MM-DD')) FROM dual;
-- 返回: 29-FEB-24 (2024是闰年)

3.6 NEXT_DAY(date, ‘day_of_week’)

• 功能：返回指定日期之后第一个指定星期几的日期。

SELECT NEXT_DAY(TO_DATE('2024-03-22', 'YYYY-MM-DD'), '星期一') FROM dual; -- 假设NLS_DATE_LANGUAGE是中文
-- 返回: 25-MAR-24

3.7 TRUNC(date, [format_model])

• 功能：按指定格式截断日期。

SELECT TRUNC(SYSDATE, 'MM') FROM dual;
-- 返回: (当月的第一天，例如 01-MAR-24)

3.8 EXTRACT(unit FROM date)

• 功能：从日期中提取特定部分。

SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
-- 返回: (当前年份，例如 2024)

四、转换函数

4.1 TO_CHAR(date/number, [format_model])

• 功能：将日期或数字转换为指定格式的字符串。

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 返回: '2024-03-22 10:30:00' (示例)

SELECT TO_CHAR(12345.67, 'FM99G999D00') FROM dual;
-- 返回: '12,345.67'

4.2 TO_DATE(string, [format_model])

• 功能：将符合特定格式的字符串转换为日期类型。

SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM dual;
-- 返回: 15-JAN-24 (日期类型)

4.3 TO_NUMBER(string, [format_model])

• 功能：将字符串转换为数字类型。

SELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual;
-- 返回: 1234.56 (数字类型)

五、聚合函数

(通常与 GROUP BY 配合使用，此处为简化，对全表操作)

5.1 COUNT(*) / COUNT(column) / COUNT(DISTINCT column)

• 功能：计算行数。

-- 假设 employees 表有10条记录, 其中 commission_pct 有3个非空值，2种不同的非空值
SELECT COUNT(*), COUNT(commission_pct), COUNT(DISTINCT commission_pct) FROM employees;
-- 返回: 10, 3, 2

5.2 SUM(expression)

• 功能：计算总和。

SELECT SUM(salary) FROM employees;
-- 返回: (所有员工薪水总和)

5.3 AVG(expression)

• 功能：计算平均值。

SELECT AVG(salary) FROM employees;
-- 返回: (所有员工薪水平均值)

5.4 MAX(expression)

• 功能：找出最大值。

SELECT MAX(salary) FROM employees;
-- 返回: (最高薪水)

5.5 MIN(expression)

• 功能：找出最小值。

SELECT MIN(salary) FROM employees;
-- 返回: (最低薪水)

六、通用/其他函数

6.1 NVL(expr1, expr2)

• 功能：如果 expr1 不为NULL，返回 expr1；否则返回 expr2。

SELECT NVL(commission_pct, 0) FROM employees;
-- 返回: (如果commission_pct是NULL，则显示0，否则显示其本身的值)

6.2 NVL2(expr1, expr2, expr3)

• 功能：如果 expr1 不为NULL，返回 expr2；否则返回 expr3。

SELECT NVL2(commission_pct, 'Has Commission', 'No Commission') FROM employees;
-- 返回: (根据commission_pct是否为NULL，显示不同的字符串)

6.3 DECODE(expr, search1, result1, … [default])

• 功能：Oracle特有的 IF-THEN-ELSE IF 逻辑。

SELECT department, DECODE(department, 'Sales', 'S', 'HR', 'H', 'Other') AS dept_code FROM employees;
-- 返回: (将部门名转换为代码)

6.4 CASE WHEN … END

• 功能：ANSI标准的条件表达式，更灵活。

SELECT salary, CASE WHEN salary > 10000 THEN 'High' ELSE 'Normal' END AS salary_level FROM employees;
-- 返回: (根据薪水是否大于10000，显示不同的等级)

练习题

背景表：employees

CREATE TABLE employees (employee_id     NUMBER(10) NOT NULL,full_name       VARCHAR2(100 CHAR) NOT NULL,job_title       VARCHAR2(100 CHAR) NOT NULL,department      VARCHAR2(50 CHAR) NOT NULL,salary          NUMBER(10, 2) NOT NULL,commission_pct  NUMBER(4, 2),hire_date       DATE NOT NULL,CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

题目：

1. 查询所有员工的全名，格式为 “名 姓” (例如, ‘John Doe’)，并且所有字母都为大写。
2. 查询所有员工入职至今的完整月数，结果四舍五入到整数。
3. 查询所有员工的姓氏 (即 full_name 中逗号前的部分)。
4. 查询所有员工的薪资等级。如果薪水大于10000，等级为’A’；如果在5000到10000之间 (含)，等级为’B’；否则为’C’。
5. 计算每个部门的员工总数和平均薪资。
6. 查询所有员工的总收入。总收入 = salary + (salary * commission_pct)。注意 commission_pct 可能为NULL，如果为NULL，则提成视为0。
7. 查询所有员工的入职日期，格式为 “YYYY年MM月DD日”。
8. 查询每个员工以及其所在部门中薪水次高的员工的薪水。如果该员工已经是薪水最高的，则显示NULL。
9. 查询所有员工的姓氏，并确保首字母大写，其余小写，同时去除可能存在的前后空格。
10. 查询每个员工入职当月的最后一天是星期几 (英文全称)。

答案与解析：

1. 查询并格式化全名：

SELECT UPPER(SUBSTR(full_name, INSTR(full_name, ',') + 2) || ' ' || SUBSTR(full_name, 1, INSTR(full_name, ',') - 1)) AS formatted_name
FROM employees;

• 解析： INSTR 找到逗号位置，SUBSTR 分别截取姓和名。|| 用于拼接字符串，UPPER 将结果转为大写。

2. 计算入职月数：

SELECT full_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date)) AS months_worked
FROM employees;

• 解析： MONTHS_BETWEEN 计算两个日期之间的月数 (返回小数)，ROUND 对结果进行四舍五入取整。

3. 提取姓氏：

SELECT SUBSTR(full_name, 1, INSTR(full_name, ',') - 1) AS last_name
FROM employees;

• 解析： INSTR 找到逗号的位置，SUBSTR 从第一个字符开始截取到逗号前一个位置。

4. 划分薪资等级：

SELECT full_name, salary,CASEWHEN salary > 10000 THEN 'A'WHEN salary BETWEEN 5000 AND 10000 THEN 'B'ELSE 'C'END AS salary_grade
FROM employees;

• 解析： 使用 CASE 语句进行多条件判断。BETWEEN ... AND ... 包含边界值。

5. 按部门聚合计算：

SELECT department, COUNT(*) AS number_of_employees, ROUND(AVG(salary), 2) AS average_salary
FROM employees
GROUP BY department;

• 解析： 使用 GROUP BY 按部门分组，COUNT(*) 计算每组的行数，AVG(salary) 计算每组的平均薪资。

6. 计算总收入 (处理NULL)：

SELECT full_name, salary + (salary * NVL(commission_pct, 0)) AS total_income
FROM employees;

• 解析： NVL(commission_pct, 0) 是关键。如果 commission_pct 为NULL，它会返回0，从而避免了整个计算表达式因NULL而变成NULL。

7. 格式化入职日期：

SELECT full_name, TO_CHAR(hire_date, 'YYYY"年"MM"月"DD"日"') AS formatted_hire_date
FROM employees;

• 解析： TO_CHAR 函数使用指定的格式模型将日期转换为字符串。双引号用于包含非格式化模型的文字。

8. 查询部门次高薪水 (LAG)：

SELECT full_name, department, salary,LAG(salary, 1, NULL) OVER (PARTITION BY department ORDER BY salary DESC) AS next_highest_salary
FROM employees;

• 解析： LAG(salary, 1, NULL) 访问按薪水降序排列后，每个部门窗口内的上一行 (即薪水次高) 的 salary 值。对于薪水最高的人，没有上一行，所以返回默认值 NULL。

9. 格式化姓氏：

SELECT INITCAP(TRIM(SUBSTR(full_name, 1, INSTR(full_name, ',') - 1))) AS cleaned_last_name
FROM employees;

• 解析： 组合使用函数。SUBSTR 和 INSTR 提取姓氏，TRIM 去除可能存在的空格，INITCAP 将其格式化为首字母大写。

10. 入职月最后一天是星期几：

SELECT full_name, hire_date, TO_CHAR(LAST_DAY(hire_date), 'Day') AS last_day_of_hire_month
FROM employees;

• 解析： LAST_DAY 找到入职月份的最后一天，然后 TO_CHAR 使用 ‘Day’ 格式模型将其转换为完整的星期几名称。