星期二, 2021-09-14 14:27 — adminshiping1

oracle中lnnvl函数

今天在无意中看到lnnvl这个函数，分享一下

lnnvl用于某个语句的where子句中的条件，如果条件为真就返回真，否则为假。
其含义可理解为LNNVL == IS NULL OR IS NOT TRUE

它的优势在于处理简单条件判断无法实现的null条件，用举例说明

SQL> select * from plch_employees;

EMPLOYEE_ID LAST_NAME           SALARY              COMMISSION_PCT
--------------------------------------- -------------------------------------------------------------------------------- ---------- --------------
           300      O'Keefe                1000000
           100     Picasso                 1000000            0.3
            200    Mondrian               1000000            0.15

如果要查询commision）不大于20%, 或者为NULL的员工的姓名，则可以用下面

SQL> SELECT last_name FROM plch_employees e WHERE LNNVL (e.commission_pct > .2);

LAST_NAME
--------------------------------------------------------------------------------
O'Keefe
Mondrian

查出小于commision0.2和为null的值，这种方法可以查询出null的值是比较方便的。

分类: oracle

标签: oracle函数

来自 https://www.cnblogs.com/wujin/archive/2012/03/14/2395629.html

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.

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<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

来自 https://blog.csdn.net/sunpeng1117/article/details/39639083

普通分类:

oracle

You are here

ORACLE的LNNVL函数使用有大用

oracle中lnnvl函数

友情链接

搜索表单

用户登录

You are here

ORACLE的LNNVL函数使用 有大用

oracle中lnnvl函数

友情链接

ORACLE的LNNVL函数使用有大用