Oracle LNNVL函数

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

模拟测试环境

几种情况测试说明

--年份小于2009(lnnvl表示年份大于或者2009包含null)

SQL> select * from xifenfei where lnnvl(year<2009);

NAME                       YEAR

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

xifenfei2008

xifenfei2009               2009

xifenfei2010               2010

xifenfei2011               2011

--year不为null(lnnvl表示年份为null)

SQL> select * from xifenfei where lnnvl(year is not null);

NAME                       YEAR

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

xifenfei2008

--年份为null(lnnvl表示年份不为null)

SQL> select * from xifenfei where lnnvl(year is  null);

NAME                       YEAR

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

xifenfei2001               2001

xifenfei2002               2002

xifenfei2003               2003

xifenfei2004               2004

xifenfei2005               2005

xifenfei2006               2006

xifenfei2007               2007

xifenfei2009               2009

xifenfei2010               2010

xifenfei2011               2011

10 rows selected.

--年份为12345(lnnvl表示年份不为12345)

SQL> select * from xifenfei where lnnvl(year =12345);

NAME                       YEAR

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

xifenfei2001               2001

xifenfei2002               2002

xifenfei2003               2003

xifenfei2004               2004

xifenfei2005               2005

xifenfei2006               2006

xifenfei2007               2007

xifenfei2008

xifenfei2009               2009

xifenfei2010               2010

xifenfei2011               2011

11 rows selected.

--年份不为12345(lnnvl表示年份为12345或者null)

SQL> select * from xifenfei where lnnvl(year !=12345);

NAME                       YEAR

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

xifenfei2008