Oracle中nvl()与nvl2()函数详解:
【函数格式】:nvl(expression1,expression2)
【函数说明】:
【特别说明】:参数expression1和expression2可以是字符型、数字型或日期型,但参数expression1与expression2的类型必须一致。
expression2为字符型:
select nvl(null,"ABCD") from dual; --返回:ABCD select nvl(null,"abcd") from dual; --返回:abcd select nvl(null,"12345") from dual; --返回:12345
expression2为数值型:
select nvl(null,12345) from dual; --返回:12345 select nvl(null,00000) from dual; --返回:0 select nvl(null,-12345) from dual; --返回:-12345 select nvl(null,123.45) from dual; --返回:123.45 select nvl(null,-123.45) from dual; --返回:-123.45
expression2为日期时间型:
select nvl(null,sysdate) from dual; --返回:2022/2/25 11:54:18 select nvl(null,to_date("2022/2/25 11:54:18","yyyy-mm-dd hh24:mi:ss")) from dual; --返回:2022/2/25 11:54:18 select nvl(null,to_date("2022/2/25","yyyy-mm-dd")) from dual; --返回:2022/2/25 select nvl(null,to_date("2022","yyyy")) from dual; --返回:2022/2/1
expression1为字符型:
select nvl("ABCD","abcd") from dual; --返回:ABCD select nvl("abcd","ABCD") from dual; --返回:abcd select nvl("12345","54321") from dual; --返回:12345
expression1为数值型:
select nvl(12345,null) from dual; --返回:12345 select nvl(00000,11111) from dual; --返回:0 select nvl(-12345,null) from dual; --返回:-12345 select nvl(123.45,-123.45) from dual; --返回:123.45 select nvl(-123.45,123.45) from dual; --返回:-123.45
expression1为日期时间型:
select nvl(sysdate,null) from dual; --返回:2022/2/25 12:18:23 select nvl(to_date("2022/2/25 12:18:23","yyyy-mm-dd hh24:mi:ss"),null) from dual; --返回:2022/2/25 12:18:23 select nvl(to_date("2022/2/25","yyyy-mm-dd"),null) from dual; --返回:2022/2/25 select nvl(to_date("2022","yyyy"),null) from dual; --返回:2022/2/1
select nvl(null,null) from dual; --返回空值 select nvl("",null) from dual; --返回空值 select nvl(null,"") from dual; --返回空值 select nvl("","") from dual; --返回空值
【函数格式】:nvl2(expression1,expression2,expression3)
【函数说明】:
【特别说明】:
expression2和expression3均为字符型:
select nvl2(0,"ABCD","abcd") from dual; --返回:ABCD select nvl2("a","ABCD","1234") from dual; --返回:ABCD select nvl2(sysdate,"1234","abcd") from dual; --返回:1234
expression2和expression3均为数值型:
select nvl2(0,12345,54321) from dual; --返回:12345 select nvl2("a",123.45,543.21) from dual; --返回:123.45 select nvl2(sysdate,-12345,-54321) from dual; --返回:-12345
expression2和expression3数据类型不一致:
注意:此时expression1有值,因此该函数直接返回expression2的值,即使expression3的类型无法转换为expression2的类型也不会报错。
select nvl2(0,"ABCD",54321) from dual; --返回:ABCD select nvl2("a","abcd",543.21) from dual; --返回:abcd select nvl2(sysdate,"12345",-54321) from dual; --返回:12345
expression2和expression3均为字符型:
select nvl2(null,"ABCD","abcd") from dual; --返回:abcd select nvl2("","ABCD","1234") from dual; --返回:1234
expression2和expression3均为数值型:
select nvl2(null,12345,54321) from dual; --返回:54321 select nvl2("",123.45,543.21) from dual; --返回:543.21
expression2和expression3数据类型不一致:
注意:此时expression1值为null,因此该函数会返回expression3的值,若expression3的类型无法转换为expression2的类型,则会报错。
select nvl2(null,"ABCD",54321) from dual; --返回:54321 select nvl2("","abcd",543.21) from dual; --返回:543.21 select nvl2("",543.21,"abcd") from dual; --执行报错 select nvl2(null,"abcd",sysdate) from dual; --返回:25-2月 -22 select nvl2(null,sysdate,"abcd") from dual; --执行报错
select nvl2(null,null,"123") from dual; --返回:123 select nvl2(null,"abc",null) from dual; --返回:空值 select nvl2(null,null,null) from dual; --返回:空值 select nvl2("","","") from dual; --返回:空值
到此这篇关于Oracle中nvl()和nvl2()函数详解的文章就介绍到这了,更多相关Oracle nvl()和nvl2()函数内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!