oracle常用的函数（二） 之 nvl、decode、length...

戳这里 → oracle常用的函数（一） 之 to_char、to_date

nvl

NVL 函数用于替换 NULL 值：如果第一个参数为 NULL，则返回第二个参数的值；否则返回第一个参数的值。

语法

NVL(expr1, expr2)

• expr1：需要检查是否为null的表达式
• expr2：如果expr1为null，则返回的值

注意：
（1）nvl函数只能处理两个参数，如果需要处理多个null值，可以嵌套使用nvl函数
（2）nvl函数的两个参数必须是相同的数据类型，或者可以隐式转换为相同的数据类型
（3）如果 expr1不为NULL，则 NVL 函数返回 expr1，不会考虑expr2的值

替换null为默认值

如果某些员工的工资为 NULL，则 NVL(salary, 0) 会将这些 NULL 值替换为 0

SELECT employee_id, name, NVL(salary, 0) AS salary
FROM employees;

在计算中使用nvl

SELECT sale_id, product_name, price * (1 - NVL(discount, 0)) AS final_price
FROM sales;

如果某些销售记录的折扣为 NULL，则 NVL(discount, 0) 会将这些 NULL 值替换为 0，从而确保计算不会出错

NVL2

类似于 NVL，但可以根据第一个参数是否为 NULL 返回不同的值

语法

NVL2(expr1, expr2, expr3)

示例语句

计算员工的佣金。如果 commission_pct 不为 NULL，则返回 commission_pct * salary；否则返回 0

SELECT NVL2(commission_pct, commission_pct * salary, 0) AS commission
FROM employees;

NVL 和 NVL2 总结

• 1、都是用于处理空值（NULL）的函数
• 2、nvl用于将null值替换为指定的默认值；nvl2根据null值返回两个不同值中的一个
• 3、nvl有两个参数；nvl2有三个参数
• 4、在处理大数据量时，nvl通常比nvl2更快。因为nvl只需要处理两个参数，而nvl2需要处理三个参数，会增加额外的计算开销
• 5、nvl和nvl2会导致索引。因为这些函数改变了查询的逻辑结果

COALESCE

类似于 NVL，但可以接受多个参数，返回第一个非 NULL 的值。

语法

COALESCE(expr1, expr2, ..., exprN)

COALESCE 函数从左到右检查这些表达式，返回第一个非空值：

• 如果第一个表达式 expr1 不为 NULL，则返回 expr1 的值
• 如果 expr1 为 NULL，则检查 expr2，返回 expr2 的值（如果 expr2 不为 NULL）
• 依次类推，直到找到第一个非空值
• 如果所有表达式都为 NULL，则返回 NULL

示例语句

假设我们有一个 employees 表，其中包含员工的 phone_number、mobile_number 和 home_phone 列。我们希望显示每个员工的联系电话，优先顺序为 phone_number > mobile_number > home_phone

SELECT employee_id, first_name, last_name, COALESCE(phone_number, mobile_number, home_phone) AS contact_number
FROM employees;

如果phone_number不为空，返回phone_number；
如果phone_number为空，mobile_number不为空，返回mobile_number；
如果phone_number、mobile_number为空，home_phone不为空，返回home_phone
如果phone_number、mobile_number、home_phone均为空，返回null

注意事项

1、所有参数必须是兼容的类型，如果类型不兼容，Oracle 会尝试进行隐式类型转换。如果无法转换，会抛出错误。
2、在处理大数据量时可能会对性能产生一定影响

DECODE

类似于其他语言中的 CASE 语句，用于根据条件返回不同的值

语法

DECODE(value, if1, then1, if2, then2, ..., else)

示例语句

如果 department_id 是 10，则返回 ‘Accounting’；
如果是 20，则返回 ‘Marketing’；
否则返回 ‘Other’

SELECT DECODE(department_id, 10, 'Accounting', 20, 'Marketing', 'Other') AS department
FROM employees;

SUBSTR

用于从字符串中提取子字符串。

语法

substr(string,start,length)

示例语句

提取first_name的前3个字符

SELECT SUBSTR(first_name, 1, 3) AS initials FROM employees;

！！！注意：
（1）在oracle中，索引不是从0开始的，而是从1开始的
（2）length是可选参数，如果不指定，substr会从起始位置开始提取到字符串的末尾

LENGTH

该函数用于计算字符串的长度。

语法

LENGTH(string)

示例语句

返回 name 的长度

SELECT LENGTH(name) AS name_length  FROM employees;

TRIM

用于去除字符串两端的空格或指定字符。

语法

TRIM([LEADING | TRAILING | BOTH] [char] FROM string)

参数说明：
leading：从字符串的开头删除指定的字符
trailing：从字符串的结尾删除指定的字符
both：从字符串的开头和结尾删除指定的字符。如果未指定，默认为both
char：要删除的字符。如果不指定，默认删除空格字符串
string：要处理的的源字符串

示例语句

1、删除字符串两端的空格 或 指定字符

SELECT TRIM('   Hello World   ') AS trimmed_string  FROM dual;
-- 结果：Hello World

2、删除字符串开头的空格 或 指定字符

SELECT TRIM(LEADING FROM '   Hello World   ') AS trimmed_string  FROM dual;
-- 结果：Hello World   

SELECT TRIM(LEADING '!' FROM '！！！Hello World！！！') AS trimmed_string  FROM dual;
-- 结果：Hello World！！！

3、删除字符串结尾的空格 或 指定字符

SELECT TRIM(TRAILING FROM '   Hello World   ') AS trimmed_string  FROM dual;
-- 结果：   Hello World

SELECT TRIM(TRAILING '!' FROM '！！！Hello World！！！') AS trimmed_string  FROM dual;
-- 结果：！！！Hello World

4、删除字符串两端的空格 或 指定字符

SELECT TRIM(BOTH FROM '   Hello World   ') AS trimmed_string  FROM dual;
-- 结果：Hello World（此时BOTH FROM 可以省略）

SELECT TRIM(BOTH '!' FROM '！！！Hello World！！！') AS trimmed_string  FROM dual;
-- 结果：Hello World

注意事项

1、如果未指定 LEADING、TRAILING 或 BOTH，默认为 BOTH
2、TRIM 函数在处理大数据量时可能会对性能产生一定影响，特别是当处理的字符串非常长时

REPLACE

用于在字符串中替换指定的子字符串。

语法

REPLACE(string, search_string, replace_string)

示例语句

将 email 中的 @ 替换为 #

SELECT REPLACE(email, '@', '#') AS modified_email
FROM employees;

ROUND

用于对数字进行四舍五入。

语法

ROUND(number, decimal_places)

示例语句

将 salary 四舍五入到小数点后两位

SELECT ROUND(salary, 2) AS rounded_salary  FROM employees;

ADD_MONTHS

用于在日期上加上指定的月数。

语法：

ADD_MONTHS(date, months)

示例语句

将 hire_date 加上 12 个月来计算员工的入职纪念日

SELECT ADD_MONTHS(hire_date, 12) AS anniversary_date
FROM employees;

MONTHS_BETWEEN

用于计算两个日期之间的月数差

语法

MONTHS_BETWEEN(date1, date2)

示例语句

计算从 hire_date 到当前日期的月数差

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

NULLIF

用于比较两个表达式，如果它们相等，则返回 NULL；否则返回第一个表达式的值。

该函数在处理数据时非常有用，尤其是在需要避免除以零或处理重复值的时候

语法

NULLIF(expr1, expr2)

expr1：第一个表达式；
expr2：第二个表达式
1、如果expr1和expr2相等，则返回null；
2、如果expr1和expr2不相等，则返回expr1的值

示例语句

1、避免除以零

假设我们有一个表employee，其中包含员工的salary和commissio_pct列。我们希望计算每个员工的佣金金额，但是需要避免除以零

select employee_id,salary,commission_pct,NVL(salary*NULLIF(commission_pct,0),100) as commission_amount from employees;

• 示例数据

  employee_id	salary	commission_pct
  1	5000	0.1
  1	6000	0
  1	7000	0.2

• 语句解释
  （1）对于 employee_id 为 1 的员工，commission_pct 为 0.1，计算结果为 5000 * 0.1 = 500.0
  （2）对于 employee_id 为 2 的员工，commission_pct 为 0，NULLIF(commission_pct, 0) 返回 NULL，因此 salary * NULLIF(commission_pct, 0) 也为 NULL。使用 NVL 将其替换为 100
  （3）对于 employee_id 为 3 的员工，commission_pct 为 0.2，计算结果为 7000 * 0.2 = 1400.0

• 最终结果

  employee_id	salary	commission_pct	commission_amount
  1	5000	0.1	500.0
  2	6000	0	100.0
  3	7000	0.2	1400.0

2、处理重复值

假设我们有一个表 products，其中包含产品的 price 和 discount 列。我们希望计算每个产品的折扣价格，但需要避免重复值的情况

SELECT product_id, price, discount, price * NULLIF(discount, price) AS discounted_price
FROM products;

• 示例数据

  product_id	price	discount
  1	100	20
  2	200	200
  3	150	30

• 语句解释
  （1）对于 product_id 为 1 的产品，discount 为 20，计算结果为 100 * 20 = 2000.0
  （2）对于 product_id 为 2 的产品，discount 为 200，与 price 相等，NULLIF(discount, price) 返回 NULL，因此 price * NULLIF(discount, price) 也为 NULL
  （3）对于 product_id 为 3 的产品，discount 为 30，计算结果为 150 * 30 = 4500.0

• 最终结果

  product_id	price	discount	discounted_price
  1	100	20	2000.0
  2	200	200	NULL
  3	150	30	4500.0

ROWNUM

ROWNUM 是一个伪列（pseudo-column），用于生成一个唯一的序号，通常用于限制查询结果的数量或对结果集进行编号。ROWNUM 的值从 1 开始，为每一行生成一个唯一的序号

功能

1、限制查询结果的数量：通过 ROWNUM 可以限制查询返回的行数。
2、对结果集进行编号：ROWNUM 可以为每一行生成一个唯一的序号。

示例语句

假设我们有一个 employees 表，我们希望查询前 5 名员工的信息

SELECT employee_id, first_name, ROWNUM FROM employees
WHERE ROWNUM <= 5;

注意事项

1、ROWNUM 的值是在查询结果生成时立即分配的，因此它不依赖于排序顺序。如果需要按特定顺序编号，应先对结果进行排序，然后再使用 ROWNUM
2、使用 ROWNUM 限制查询结果的数量时，必须将其放在 WHERE 子句中
3、ROWNUM 通常用于限制查询结果的数量，因此对性能的影响较小