Oracle 常用函数

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

思维导图

一、字符函数

1.1 UPPER(string)
功能：将字符串转换为大写。
sql SELECT UPPER('Hello Oracle') FROM dual; -- 返回: 'HELLO ORACLE'

1.2 LOWER(string) 功能：将字符串转换为 小写。
sql SELECT LOWER('Hello Oracle') FROM dual; -- 返回: 'hello oracle'

1.3 INITCAP(string)
功能：将字符串中每个单词的首字母大写。
sql SELECT INITCAP('hello oracle world') FROM dual; -- 返回: 'Hello Oracle World'

1.4 LENGTH(string) 功能：返回字符串的 字符长度。
sql SELECT LENGTH('Oracle SQL') FROM dual; -- 返回: 10

1.5 INSTR(string, substring, [start_position], [nth_appearance])
功能：返回子字符串在字符串中的位置。
sql SELECT INSTR('oracle sql is cool sql', 'sql', 1, 2) FROM dual; -- 返回: 21 (从第1个字符开始查找，第2次出现的'sql'的位置)

1.6 SUBSTR(string, start_position, [length]) 功能：从 指定位置开始 截取子字符串。
sql SELECT SUBSTR('Oracle Database', 8, 8) FROM dual; -- 返回: 'Database' (从第8个字符开始，截取8个字符)

1.7 REPLACE(string, search_string, [replacement_string])
功能：替换字符串中所有出现的子字符串。
sql SELECT REPLACE('black cat and blue cat', 'cat', 'dog') FROM dual; -- 返回: 'black dog and blue dog'

1.8 CONCAT(string1, string2) 功能： 连接两个字符串。更常用的是 || 操作符。
sql SELECT CONCAT('Hello', ' World') FROM dual; -- 返回: 'Hello World'
sql SELECT 'Oracle' || ' ' || 'SQL' FROM dual; -- 返回: 'Oracle SQL'

1.9 LPAD(string, length, [pad_string])
功能：左侧填充字符到指定长度。
sql SELECT LPAD('123', 5, '0') FROM dual; -- 返回: '00123'

1.10 RPAD(string, length, [pad_string]) 功能： 右侧填充字符到指定长度。
sql SELECT RPAD('abc', 5, '*') FROM dual; -- 返回: 'abc**'

1.11 TRIM(string)
功能：去除字符串两边的空格。
sql SELECT TRIM(' Oracle ') FROM dual; -- 返回: 'Oracle'

1.12 LTRIM(string, [set]) 功能：去除字符串 左侧的指定字符集。
sql SELECT LTRIM('$$$100', '$') FROM dual; -- 返回: '100'

1.13 RTRIM(string, [set])
功能：去除字符串右侧的指定字符集。
sql SELECT RTRIM('abc##', '#') FROM dual; -- 返回: 'abc'

### *二、数值函数 2.1 ROUND(number, [decimal_places])
功能：对数字进行四舍五入。
sql SELECT ROUND(123.456, 2) FROM dual; -- 返回: 123.46

2.2 TRUNC(number, [decimal_places]) 功能：对数字进行 截断。
sql SELECT TRUNC(123.456, 2) FROM dual; -- 返回: 123.45

2.3 CEIL(number)
功能：返回大于或等于该数字的最小整数 (向上取整)。
sql SELECT CEIL(99.1) FROM dual; -- 返回: 100

2.4 FLOOR(number) 功能：返回 小于或等于该数字的 最大整数 (向下取整)。
sql SELECT FLOOR(99.9) FROM dual; -- 返回: 99

2.5 MOD(m, n)
功能：返回 m 除以 n 的余数。
sql SELECT MOD(10, 3) FROM dual; -- 返回: 1

2.6 ABS(number) 功能：返回数字的 绝对值。
sql SELECT ABS(-123) FROM dual; -- 返回: 123

### 三、日期函数 3.1 SYSDATE
功能：返回当前数据库服务器的日期和时间。
sql SELECT SYSDATE FROM dual; -- 返回: (当前日期和时间，例如 2024-03-22 10:30:00)

3.2 SYSTIMESTAMP 功能：返回 当前数据库服务器的 日期、时间，并包含 小数秒和时区。
sql SELECT SYSTIMESTAMP FROM dual; -- 返回: (当前日期时间+小数秒+时区，例如 22-MAR-24 10.30.00.123456 AM +08:00)

3.3 ADD_MONTHS(date, integer)
功能：增加或减少指定的月份数。
sql SELECT ADD_MONTHS(TO_DATE('2024-01-31', 'YYYY-MM-DD'), 1) FROM dual; -- 返回: 29-FEB-24 (会自动处理月末日期)

3.4 MONTHS_BETWEEN(date1, date2) 功能：返回两个日期之间的 月份数。
sql 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)
功能：返回指定日期所在月份的最后一天。
sql 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') 功能：返回指定日期之后 第一个指定星期几的日期。
sql 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])
功能：按指定格式截断日期。
sql SELECT TRUNC(SYSDATE, 'MM') FROM dual; -- 返回: (当月的第一天，例如 01-MAR-24)

3.8 EXTRACT(unit FROM date) 功能：从日期中 提取特定部分。
sql SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual; -- 返回: (当前年份，例如 2024)

### 四、转换函数 4.1 TO_CHAR(date/number, [format_model])
功能：将日期或数字转换为指定格式的字符串。
sql SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual; -- 返回: '2024-03-22 10:30:00' (示例)
sql SELECT TO_CHAR(12345.67, 'FM99G999D00') FROM dual; -- 返回: '12,345.67'

4.2 TO_DATE(string, [format_model]) 功能：将 符合特定格式的字符串转换为 日期类型。
sql SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM dual; -- 返回: 15-JAN-24 (日期类型)

4.3 TO_NUMBER(string, [format_model])
功能：将字符串转换为数字类型。
sql SELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual; -- 返回: 1234.56 (数字类型)

### 五、聚合函数
(通常与 GROUP BY 配合使用，此处为简化，对全表操作)

**5.1 COUNT() / COUNT(column) / COUNT(DISTINCT column)
功能：计算行数。
```sql
-- 假设 employees 表有10条记录, 其中 commission_pct 有3个非空值，2种不同的非空值
SELECT COUNT(), COUNT(commission_pct), COUNT(DISTINCT commission_pct) FROM employees;
-- 返回: 10, 3, 2
```

5.2 SUM(expression)
* 功能：计算总和。
sql SELECT SUM(salary) FROM employees; -- 返回: (所有员工薪水总和)

5.3 AVG(expression)
* 功能：计算平均值。
sql SELECT AVG(salary) FROM employees; -- 返回: (所有员工薪水平均值)

5.4 MAX(expression)
* 功能：找出最大值。
sql SELECT MAX(salary) FROM employees; -- 返回: (最高薪水)

5.5 MIN(expression)
* 功能：找出最小值。
sql SELECT MIN(salary) FROM employees; -- 返回: (最低薪水)
### 六、通用/其他函数** 6.1 NVL(expr1, expr2)
功能：如果 expr1 不为NULL，返回 expr1；否则返回 expr2。
sql SELECT NVL(commission_pct, 0) FROM employees; -- 返回: (如果commission_pct是NULL，则显示0，否则显示其本身的值)

6.2 NVL2(expr1, expr2, expr3) 功能：如果 expr1 不为NULL，返回 expr2；否则返回 expr3。
sql 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 逻辑。
sql SELECT department, DECODE(department, 'Sales', 'S', 'HR', 'H', 'Other') AS dept_code FROM employees; -- 返回: (将部门名转换为代码)

6.4 CASE WHEN ... END 功能： ANSI标准的条件表达式， 更灵活。
sql 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,
      CASE
        WHEN 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 函数使用指定的格式模型将日期转换为字符串。双引号用于包含非格式化模型的文字。

1. 查询部门次高薪水 (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。

2. 格式化姓氏：

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

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

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

   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' 格式模型将其转换为完整的星期几名称。