Oracle中的-------NVL( )函数

Oracle中通用函数:  

主要有：NVL,

                NVL2,

                NULLIF,

               COALESCE，这几个函数用在各个类型上都可以。

在介绍这个之前你必须明白什么是oracle中的空值null

1.NVL()函数

NVL函数的格式如下：

         NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

----------------------------------------------------------------

例如：
SQL> select ename,NVL(comm, -1) from emp;
 
ENAME NVL(COMM,-1)
------- ----
SMITH -1
ALLEN 300
WARD 500
JONES -1
MARTIN 1400
BLAKE -1
FORD -1
MILLER -1

其中显示-1的本来的值全部都是空值的

实用例子：查询某个员工年薪，如果为空则用0代替

select (sal+nvl(comm,o))*12

   from emp where emp.pno=:pno;

这里，comm是未定义的变量，代指数字类型。 nvl返回为0.

实例：返回员工姓名和奖金，若该员工没有奖金则返回 Not Applicable

   SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')        "COMMISSION" 

   FROM employees 

   WHERE last_name LIKE 'B%' 

   ORDER BY last_name; 

----------------------------------------------------------

以前对nvl函数的理解很模糊，今天，终于知道了nvl函数的用法：它主要是对null的处理，因为空值
是不能被索引的，所以查询时有些符合条件的数据可能查询不出来。
比如下面这张表的数据，如果你要求平均月销售金额（其中第12条
数据的月销售额为空），直接用avg()函数，数据是不准确的，这
样求出来的平均数是11个月的，而我们要求的是12个月的平均值，所以
这样做，得到的数据显然不准确。对于处理空值，我们nvl函数来对
空值处理一下，就能得出准确的数据的了：select count(*), round(avg(nvl(sell,0)),2) from sale;

现有一个商品销售表sale，表结构为：
month　 char(6)　 --月份
sell number(10,2)　--月销售金额
 
create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200008',1100);
insert into sale values('200009',1200);
insert into sale values('200010',1300);
insert into sale values('200011',1000);
insert into sale(month) values('200012');

select count(*) from sale;

select count(*), round(avg(nvl(sell,0)),2) from sale;

 

NVL函数的用法：
     从两个表达式返回一个非 null 值。
语法
NVL(eexpression_r1, eexpression_r2)
参数
eexpression_r1, eexpression_r2
如果 eexpression_r1 的计算结果为 null 值，则 NVL( ) 返回 eexpression_r2。
如果 eexpression_r1 的计算结果不是 null 值，则返回 eexpression_r1。eexpression_r1 和 eexpression_r2
可以是任意一种数据类型。如果 eexpression_r1 与 eexpression_r2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

 

这是我的一点点心得，希望对大家能有所帮助！！！

--------------------------------------------

对数字型： NVL（ comm,0);

对字符型 NVL( TO_CHAR(comm), 'No Commission')

对日期型 NVL（hiredate,' 31-DEC-99')
 ======================================================================================================================
2 NVL2函数

NVL2函数的格式如下：

        NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

SQL> select ename,NVL2(comm,-1,1) from emp;

 
ENAME NVL2(COMM,-1,1)
------- -----
SMITH 1
ALLEN -1
WARD -1
JONES 1
MARTIN -1
BLAKE 1
CLARK 1
SCOTT 1

上面的例子中。凡是结果是1的原来都不为空，而结果是-1的原来的值就是空。

例如 NVL2（comm，'sal+comm',sal)
     NVL2函数测试comm
如果comm为空，就返回sal 的值。如果 comm 不为空(null),就返回表达式 sal+comm的值。

实例：返回员工收入，看员工收入(income)是否由工资(salary)加奖金(commission)组成，这取决于commission_pct列是否为null

   SELECT last_name, salary, NVL2(commission_pct, salary + (salary * commission_pct), salary) income FROM employees 

   WHERE last_name like 'B%' 

   ORDER BY last_name; 

===========================================================================================================

3. NULLIF函数

     NULLIF(exp1,expr2)

       函数的作用:  是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

下面是一个例子。使用的是oracle中HR schema，如果HR处于锁定，请启用
这里的作用是显示出那些换过工作的人员原工作，现工作。

SQL> 

SELECT e.last_name, e.job_id,j.job_id,NULLIF(e.job_id, j.job_id) “Old Job ID”

FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;
 
LAST_NAME JOB_ID JOB_ID Old Job ID
----------------- ------- ------- -------
De Haan AD_VP IT_PROG AD_VP
Hartstein MK_MAN MK_REP MK_MAN
Kaufling ST_MAN ST_CLERK ST_MAN
Kochhar AD_VP AC_MGR AD_VP
Kochhar AD_VP AC_ACCOUNT AD_VP
Raphaely PU_MAN ST_CLERK PU_MAN
Taylor SA_REP SA_MAN SA_REP
Taylor SA_REP SA_REP
Whalen AD_ASST AC_ACCOUNT AD_ASST
Whalen AD_ASST AD_ASST

可以看到凡是employee。job_id和job_histroy.job_id相等的，

                                                                        都会在结果中输出NULL即为空

                                                                    ，否则显示的是employee。job_id

4.Coalesce函数
         Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。
格式如下：
         Coalesce(expr1, expr2, expr3….. exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

返回表达式中第一个非空表达式，如有以下语句： 　

　SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 　　

            其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。 　　

COALESCE(expression1,...n) 与此 CASE 函数等价：

这个函数实际上是NVL的循环使用，在此就不举例子了。