NULL值的处理、逻辑操作和函数嵌套
一：什么是NULLNULL 值是一个很特别的值。它既不是零，也不是空格。它的值是没有定义的、未知的、不确定的。一些英文书中用了如下的词来形容 NULL：“unavailable，unassigned，undefined，unknown，immeasurable，inapplicable。”总之您没有办法得到它的准确值。
二：含有NULL的表达式的计算
SQL> select ename, sal,comm,sal+comm "Income",job 2 from emp 3 where job not like 'SALES%' 4 order by job;
ENAME SAL COMM Income JOB---------- ---------- ---------- ---------- ---------SCOTT 3000 ANALYSTFORD 3000 ANALYSTSMITH 800 CLERKJAMES 950 CLERKADAMS 1100 CLERKMILLER 1300 CLERKJONES 2975 MANAGERBLAKE 2850 MANAGERCLARK 2450 MANAGERKING 5000 PRESIDENT
已选择10行。
SQL>
SAL一列全部有值，但是 SAL加上comm之后的Income 一列全部却都是空，问题出在 NULL（空值）上，因为只要在一个表达式中包含任何 NULL（空值），该表达式的值就为 NULL。
SQL> select empno, ename,sal,job,comm from emp where comm is null;
EMPNO ENAME SAL JOB COMM---------- ---------- ---------- --------- ---------- 7369 SMITH 800 CLERK 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK
已选择10行。
SQL> select empno, ename,sal,job,comm from emp where comm is not null;
EMPNO ENAME SAL JOB COMM---------- ---------- ---------- --------- ---------- 7499 ALLEN 1600 SALESMAN 300 7521 WARD 1250 SALESMAN 500 7654 MARTIN 1250 SALESMAN 1400 7844 TURNER 1500 SALESMAN 0
SQL>
三：空值（NULL）的排序
SQL> select empno,job,comm from emp order by comm;
EMPNO JOB COMM---------- --------- ---------- 7844 SALESMAN 0 7499 SALESMAN 300 7521 SALESMAN 500 7654 SALESMAN 1400 7369 CLERK 7566 MANAGER 7900 CLERK 7934 CLERK 7902 ANALYST 7876 CLERK 7698 MANAGER 7782 MANAGER 7788 ANALYST 7839 PRESIDENT
已选择14行。
SQL>上例表明：在升序排序时，NULL值排在最后。
SQL> select empno,job,comm from emp order by comm desc;
EMPNO JOB COMM---------- --------- ---------- 7369 CLERK 7566 MANAGER 7782 MANAGER 7698 MANAGER 7788 ANALYST 7839 PRESIDENT 7900 CLERK 7934 CLERK 7902 ANALYST 7876 CLERK 7654 SALESMAN 1400 7521 SALESMAN 500 7499 SALESMAN 300 7844 SALESMAN 0
已选择14行。
SQL>上例表明：在降序排序时，NULL值排在最前。
四：逻辑表达式和逻辑运算符
Oracle提供了AND（逻辑与/逻辑乘）、OR（逻辑加/逻辑或）和NOT（逻辑非）3个逻辑运算符。其中AND（逻辑与/逻辑乘）和OR（逻辑加/逻辑或）把两个条件组合在一起产生一个结果。 其格式如下： 条件1 逻辑运算符 条件2； 它也叫逻辑表达式，这里逻辑运算符为AND或OR。 逻辑表达式有以下的重要特性： 条件1 逻辑运算符 条件2 = 条件2 逻辑运算符 条件1 （定理5.1） 在二值逻辑中逻辑表达式或条件只能为真（T）或假（F）。但在Oracle的逻辑表达式中还引入了另一个值，未知（NULL）。 在二值逻辑中逻辑表达式“条件1AND 条件2”中，只有当条件1和条件2同时为真时其结果才为真，否则为假。但现在我们多了一个NULL值，以上逻辑表达式的结果又该如何呢？
下面是AND的真值表（算法）： F AND F = F F AND T = F F AND NULL = F T AND F = F T AND T = T T AND NULL IS NULL NULL AND F = F NULL AND T IS NULL NULL AND NULL IS NULL
您只要能记住真值表的中线和左下角（即黑体）部分就行了。因为剩余部分您可以用定理 5.1 推导出来。您也可以把下划线部分看成对称轴，对称轴上下两部的结果相等。因此您只要记住对称轴的上部或下部就可以了，另一部分您可以用交换 AND 左右的真值来得到。 AND运算的优先级为：F-->NULL-->T
即在AND逻辑表达式中:只要有F其结果就为F，如真值表中第一行和第一列所表示的；如果没有F，在AND逻辑表达式中有NULL其结果就为NULL，如真值表中，除了结果为F的部分最后一行和最后一列所表示的；只有当两个条件都为T时，AND逻辑表达式的结果才为T，如真值表中正中心所表示的。
SQL> select empno, ename,sal,job 2 from emp 3 where sal >=1500 4 and job in ('SALESMAN','CLERK','MANAGER') 5 order by job;
EMPNO ENAME SAL JOB---------- ---------- ---------- --------- 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN
SQL>
下面我们看一下OR的真值表： T OR T = T T OR F = T T OR NULL = T F OR T = T F OR F = F F OR NULL IS NULL NULL OR T = T NULL OR F IS NULL NULL AND NULL IS NULL 同样您只要能记住真值表的中线和左下角（即黑体）部分就行了。因为剩余部分您可以用定理5.1推导出来。您也可以把下划线部分看成对称轴，对称轴上下两部的结果相等。
因此您只要记住对称轴的上部或下部就可以了，另一部分您可以用交换 OR 左右的真值来得到。 OR运算的优先级为： T-->NULL-->F
即在 OR 逻辑表达式中:只要有 T 其结果就为 T，如真值表中第一行和第一列所表示的；如果没有T，在 OR 逻辑表达式中有NULL其结果就为NULL，如真值表中，除了结果为T的部分最后一行和最后一列所表示的；只有当两个条件都为F，OR逻辑表达式的结果才为F，如真值表中正中心所表示的。
SQL> l 1 select empno, ename,sal,job 2 from emp 3 where sal >=1500 4 and job in ('SALESMAN','CLERK','MANAGER') 5* order by jobSQL> 4 4* and job in ('SALESMAN','CLERK','MANAGER')SQL> c /and/or 4* or job in ('SALESMAN','CLERK','MANAGER')SQL> l 1 select empno, ename,sal,job 2 from emp 3 where sal >=1500 4 or job in ('SALESMAN','CLERK','MANAGER') 5* order by jobSQL> /
EMPNO ENAME SAL JOB---------- ---------- ---------- --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7934 MILLER 1300 CLERK 7900 JAMES 950 CLERK 7566 JONES 2975 MANAGER 7782 CLARK 2450 MANAGER 7698 BLAKE 2850 MANAGER 7839 KING 5000 PRESIDENT 7499 ALLEN 1600 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 7521 WARD 1250 SALESMAN
已选择14行。
SQL>
现在介绍最后一个逻辑运算符NOT。在二值逻辑中，NOT的真值表非常简单，其真值表如下： NOT T = F NOT F = T NOT NULL = NULL
五：运算符的优先级
（1）算术运算符→（2）连接运算符→（3）比较（关系）运算符→（4）IS NULL，IS NOT NULL，LIKE，NOT LIKE，IN，NOT IN 运算符→（5）BETWEEN，NOT BETWEEN运算符→（6）NOT逻辑运算符→（7）AND逻辑运算符→（8）OR逻辑运算符。 如果使用了括号，括号中的运算优先。
SQL> select empno, ename,sal,job 2 from emp 3 where job='CLERK' 4 or job='SALESMAN' 5 and sal >= 1300;
EMPNO ENAME SAL JOB---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK
已选择6行。
SQL>按照运算符的优先级，Oracle首先执行由黑体部分组成的条件，即首先找到工资在1300元或以上的推销员（SALESMAN）；第二步是找到所有的文员（CLERK）；最后 Oracle显示的结果为所有的文员（CLERK）和工资在1300元或以上的推销员（SALESMAN）的信息。
SQL> select empno, ename,job,sal 2 from emp 3 where (job='CLERK' or job='SALESMAN') 4 and sal >= 1300;
EMPNO ENAME JOB SAL---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 7934 MILLER CLERK 1300
SQL>按照运算符的优先级，Oracle 首先执行由黑体部分组成的条件，即找出所有的文员（CLERK）和推销员（SALESMAN），接下来找出工资在1300元或以上的员工；最后Oracle显示的结果为所有工资在1300元或以上的文员（CLERK）和推销员（SALESMAN）的信息。
六：用AND和OR替代BETWEEN AND 和IN运算符
SQL> select empno,ename,sal 2 from emp 3 where sal between 1500 and 2900;
EMPNO ENAME SAL---------- ---------- ---------- 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500
SQL> select empno,ename,sal 2 from emp 3 where sal >= 1500 and sal <= 2900;
EMPNO ENAME SAL---------- ---------- ---------- 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500
SQL>上面两个sql的执行结果是一样的。
SQL> select empno,ename,sal 2 from emp 3 where job in ('SALESMAN','CLERK','MANAGER');
EMPNO ENAME SAL---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7934 MILLER 1300
已选择11行。
SQL> select empno,ename,sal 2 from emp 3 where job='SALESMAN' or job='CLERK' or job='MANAGER';
EMPNO ENAME SAL---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7934 MILLER 1300
已选择11行。
SQL>上面两个sql的执行结果是一样的。
七：NVL函数
NVL函数是一个空值转换函数。
SQL> L 1 select ename,sal,comm,sal+comm,job 2 from emp 3 where job not like 'SALES%' 4* order by jobSQL> /
ENAME SAL COMM SAL+COMM JOB---------- ---------- ---------- ---------- ---------SCOTT 3000 ANALYSTFORD 3000 ANALYSTSMITH 800 CLERKJAMES 950 CLERKADAMS 1100 CLERKMILLER 1300 CLERKJONES 2975 MANAGERBLAKE 2850 MANAGERCLARK 2450 MANAGERKING 5000 PRESIDENT
由于表达式sal+comm中comm的值为NULL，我们得到的所有sal+comm都为NULL。Oracle提供的NVL函数可以用来解决这一难题。如下：
SQL> select ename,sal,comm,sal+NVL(comm,0),job 2 from emp 3 where job not like 'SALES%' 4 order by job;
ENAME SAL COMM SAL+NVL(COMM,0) JOB---------- ---------- ---------- --------------- ---------SCOTT 3000 3000 ANALYSTFORD 3000 3000 ANALYSTSMITH 800 800 CLERKJAMES 950 950 CLERKADAMS 1100 1100 CLERKMILLER 1300 1300 CLERKJONES 2975 2975 MANAGERBLAKE 2850 2850 MANAGERCLARK 2450 2450 MANAGERKING 5000 5000 PRESIDENT
NVL函数是把一空值（NULL）转换成某一实际的值。它的格式如下： NVL（表达式1，表达式2） 如果表达式1为空值（NULL），NVL返回值为表达式2的值，否则返回表达式1的值。
该函数的目的是把一个空值（NULL）转换成一个实际的值。其表达式 1 和表达式 2可以是数字型、字符型或日期型，但表达式1和表达式2的数据类型必须一致。如： 对数字型：NVL（comm，0）； 对字符型：NVL（TO_CHAR(comm)，‘No Commission’）； 对日期型：NVL（hiredate，‘31-DEC-99’）。 有经验的Oracle开发人员会在他们开发的SQL语句中经常地使用NVL函数以避免因空值（NULL）产生的错误。
八：DECODE函数
因为SQL中没有逻辑判断语句（分支语句），所以Oracle引入了DECODE函数来完成类似的功能。您可以使用下例的查询语句，利用 DECODE 函数求出基于不同职位的（job）每个员工加薪后的工资值。
SQL> l 1 select ename "Name",job,sal "Salary",decode(job,'SALESMAN',sal*1.15,'CLERK',sal*1.2,'ANALYST',sal*1.25,sal*1.4) "New Salary" 2 from emp 3* order by jobSQL> /
Name JOB Salary New Salary---------- --------- ---------- ----------SCOTT ANALYST 3000 3750FORD ANALYST 3000 3750SMITH CLERK 800 960ADAMS CLERK 1100 1320MILLER CLERK 1300 1560JAMES CLERK 950 1140JONES MANAGER 2975 4165CLARK MANAGER 2450 3430BLAKE MANAGER 2850 3990KING PRESIDENT 5000 7000ALLEN SALESMAN 1600 1840MARTIN SALESMAN 1250 1437.5TURNER SALESMAN 1500 1725WARD SALESMAN 1250 1437.5
已选择14行。
SQL>在上例的查询语句中，DECODE函数执行的步骤如下： （1）当job为SALESMAN时，DECODE 函数返回表达式sal*1.15的值，否则执行（2）。 （2）当job为CLERK时DECODE函数返回表达式sal*1.2的值，否则执行（3） （3）当job为ANALYST时DECODE函数返回表达式sal*1.25的值，否则执行（4） （4）DECODE函数返回表达式sal*1.40的值。
九：单值函数的嵌套
Oracle 的单值函数可以嵌套。函数的计算次序为从里到外。按 Oracle 的说法，Oracle的单值函数可以嵌套任意层。但这只是在理论上行得通，因为嵌套的层数要受实际可用内存的限制，而实际可用内存又是受操作系统和计算机硬件的限制的。
SQL> select ename "Name",nvl(to_char(comm), ename||' is not a Salesperson') "Commission" 2 from emp 3 order by 2;
Name Commission---------- ----------------------------------------TURNER 0MARTIN 1400ALLEN 300WARD 500ADAMS ADAMS is not a SalespersonBLAKE BLAKE is not a SalespersonCLARK CLARK is not a SalespersonFORD FORD is not a SalespersonJAMES JAMES is not a SalespersonJONES JONES is not a SalespersonKING KING is not a SalespersonMILLER MILLER is not a SalespersonSCOTT SCOTT is not a SalespersonSMITH SMITH is not a Salesperson
在上例的查询语句中，表达式NVL（TO_CHAR(comm), ename||’ is not a Salesperson!’）的计算次序如下： （1）Oracle首先用TO_CHAR函数把comm由数字型转换成字符型； （2）NVL函数测试TO_CHAR（comm）； （3）如果TO_CHAR（comm）不为空就返回TO_CHAR（comm）的值； （4）如果TO_CHAR（comm）为空就返回表达式ename||’ is not a Salesperson!’的值。
注意：由于 TO_CHAR 函数把 comm 由数字型转换成字符型，因此查询显示的结果是按 ASCII码的顺序排列的。
十：oracle9i新增加的单值函数和表达式
NVL2、NULLIF和COALESCE为Oracle9i新增加的函数，而CASE为Oracle9i新增加的表达式。
NVL2是Oracle9i刚刚引入的一个新函数。NVL2对NVL函数进行了小小的扩充。
SQL> select ename "Name",nvl2(comm,'sal+comm',sal) "Income",job 2 from emp 3 where job not like 'SALES%' 4 order by job;
Name Income JOB---------- ---------------------------------------- ---------SCOTT 3000 ANALYSTFORD 3000 ANALYSTSMITH 800 CLERKJAMES 950 CLERKADAMS 1100 CLERKMILLER 1300 CLERKJONES 2975 MANAGERBLAKE 2850 MANAGERCLARK 2450 MANAGERKING 5000 PRESIDENT
在上例的查询语句中，表达式NVL2（comm,’sal+comm’,sal）"Income" 的执行次序如下： （1）NVL2函数测试comm； （2）如果comm不为空（NULL），就返回表达式sal+comm的值； （3）如果comm为空（NULL），就返回sal的值；（4）返回值的列标为Income。
NVL2函数的格式如下： NVL2（表达式1，表达式2，表达式3） 函数NVL2（表达式1，表达式2，表达式3）的执行次序如下。 如果表达式1不为空值（NULL），NVL2函数返回值为表达式2的值； 如果表达式1为空值（NULL），NVL2函数返回值为表达式3的值。 表达式2和表达式3可以是除LONG以外的任何数据类型，表达式1可以是任何数据类型。
如果表达式2和表达式3的数据类型不同，Oracle服务器把表达式3的数据类型转换成表达式 2的数据类型；此时如果表达式3 为空值，Oracle服务器就不进行数据类型的转换。NVL2 函数返回值的数据类型与表达式 2 的数据类型相同。但当表达式 2 的数据类型为定长字符型CHAR时，NVL2函数返回值的数据类型为变长字符型VARCHAR2。
NULLIF是Oracle9i新引入的另一函数。您可以使用下例的查询语句来测试这一函数。
SQL> SELECT ename, job, LENGTH(ename) "Name_Length", LENGTH(job) "Job_Lenght", 2 NULLIF(LENGTH(ename),LENGTH(job)) "Comparision" 3 FROM emp;
ENAME JOB Name_Length Job_Lenght Comparision---------- --------- ----------- ---------- -----------SMITH CLERK 5 5ALLEN SALESMAN 5 8 5WARD SALESMAN 4 8 4JONES MANAGER 5 7 5MARTIN SALESMAN 6 8 6BLAKE MANAGER 5 7 5CLARK MANAGER 5 7 5SCOTT ANALYST 5 7 5KING PRESIDENT 4 9 4TURNER SALESMAN 6 8 6ADAMS CLERK 5 5JAMES CLERK 5 5FORD ANALYST 4 7 4MILLER CLERK 6 5 6
从上例可以看出NULLIF函数的功能： 当LENGTH（ename）和LENGTH（job）相等时，函数NULLIF（LENGTH（ename），LENGTH（job））返回空值（NULL）；否则返回LENGTH（ename）的值，即ename的长度。 NULLIF函数的格式如下：NULLIF（表达式1，表达式2） 函数NULLIF（表达式1，表达式2）的执行次序如下： NULLIF函数比较表达式1和表达式2； 如果两个表达式相等就返回空值（NULL）； 如果不等就返回表达式1。 NULLIF函数中的表达式1不能为“NULL”。
现在来介绍Oracle9i引入的另一个新函数COALESCE。
SQL> insert into emp(empno,ename) values(10,'testuser');
已创建 1 行。
SQL> select ename,sal,comm,coalesce(comm,sal*0.1,100) "New Commission" 2 from emp;
ENAME SAL COMM New Commission---------- ---------- ---------- --------------SMITH 800 80ALLEN 1600 300 300WARD 1250 500 500JONES 2975 297.5MARTIN 1250 1400 1400BLAKE 2850 285CLARK 2450 245SCOTT 3000 300KING 5000 500TURNER 1500 0 0ADAMS 1100 110JAMES 950 95FORD 3000 300MILLER 1300 130testuser 100
从上例的结果可以看出COALESCE函数的功能： 当comm 不为空时，COALESCE函数返回comm的值。 如ALLEN的佣金（comm）为300，New Commission的显示也为300； 当 comm 为空，同时 sal*0.1 不为空（即 sae 不为空）时，COALESCE 函数返回sal*0.1的值。如SMITH的佣金（comm）为空，但sal=800不为空，所以COALESCE函数返回800*0.1=80； 当comm 为空并且sal*0.1也为空（即sae为空）时，COALESCE函数返回100。如QUEEN的佣金（comm）为空，并且sal也为空，所以COALESCE函数返回100。 COALESCE函数的格式如下： COALESCE（表达式1，表达式2，表达式3，…，表达式n） 该函数返回表达式列表（表达式1，表达式2，表达式3，…，表达式n）中第一个不为空的表达式的值。 CASE表达式也是Oracle9i刚刚引入的。它的功能与DECODE函数完全相同，只是它的语法更类似于一般的程序设计语言的 CASE 语句。
SQL> L 1 SELECT ename,job,sal, 2 CASE job WHEN 'SALESMAN' THEN sal*1.15 3 WHEN 'CLERK' THEN sal*1.2 4 WHEN 'ANALYST' THEN sal*1.25 5 ELSE sal*1.4 6 END "New Salary" 7 FROM emp 8* ORDER BY jobSQL> /
ENAME JOB SAL New Salary---------- --------- ---------- ----------SCOTT ANALYST 3000 3750FORD ANALYST 3000 3750SMITH CLERK 800 960ADAMS CLERK 1100 1320MILLER CLERK 1300 1560JAMES CLERK 950 1140JONES MANAGER 2975 4165CLARK MANAGER 2450 3430BLAKE MANAGER 2850 3990KING PRESIDENT 5000 7000ALLEN SALESMAN 1600 1840MARTIN SALESMAN 1250 1437.5TURNER SALESMAN 1500 1725WARD SALESMAN 1250 1437.5
在上例的查询语句中，CASE表达式执行的步骤如下： 当job为SALESMAN时CASE表达式返回表达式sal*1.15的值； 否则，当job为CLERK时CASE表达式返回表达式sal*1.20的值； 否则，当job为ANALYST时CASE表达式返回表达式sal*1.25的值； 否则，CASE表达式返回表达式sal*1.40的值。
