ORACLE函数之空值

NVL、NVL2、NULLIF、COALESCE
NVL
SELECT employee_id,salary,commission_pct,salary+salarycommission_pct FROM employees;
SELECT employee_id,salary,commission_pct,salary+salaryNVL(commission_pct,0) FROM employees;


nvl(exp1,exp2) exp1为空值输出exp2 非空输出原值
SELECT employee_id,salary,commission_pct,salary+salary*NVL(commission_pct,‘ABC’) FROM employees;注意参与运算时一定要让参数个数相同，不然会报错

NVL2
SELECT employee_id,salary,commission_pct,salary+salary*NVL2(commission_pct,‘sal+comm’,‘sal’) FROM employees;错误

SELECT employee_id,salary,commission_pct,NVL2(commission_pct,‘sal+comm’,‘sal’) FROM employees;

nvl2(exp1,exp2，exp3)如果exp1为空值输出exp3，若为非空，输出exp2

NULLIF
SELECT length(first_name),length(last_name),nullif(length(first_name),length(last_name)) “Res” FROM employees;

nullif(exp1,exp2)exp1等于exp2时为空值 否则输出exp1

COALESCE
SELECT first_name,commission_pct,manager_id,COALESCE(commission_pct,manager_id,‘NO commission and no manager’) FROM employees;错误格式不统一
SELECT first_name,commission_pct,manager_id,COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),‘NO commission and no manager’) FROM employees；需要to_char转化一次

COALESCE（exp1,exp2,…expn）如果exp1为空 则继续看exp2， 直到不为空输出结果