與Null打交道
Nvl(p1,p2)
if p2 is null then
return p2
else
return p1
end if
Nvl2(p1,p2,p3)
if p1 is null then
return p3
else
return p2
end if
Decode(p1,null,p2,p3)
if p1 is null then
return p2
else
return p3
end if
Coalesce(p1,p2,p3,p4....)
if p1 is not null then
return p1
elsif p2 is not null then
return p2
elsif p3 is not null then
return p3
elsif p4 is not null then
return p4
.....
NullIf(p1,p2)
if p1 = p2 then
return null
else
return p1
end if;
Case
select
cast
when p1 is null then
'null'
else
'not null'
end case
from dual
cast
when p1 is null then
'null'
else
'not null'
end case
from dual
總結 : 與null有關的函數不少,端看需求如何
沒有留言:
張貼留言