Oracle通用函数

开发工具与关键技术：Oracle
作者：邹铭霞
撰写时间：2019-04-16

这些函数适用于任何数据类型，同时也适用于空值：
 NVL (expr1, expr2)
 NVL2 (expr1, expr2, expr3)
 NULLIF (expr1, expr2)
 COALESCE (expr1, expr2, …, exprn)

NVL函数
将空值转换成一个已知的值：利用NVL函数对空值进行处理

例如：求公司员工的年薪

SELECT last_name, salary, NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) as 年薪
FROM employees;

如果null值参与运算，结果也是null，而NVL函数就可以解决这个问题，当null值参与运算的时候，NVL函数就进行空值处理，可以将它归为零（也可以是其他数值）

NVL2函数
当expr1（有值）不为空的时候返回expr2，
当expr1（没有值）为空的时候返回expr3
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3

例如：查询员工的奖金率（commission_pct），若为空，返回0.01,
若不为空，返回实际奖金率+0.015

select commission_pct,NVL2(commission_pct,commission_pct+0.015,0.01)
FROM EMPLOYEES;

NULLIF 函数
NULLIF (expr1, expr2) : expr1和expr2两者的值相等的时候就返回NULL，不相等返回expr1

SELECT first_name, LENGTH(first_name) "expr1", 
        last_name,  LENGTH(last_name)  "expr2",
        NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM   employees;

COALESCE函数
传入的参数，从第一个开始判断，当第一个参数为空的时候就返回第二个，第二个为空就返回第三个，以此类推。当参数有值的时候，后面不管有没有参数，就返回当前
select coalesce (null,‘b’,‘c’)
FROM employees;
返回b

条件表达式
• 在 SQL 语句中使用IF-THEN-ELSE 逻辑
• 使用两种方法:
– CASE 表达式
– DECODE 函数

练习：查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

第一种写法：
select employee_id,last_name,salary,department_id
，case department_id 
       when 10 then salary*1.1
         when 20 then salary*1.2
           when 30 then salary*1.3
  else salary    
         end
FROM employees
--where department_id in (10,20,30);
第二种写法：
select employee_id,last_name,salary,department_id
，case when department_id = 10 then salary*1.1
        when department_id = 20 then salary*1.2
        when department_id = 30 then salary*1.3
       else salary 
         end
FROM employees
where department_id in (10,20,30);