nvl nvl2注意点及区别

点击(此处)折叠或打开

1. NVL2(expr1,expr2,expr3)
   
2. 
   
3. 　　功能：如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。
   
4.    expr2 和 expr3的类型必须保持一致，expr1的类型不用管。
5. 
   
6. NVL( string1, replace_with)
   
7. 
   
8. 　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
9.     string1和replace_with的类型必须保持一致
10.    

NVL2测试：

点击(此处)折叠或打开

1. SQL> select nvl2(sysdate,'sysdate+1','dfdf') from dual;
   
2. 
   
3. NVL2(SYSD
   
4. ---------
   
5. sysdate+1
   
6. 
   
7. SQL> select nvl2(sysdate,sysdate+1,'dfdf') from dual;
   
8. select nvl2(sysdate,sysdate+1,'dfdf') from dual
   
9.                               *
   
10. ERROR at line 1:
    
11. ORA-01858: a non-numeric character was found where a numeric was expected

NVL1测试：

点击(此处)折叠或打开

1. SQL> select nvl(sysdate,sysdate+1) from dual;
   
2. 
   
3. NVL(SYSDATE,
   
4. ------------
   
5. 04-AUG-16
   
6. 
   
7. SQL> select nvl(sysdate,'sysdate+1') from dual;
   
8. select nvl(sysdate,'sysdate+1') from dual
   
9.                    *
   
10. ERROR at line 1:
    
11. ORA-01858: a non-numeric character was found where a numeric was expected
    
12. 
    
13. 
    
14. SQL> select nvl(111,'sysdate+1') from dual;
    
15. select nvl(111,'sysdate+1') from dual
    
16.                *
    
17. ERROR at line 1:
    
18. ORA-01722: invalid number
    
19. 
    
20. 
    
21. SQL> select nvl('dfdf','sysdate+1') from dual;
    
22. 
    
23. NVL(
    
24. ----
    
25. dfdf

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

转载于:http://blog.itpub.net/28551528/viewspace-2122987/