Oracle中的nvl()、nvl2()、nullif()、Coalesce()、lvvnl()等函数简介

Oracle中函数除了的字符串处理，日期函数，数学函数,以及转换函数等常用函数外，还有一类函数是通用函数。主要有：NVL,NVL2,NULLIF,COALESCE，这几个函数用在各个类型上都可以。

下面简单介绍一下几个函数的用法。

在介绍这个之前你必须明白什么是表中的空值(NULL)

1.NVL函数

NVL函数的格式如下：

NVL(expr1,expr2)

含义是：如果expr1为NULL,返回expr2的值，如果expr1的值不为NULL，则显示expr1本来的值。

该函数在我们需要替换值,需要对空值进行处理时非常常用,因为如果我们在对一个很大的表做查询时,为了提高查询速度,我们经常对为常用的字段添加index,但如果表中存在NULL,而我们又需要在 where 子句中对字段进行 null 值判断的话，将导致引擎放弃使用索引而进行全表扫描.

此时我们就可以使用NVL函数对每个字段进行临时的空值替换处理,比如,将NULL值替换成-1,再对字段进行值判断,index便不会被引擎放弃使用

例如：

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的,原本的值为空值

2 NVL2函数

NVL2函数的格式如下：

NVL2(expr1,expr2, expr3)

含义是：如果expr1值不为空，返回expr2的值,如果该函数的第expr1为空,那么返回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
FORD	1
MILLER	1

• 上面的例子中。凡是原值不为空,则在结果集中对应值为1，而原来值为空的,结果集中对应值为-1。

3. NULLIF函数

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
下面是一个例子。使用的是oracle中HR schema，如果HR处于锁定，请启用

这里的作用是显示出那些换过工作的人员原工作，现工作。

---

SELECT e.last_name, e.job_id, j.job_id, NULLIF(e.job_id, j.job_id) as “Old Job ID”
FROM employees as e, job_history as j
WHERE e.employee_id = j.employee_id
ORDER BY last_name;

LAST_NAME	e.JOB_ID	j.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)

1.表示可以指定多个表达式的占位符。
2.所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

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

SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 　　其返回结果为：3

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

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

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

其他函数

lvvnl()

用于某个语句的where子句中的条件，如果条件为true就返回false；
如果条件为UNKNOWN或者false就返回true。
该函数不能用于复合条件如AND, OR, or BETWEEN中。

select * from empwhere lnnvl(sal<=3000)