Oracle数据库常用函数总结大全_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)
);

题目：

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

答案与解析：

查询并格式化全名：

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 将结果转为大写。

计算入职月数：

select full_name, round(months_between(sysdate, hire_date)) as months_worked
from employees;

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

提取姓氏：

select substr(full_name, 1, instr(full_name, ',') - 1) as last_name
from employees;

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

划分薪资等级：

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 ... 包含边界值。

按部门聚合计算：

select department, count(*) as number_of_employees, round(avg(salary), 2) as average_salary
from employees
group by department;

解析：使用 group by 按部门分组，count(*) 计算每组的行数，avg(salary) 计算每组的平均薪资。

计算总收入 (处理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。

格式化入职日期：

select full_name, to_char(hire_date, 'yyyy"年"mm"月"dd"日"') as formatted_hire_date
from employees;

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

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

格式化姓氏：

select initcap(trim(substr(full_name, 1, instr(full_name, ',') - 1))) as cleaned_last_name
from employees;

解析：组合使用函数。substr 和 instr 提取姓氏，trim 去除可能存在的空格，initcap 将其格式化为首字母大写。

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

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

总结

到此这篇关于oracle数据库常用函数的文章就介绍到这了,更多相关oracle常用函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

Oracle数据库常用函数总结大全

前言