sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

学习笔记:Oracle过滤函数LNNVL where子句中的条件返回true/false

时间:2016-06-04 23:06   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

SQL语句中过滤函数LNNVL,where子句判断条件返回true/false

1.LNNVL官方解释

LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE. LNNVL can be used anywhere a scalar expression can appear, even in contexts where the IS [NOT] NULL, AND, or OR conditions are not valid but would otherwise be required to account for potential nulls. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions. In such cases, output from EXPLAIN PLAN shows this operation in the plan table output. The condition can evaluate any scalar values but cannot be a compound condition containing AND, OR, or BETWEEN.

2.LNNVL官方解释翻译

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

3.模拟测试环境

SQL> create table oracleplus(name varchar2(20),year number);

Table created.

SQL> insert into oracleplus values('oracleplus2001',2001);

1 row created.

SQL> insert into oracleplus values('oracleplus2002',2002);

1 row created.

SQL> insert into oracleplus values('oracleplus2003',2003);

1 row created.

SQL> insert into oracleplus values('oracleplus2004',2004);

1 row created.

SQL> insert into oracleplus values('oracleplus2005',2005);

1 row created.

SQL> insert into oracleplus values('oracleplus2006',2006);

1 row created.

SQL> insert into oracleplus values('oracleplus2007',2007);

1 row created.

SQL> insert into oracleplus values('oracleplus2008',null);

1 row created.

SQL> insert into oracleplus values('oracleplus2009',2009);

1 row created.

SQL> insert into oracleplus values('oracleplus2010',2010);

1 row created.

SQL> insert into oracleplus values('oracleplus2011',2011);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from oracleplus;

NAME                       YEAR
-------------------- ----------
oracleplus2001               2001
oracleplus2002               2002
oracleplus2003               2003
oracleplus2004               2004
oracleplus2005               2005
oracleplus2006               2006
oracleplus2007               2007
oracleplus2008
oracleplus2009               2009
oracleplus2010               2010
oracleplus2011               2011

11 rows selected.

4.几种情况测试说明

--年份小于2009(lnnvl表示年份大于或者2009包含null)
SQL> select * from oracleplus where lnnvl(year<2009);

NAME                       YEAR
-------------------- ----------
oracleplus2008
oracleplus2009               2009
oracleplus2010               2010
oracleplus2011               2011

--year不为null(lnnvl表示年份为null)
SQL> select * from oracleplus where lnnvl(year is not null);

NAME                       YEAR
-------------------- ----------
oracleplus2008

--年份为null(lnnvl表示年份不为null)
SQL> select * from oracleplus where lnnvl(year is  null);

NAME                       YEAR
-------------------- ----------
oracleplus2001               2001
oracleplus2002               2002
oracleplus2003               2003
oracleplus2004               2004
oracleplus2005               2005
oracleplus2006               2006
oracleplus2007               2007
oracleplus2009               2009
oracleplus2010               2010
oracleplus2011               2011

10 rows selected.

--年份为12345(lnnvl表示年份不为12345)
SQL> select * from oracleplus where lnnvl(year =12345);

NAME                       YEAR
-------------------- ----------
oracleplus2001               2001
oracleplus2002               2002
oracleplus2003               2003
oracleplus2004               2004
oracleplus2005               2005
oracleplus2006               2006
oracleplus2007               2007
oracleplus2008
oracleplus2009               2009
oracleplus2010               2010
oracleplus2011               2011

11 rows selected.

--年份不为12345(lnnvl表示年份为12345或者null)
SQL> select * from oracleplus where lnnvl(year !=12345);

NAME                       YEAR
-------------------- ----------
oracleplus2008



--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle过滤函数LNNVL where子句中的条件返回true/false

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/277.html

Oracle研究中心

关键词:

Oracle过滤函数LNNVL详解

where子句中的条件返回true/false的函数lnnvl