Oracle 常用函数_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_nameFROM employees;

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

2. 计算入职月数：

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

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

3. 提取姓氏：

SELECT SUBSTR(full_name, 1, INSTR(full_name, \',\') - 1) AS last_nameFROM employees;

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

4. 划分薪资等级：

SELECT full_name, salary, CASE WHEN salary > 10000 THEN \'A\' WHEN salary BETWEEN 5000 AND 10000 THEN \'B\' ELSE \'C\' END AS salary_gradeFROM employees;

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

5. 按部门聚合计算：

SELECT department, COUNT(*) AS number_of_employees, ROUND(AVG(salary), 2) AS average_salaryFROM employeesGROUP BY department;

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

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

SELECT full_name, salary + (salary * NVL(commission_pct, 0)) AS total_incomeFROM 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_dateFROM 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_salaryFROM employees;

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

9. 格式化姓氏：

SELECT INITCAP(TRIM(SUBSTR(full_name, 1, INSTR(full_name, \',\') - 1))) AS cleaned_last_nameFROM employees;

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

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

SELECT full_name, hire_date, TO_CHAR(LAST_DAY(hire_date), \'Day\') AS last_day_of_hire_monthFROM employees;

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