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