LNNVL官方解释翻译
lnnvl用于某个语句的where子句中的条件,如果条件为true就返回false;如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。
模拟测试环境
|
几种情况测试说明
--年份小于2009(lnnvl表示年份大于或者2009包含null)
SQL>
select
*
from
xifenfei
where
lnnvl(
year
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