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);


摘自<>中對nullif的解釋
----------------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
------------------------- ----------
De Haan AD_VP
Hartstein MK_MAN
Kaufling ST_MAN
Kochhar AD_VP
Kochhar AD_VP
Raphaely PU_MAN
Taylor SA_REP
Taylor
Whalen AD_ASST
Whalen
----------------quote begin-------------------------------------------------

上述有誤, 應該是“New Job ID”
expr1,expr2除了numeric datatype以外,為其它型別時要求一致,否則會報錯



注：在mysql中nullif的函式與oracle中nullif一致