NVL & NVL2 & NULLIF

最新推荐文章于 2019-02-24 19:41:13 发布

congqiaole2553

最新推荐文章于 2019-02-24 19:41:13 发布

阅读量112

点赞数

NVL & NVL2 & NULLIF

NULLIF (expr1, expr2) --&gt相等返回NULL，不等则返回expr1

SQL> SELECT nullif('abc','abc') FROM dual;

NULLIF('ABC','ABC')
-------------------

SQL> SELECT nullif('abc','abcd') FROM dual ;

NULLIF('ABC','ABCD')
--------------------
abc

SQL> SELECT nullif('1',1) FROM dual; -- 注意类型要一致

SELECT nullif('1',1) FROM dual

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

===============================================

NVL2 (expr1, expr2, expr3) --&gtexpr1不为NULL，返回expr2；为NULL，则返回expr3
SQL> SELECT nvl2('abc','abcd','abcde') FROM dual;

NVL2('ABC','ABCD','ABCDE')
--------------------------
abcd

SQL> SELECT nvl2('','abcd','abcde') FROM dual;

NVL2('','ABCD','ABCDE')
-----------------------
abcde

SQL> SELECT nvl2('',1,'2.0000') FROM dual;

NVL2('',1,'2.0000')
-------------------
2