Oracle中通用函数:
主要有:NVL,
NVL2,
NULLIF,
COALESCE,这几个函数用在各个类型上都可以。
在介绍这个之前你必须明白什么是oracle中的空值null
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函数的用法:
语法
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;
===========================================================================================================
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 jWHERE 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的循环使用,在此就不举例子了。