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，不等返回expr1nvl2函数的例子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-------------------------------------------------PurposeNULLIF compares expr1 and expr2. If they are equal, then the function returnsnull. If they are not equal, then the function returns expr1. You cannot specify theliteral NULL for expr1.If both arguments are numeric datatypes, then Oracle Database determines theargument with the higher numeric precedence, implicitly converts the otherargument to that datatype, and returns that datatype. If the arguments are notnumeric, 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 ENDExamplesThe following example selects those employees from the sample schema hr whohave 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_VPHartstein MK_MANKaufling ST_MANKochhar AD_VPKochhar AD_VPRaphaely PU_MANTaylor SA_REPTaylorWhalen AD_ASSTWhalen----------------quote begin-------------------------------------------------上述有误, 应该是“New Job ID”expr1,expr2除了numeric datatype以外,为其它类型时要求一致,否则会报错注：在mysql中nullif的函数与oracle中nullif一致

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-600291/，如需转载，请注明出处，否则将追究法律责任。