Oracle LNNVL函数

数据运维 2024-06-01 剑圣无痕 手机阅读

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

模拟测试环境

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

 

Table created.

 

SQL> insert into xifenfei values('xifenfei2001',2001);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2002',2002);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2003',2003);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2004',2004);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2005',2005);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2006',2006);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2007',2007);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2008',null);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2009',2009);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2010',2010);

 

1 row created.

 

SQL> insert into xifenfei values('xifenfei2011',2011);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from xifenfei;

 

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.

几种情况测试说明

--年份小于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