Oracle的Nvl函数

一、介绍：

nvl( ) 函数

从两个表达式返回一个非 null 值。

语法

NVL(eExpression1, eExpression2)

参数
eExpression1, eExpression2

如 果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

说明

在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID

注意：两个参数得类型要匹配

 

二、使用：

 

 select   h.site, NVL(TO_NUMBER(h.ticketSum),0) as ticketSum, NVL(TO_NUMBER(h.totalTax),0) as totalTax, 
                      NVL(TO_NUMBER(j.refundTktSum),0) as refundTktSum, NVL(TO_NUMBER(j.refundTax),0) as refundTax, 
                      NVL(TO_NUMBER(j.refundFee),0) as refundFee,  
                     (NVL(TO_NUMBER(h.ticketSum),0) +  NVL(TO_NUMBER(h.totalTax),0) - NVL(TO_NUMBER(j.refundTktSum),0) - NVL(TO_NUMBER(j.refundTax),0) + NVL(TO_NUMBER(j.refundFee),0)) as totalSum
 
    from  
                (select a.site, a.ticketSum, b.totalTax  from (select  sum(t.pay_amount) as ticketSum,t.site  from ord_tkt t where 1=1 group by t.site) a 
		     join  (select sum(NVL(TO_NUMBER(m.TAX_AMT1),0) + NVL(TO_NUMBER(m.TAX_AMT2),0) + NVL(TO_NUMBER(m.TAX_AMT3),0) + NVL(TO_NUMBER(m.TAX_AMT4),0) + NVL(TO_NUMBER(m.TAX_AMT5),0)  + NVL(TO_NUMBER(m.TAX_AMT6),0) + NVL(TO_NUMBER(m.TAX_AMT7),0) + NVL(TO_NUMBER(m.TAX_AMT8),0) + NVL(TO_NUMBER(m.TAX_AMT9),0) + NVL(TO_NUMBER(m.TAX_AMT10),0)  + NVL(TO_NUMBER(m.TAX_AMT11),0) + NVL(TO_NUMBER(m.TAX_AMT12),0) + NVL(TO_NUMBER(m.TAX_AMT13),0) + NVL(TO_NUMBER(m.TAX_AMT14),0) + NVL(TO_NUMBER(m.TAX_AMT15),0)) as totalTax, t.site  from ord_tax m, ord_tkt t  where 1=1  and t.ord_no=m.ord_no and t.pax_id = m.pax_id   group by t.site) b  
	               on a.site=b.site) h 
left join    (select c.site,c.refundTax, d.refundFee, e.refundTktSum  from 
                                 (select sum(NVL(TO_NUMBER(m.TAX_AMT1),0) + NVL(TO_NUMBER(m.TAX_AMT2),0) + NVL(TO_NUMBER(m.TAX_AMT3),0) + NVL(TO_NUMBER(m.TAX_AMT4),0) + NVL(TO_NUMBER(m.TAX_AMT5),0)  + NVL(TO_NUMBER(m.TAX_AMT6),0) + NVL(TO_NUMBER(m.TAX_AMT7),0) + NVL(TO_NUMBER(m.TAX_AMT8),0) + NVL(TO_NUMBER(m.TAX_AMT9),0) + NVL(TO_NUMBER(m.TAX_AMT10),0)  + NVL(TO_NUMBER(m.TAX_AMT11),0) + NVL(TO_NUMBER(m.TAX_AMT12),0) + NVL(TO_NUMBER(m.TAX_AMT13),0) + NVL(TO_NUMBER(m.TAX_AMT14),0) + NVL(TO_NUMBER(m.TAX_AMT15),0)) as refundTax, t.site  from rfnd_inter_trfd m, ord_tkt t, rfnd_apply n  where 1=1  and t.rfnd_app_id= m.rfnd_app_id and t.pax_id = m.pax_id and t.rfnd_app_id = n.ID and n.ORD_NO = t.ORD_NO group by t.site) c,
				 (select sum(NVL(TO_NUMBER(m.RFND_FEE),0)) as refundFee, t.site   from rfnd_inter_trfd m, ord_tkt t, rfnd_apply n  where 1=1  and t.rfnd_app_id= m.rfnd_app_id and t.pax_id = m.pax_id and t.rfnd_app_id = n.ID and n.ORD_NO = t.ORD_NO group by site) d,
				 (select sum(NVL(TO_NUMBER(m.TAX_AMT1),0) + NVL(TO_NUMBER(m.RFND_FEE),0)+  - (NVL(TO_NUMBER(m.TAX_AMT1),0) + NVL(TO_NUMBER(m.TAX_AMT2),0) + NVL(TO_NUMBER(m.TAX_AMT3),0) + NVL(TO_NUMBER(m.TAX_AMT4),0) + NVL(TO_NUMBER(m.TAX_AMT5),0)  + NVL(TO_NUMBER(m.TAX_AMT6),0) + NVL(TO_NUMBER(m.TAX_AMT7),0) + NVL(TO_NUMBER(m.TAX_AMT8),0) + NVL(TO_NUMBER(m.TAX_AMT9),0) + NVL(TO_NUMBER(m.TAX_AMT10),0)  + NVL(TO_NUMBER(m.TAX_AMT11),0) + NVL(TO_NUMBER(m.TAX_AMT12),0) + NVL(TO_NUMBER(m.TAX_AMT13),0) + NVL(TO_NUMBER(m.TAX_AMT14),0) + NVL(TO_NUMBER(m.TAX_AMT15),0))) as refundTktSum, t.site  from rfnd_inter_trfd m, ord_tkt t, rfnd_apply n where 1=1  and t.rfnd_app_id= m.rfnd_app_id and t.pax_id = m.pax_id and t.rfnd_app_id = n.ID and n.ORD_NO = t.ORD_NO group by t.site) e 
		 where c.site=d.site and c.site=e.site ) j 
       on     h.site = j.site