LNNVL函数使用-CSDN博客

转载自：http://blog.csdn.net/stevendbaguo/article/details/12611189

显示那些佣金比例（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.