# nvl, nvl2, nullif

NVL,NVL2,NULLIF三个函数的含义

NULL指的是空值，或者非法值。
NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

nvl2函数的例子

SELECT NVL2(partition_name,segment_name || ':' || partition_name,segment_name)
FROM user_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION')
and segment_name NOT IN (SELECT object_name FROM recyclebin bin);

----------------quote begin-------------------------------------------------
Purpose
NULLIF compares expr1 and expr2. If they are equal, then the function returns
null. If they are not equal, then the function returns expr1. You cannot specify the
literal NULL for expr1.
If both arguments are numeric datatypes, then Oracle Database determines the
argument with the higher numeric precedence, implicitly converts the other
argument to that datatype, and returns that datatype. If the arguments are not
numeric, then they must be of the same datatype, or Oracle returns an error.
The NULLIF function is logically equivalent to the following CASE expression:

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

Examples
The following example selects those employees from the sample schema hr who
have changed jobs since they were hired, as indicated by a job_id in the job_
history table different from the current job_id in the employees table:

SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

LAST_NAME Old Job ID
------------------------- ----------
Hartstein MK_MAN
Kaufling ST_MAN
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen
----------------quote begin-------------------------------------------------

expr1,expr2除了numeric datatype以外,为其它类型时要求一致,否则会报错

• 博文量
136
• 访问量
386994