Oracle 函数对null,空值的处理
Token
Code
/**//*
NVL2( string1, value_if_NOT_null, value_if_null )
RETURN Y*/
select nvl2('','N','Y') from dual;
/**//*
*Decdoe
*
*/
select decode ('Test','value1','1') from dual;
select nvl2(decode ('Test','value1','1'),'N','Y') from dual; -- NULL Y
select nvl2(decode ('Test','Test',''),'N','Y') from dual; -- NULL Y
select nvl2(decode ('Test','Test',' '),'N','Y') from dual; -- NOT NULL N
select nvl2(decode ('','',' '),'N','Y') from dual; -- NOT NULL N -- Decode can handle null value !!
/**//*
*sum,max,min count ,avg -- same behavior for other DB?
*
*/
select sum(score)
from (select 1 score
from dual
union
select 2 score
from dual
union
select 3 score from dual) ; -- 6
select sum(score)
from (select 1 score
from dual
union
select 2 score
from dual
union
select '' score from dual); -- error 01790 boz -- error is coming from union part
-- compare as varchar2
select min(score), --1
max(score) --2
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual); -- ignore null value -- same for varchar2 and date data type . but for number ,we 'd better to_number .see below
--compare as number
select min(to_number(score)), --1
max(to_number(score)) --2
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual); -- so to_number('') will return null -- as min max sum avg will ingnore null value
select avg(to_number(score)), --1.5=(1+2)/2) -- here 2
avg(nvl(to_number(score), 0)), -- 1= (1+2+0)/3
sum(to_number(score)) -- 3
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual);
/**//*
So from here we can find that:
1. oracle aggregate function will erase the record which has null value
2 That is why above avg function return 1.5 as (1.5=(1+2)/2)) .only 2 records will consider here
*/
select count(score), --2
count(to_number(score)), --2
count(nvl(score, ' ')), --3
count(nvl(score, '')), -- 2
count(nvl2(score, '', '')) -- 0
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual);
/**//*
NVL2( string1, value_if_NOT_null, value_if_null )
RETURN Y*/
select nvl2('','N','Y') from dual;
/**//*
*Decdoe
*
*/
select decode ('Test','value1','1') from dual;
select nvl2(decode ('Test','value1','1'),'N','Y') from dual; -- NULL Y
select nvl2(decode ('Test','Test',''),'N','Y') from dual; -- NULL Y
select nvl2(decode ('Test','Test',' '),'N','Y') from dual; -- NOT NULL N
select nvl2(decode ('','',' '),'N','Y') from dual; -- NOT NULL N -- Decode can handle null value !!
/**//*
*sum,max,min count ,avg -- same behavior for other DB?
*
*/
select sum(score)
from (select 1 score
from dual
union
select 2 score
from dual
union
select 3 score from dual) ; -- 6
select sum(score)
from (select 1 score
from dual
union
select 2 score
from dual
union
select '' score from dual); -- error 01790 boz -- error is coming from union part
-- compare as varchar2
select min(score), --1
max(score) --2
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual); -- ignore null value -- same for varchar2 and date data type . but for number ,we 'd better to_number .see below
--compare as number
select min(to_number(score)), --1
max(to_number(score)) --2
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual); -- so to_number('') will return null -- as min max sum avg will ingnore null value
select avg(to_number(score)), --1.5=(1+2)/2) -- here 2
avg(nvl(to_number(score), 0)), -- 1= (1+2+0)/3
sum(to_number(score)) -- 3
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual);
/**//*
So from here we can find that:
1. oracle aggregate function will erase the record which has null value
2 That is why above avg function return 1.5 as (1.5=(1+2)/2)) .only 2 records will consider here
*/
select count(score), --2
count(to_number(score)), --2
count(nvl(score, ' ')), --3
count(nvl(score, '')), -- 2
count(nvl2(score, '', '')) -- 0
from (select '1' score
from dual
union
select '2' score
from dual
union
select '' score from dual);
posted on 2009-04-15 19:54 dolphin_bobo 阅读(712) 评论(0) 编辑 收藏 举报