[20250718]关于oracle lnnvl函数.txt

--//涉及or系列优化，使用or_expand提示，涉及使用oracle lnnvl函数,关于该函数，以前也写过一个简单的例子。
--//简单讲就是结果集合取反，涉及NULL值的特殊情况，全称是 Logical Not Null Value Logic(不知道如何理解)。
--//通过一些简单的例子再次说明，并且加强记忆。

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.测试：
SCOTT@book01p> select * from emp where comm >=500;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

--//如果使用lnnvl(comm >=500),相当于整个集合取反，如果使用comm <500是不行(因为NULL值的存在)：
SCOTT@book01p> select * from emp where comm <500;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
--//可以发现不包含空值的情况，使用lnnvl(comm >=500)就没有问题，类似集合的取反。

SCOTT@book01p> set null null
SCOTT@book01p> select * from emp where lnnvl(comm >=500);
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800 null               20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975 null               20
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850 null               30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450 null               10
      7788 SCOTT      ANALYST         7566 1987-07-13 00:00:00       3000 null               20
      7839 KING       PRESIDENT null       1981-11-17 00:00:00       5000 null               10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-07-13 00:00:00       1100 null               20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950 null               30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000 null               20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300 null               10
12 rows selected.
--//简单理解就是排除comm >=500的情况的结果集，也可以写成如下：
--//select * from emp where comm <500 or comm is null;
--//输出略。

--//如果查询范围写成如下：
SCOTT@book01p> select * from emp where comm >=500 and comm <=1000;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

--//如果要取反，写成如下会报错。
SCOTT@book01p> select * from emp where lnnvl(comm >=500 and comm <=1000);
select * from emp where lnnvl(comm >=500 and comm <=1000)
                                         *
ERROR at line 1:
ORA-00907: missing right parenthesis

SCOTT@book01p> select * from emp where lnnvl(comm between 500 and 1000);
select * from emp where lnnvl(comm between 500 and 1000)
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator
--//oracle不支持里面表达式存在and，or操作。

--//要支持这种情况的取反，写成如下，注意要使用or条件。
SCOTT@book01p> select * from emp where lnnvl(comm >=500) or lnnvl(comm <=1000);
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800 null               20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975 null               20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850 null               30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450 null               10
      7788 SCOTT      ANALYST         7566 1987-07-13 00:00:00       3000 null               20
      7839 KING       PRESIDENT null       1981-11-17 00:00:00       5000 null               10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-07-13 00:00:00       1100 null               20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950 null               30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000 null               20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300 null               10
13 rows selected.

--//或者写成如下：
SCOTT@book01p> select * from emp where comm <500 or comm >1000 or comm is null;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800 null               20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975 null               20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850 null               30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450 null               10
      7788 SCOTT      ANALYST         7566 1987-07-13 00:00:00       3000 null               20
      7839 KING       PRESIDENT null       1981-11-17 00:00:00       5000 null               10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-07-13 00:00:00       1100 null               20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950 null               30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000 null               20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300 null               10
13 rows selected.

4.关于lvnvl函数的返回值，这个问题实际上一直困惑我。

SCOTT@book01p> select dept.*,lnnvl(deptno = 10) from dept where LNNVL(DEPTNO = 10);
    DEPTNO DNAME                          LOC           LNNVL(DEPTNO = 10)
---------- ------------------------------ ------------- ----------------
        20 RESEARCH                       DALLAS        null
        30 SALES                          CHICAGO       null
        40 OPERATIONS                     BOSTON        null

SCOTT@book01p> select dept.*,lnnvl(deptno = 10) from dept ;
    DEPTNO DNAME                          LOC           LNNVL(DEPTNO = 10)
---------- ------------------------------ ------------- ----------------
        10 ACCOUNTING                     NEW YORK      null
        20 RESEARCH                       DALLAS        null
        30 SALES                          CHICAGO       null
        40 OPERATIONS                     BOSTON        null

--//当deptno = 10时，LNNVL(DEPTNO = 10)返回是NULL值，其他情况也是null，oracle不知道如何判断真假。
--//不是通过返回值判断的吗？