ITPub博客

首页 > Linux操作系统 > Linux操作系统 > nvl, nvl2, nullif

nvl, nvl2, nullif

原创 Linux操作系统 作者:tengrid 时间:2009-05-18 19:12:04 0 删除 编辑

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一致

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-18

  • 博文量
    136
  • 访问量
    378050