LNNVL函式的使用

以下轉自：http://blog.csdn.net/stevendbaguo/article/details/12611189 作者：深圳gg

      顯示那些佣金比例（commision）不大於20%或者為NULL的員工的資訊。

CREATE TABLE plch_employees
(
    employee_id      INTEGER PRIMARY KEY
,  last_name        VARCHAR2 (100)
,  salary           NUMBER
,  commission_pct   NUMBER
);
INSERT INTO plch_employees   VALUES (100,  'Picasso',  1000000 ,  .3);
INSERT INTO plch_employees VALUES (200,  'Mondrian',  1000000,  .15);
INSERT INTO plch_employeesVALUES (300,  'O''Keefe',  1000000,  NULL);
COMMIT;

SQL> SELECT last_name,e.salary,e.commission_pct
       FROM plch_employees e
      WHERE e.commission_pct <= .2
         OR e.commission_pct IS NULL;
LAST_NAME           SALARY COMMISSION_PCT
--------------- ---------- --------------
Mondrian           1000000            .15
O'Keefe            1000000


SQL> SELECT last_name,e.salary,e.commission_pct
       FROM plch_employees e
     WHERE LNNVL (e.commission_pct > .2);
LAST_NAME           SALARY COMMISSION_PCT
--------------- ---------- --------------
Mondrian           1000000            .15
O'Keefe            1000000

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.

The table that follows shows what LNNVL returns given that a = 2 and b is null.

Condition	Truth of Condition	LNNVL Return Value
a = 1	FALSE	TRUE
a = 2	TRUE	FALSE
a IS NULL	FALSE	TRUE
b = 1	UNKNOWN	TRUE
b IS NULL	TRUE	FALSE
a = b	UNKNOWN	TRUE

      大致的意思是：lnnvl用於某個語句的where子句中的條件，如果條件為true就返回false；如果條件為UNKNOWN或者false就返回true。該函式不能用於複合條件如AND, OR, or BETWEEN中。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22207394/viewspace-1255341/，如需轉載，請註明出處，否則將追究法律責任。