漫谈oracle 中的空值_ORACLE_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

漫谈oracle 中的空值

作者：闵涛文章来源：闵涛的学习笔记点击数：1677 更新时间：2009/4/22 22:05:30

在数据库中，空值用来表示实际值未知或无意义的情况。在一个表中，如果一行中的某列没有值，那么就称它为空值（NULL）。任何数据类型的列, 只要没有使用非空（NOT NULL）或主键（PRIMARY KEY）完整性限制，都可以出现空值。在实际应用中，如果忽略空值的存在，将会造成造成不必要的麻烦。

---- 例如，在下面的雇员表（EMP）中，雇员名（ENAME）为KING 的行，因为KING 为最高官员（PRESIDENT），他没有主管（MGR），所以其MGR 为空值。因为不是所有的雇员都有手续费（COMM），所以列COMM 允许有空值，除300、500、1400、0 以外的其它各行COMM 均为空值。

EMPNO ENAME    JOB       MGR HIREDATE    SAL   COMM  DEPTNO
---- -------- -------- --------- -------- ------ ---------
7369 SMITH     CLERK     7902 17-DEC-80  800           20
7499 ALLEN     SALESMAN  7698 20-FEB-81  1600    300   30
7521 WARD      SALESMAN  7698 22-FEB-81  1250    500   30
7566 JONES     MANAGER   7839 02-APR-81  2975          20
7654 MARTIN    SALESMAN  7698 28-SEP-81  1250   1400   30
7698 BLAKE     MANAGER   7839 01-MAY-81  2850          30
7782 CLARK     MANAGER   7839 09-JUN-81  2450          10
7788 SCOTT     ANALYST   7566 09-DEC-82  3000          20
7839 KING      PRESIDENT      17-NOV-81  5000          10
7844 TURNER    SALESMAN  7698 08-SEP-81  1500      0   30
7876 ADAMS     CLERK     7788 12-JAN-83  1100          20
7900 JAMES     CLERK     7698 03-DEC-81   950          30
7902 FORD      ANALYST   7566 03-DEC-81  3000          20
7934 MILLER    CLERK     7782 23-JAN-82  1300          10

---- 本文将以上述EMP 表为例，具体讨论一下空值在日常应用中所具有的一些特性。

---- 一、空值的生成及特点

---- 1. 空值的生成

---- 如果一列没有非空（NOT NULL）完整性限制，那么其缺省的值为空值，即如果插入一行时未指定该列的值，则其值为空值。

---- 使用SQL 语句INSERT 插入行，凡未涉及到的列，其值为空值；涉及到的列，如果其值确实为空值，插入时可以用NULL 来表示（对于字符型的列，也可以用'''''''' 来表示）。

---- 例：插入一行，其EMPNO 为1、ENAME 为''''JIA''''、SAL 为10000、job 和comm 为空值。

SQL >insert into emp(empno,ename,job,sal,comm) 
values(1,''''JIA'''',NULL,1000,NULL);
SQL >select * from emp where empno=1;
EMPNO ENAME  JOB   MGR HIREDATE    SAL    COMM    DEPTNO
--------- ---------- --------- --------- --------- ---------
        1 JIA         1000

---- 可以看到新插入的一行，除job 和comm 为空值外，mgr、hiredate、deptno 三列由于插入时未涉及，也为空值。

---- 使用SQL 语句UPDATE 来修改数据，空值可用NULL 来表示（对于字符型的列，也可以用'''''''' 来表示）。例:

SQL >update emp set ename=NULL,sal=NULL where empno=1;

---- 2. 空值的特点

---- 空值具有以下特点：

---- * 等价于没有任何值。

---- * 与 0、空字符串或空格不同。

---- * 在where 条件中, Oracle 认为结果为NULL 的条件为FALSE，带有这样条件的select 语句不返回行，并且不返回错误信息。但NULL 和FALSE 是不同的。

---- * 排序时比其他数据都大。

---- * 空值不能被索引。

---- 二、空值的测试

---- 因为空值表示缺少数据，所以空值和其它值没有可比性，即不能用等于、不等于、大于或小于和其它数值比较，当然也包括空值本身（但是在decode 中例外，两个空值被认为是等价）。测试空值只能用比较操作符IS NULL 和IS NOT NULL。如果使用带有其它比较操作符的条件表达式，并且其结果依赖于空值，那么其结果必定是NULL。在where 条件中，Oracle 认为结果为NULL 的条件为FALSE，带有这样条件的select 语句不返回行，也不返回错误信息。

---- 例如查询EMP 表中MGR 为NULL 的行：

SQL >select * from emp where mgr='''''''';  
no rows selected
SQL >select * from emp where mgr=null;  
no rows selected
SQL >select * from emp where mgr is null;
EMPNO ENAME  JOB  MGR HIREDATE   SAL  COMM  DEPTNO
---------- --------- --------- --------- ---------
7839 KING  PRESIDENT  17-NOV-81  5000        10

---- 第1、2 句写法不妥，WHERE 条件结果为NULL，不返回行。第三句正确，返回MGR 为空值的行。

---- 三、空值和操作符

---- 1. 空值和逻辑操作符

---- 逻辑操作符

---- 表达式

---- 结果

AND
NULL AND TRUE
NULL

NULL AND FALSE
FALSE

NULL AND NULL
NULL
OR
NULL OR TRUE
TRUE

NULL OR FALSE
NULL

NULL OR NULL
NULL
NOT
NOT NULL
NULL

---- 可以看到，在真值表中，除NULL AND FALSE 结果为FALSE、NULL OR TRUE 结果为TRUE 以外，其它结果均为NULL。

---- 虽然在where 条件中，Oracle 认为结果为NULL 的WHERE 条件为FALSE，但在条件表达式中NULL 不同于FALSE。例如在NOT （ NULL AND FALSE ）和NOT （ NULL AND NULL ）二者中仅有一处FALSE 和TRUE 的区别，但NOT （ NULL AND FALSE ）的结果为 TRUE，而NOT （ NULL AND NULL ）的结果为NULL。

---- 下面举例说明空值和逻辑操作符的用法：

SQL > select * from emp where not comm=null and comm!=0;
no rows selected
SQL > select * from emp where not ( not comm=null and comm!=0 );
EMPNO ENAME  JOB      MGR  HIREDATE  SAL  COMM  DEPTNO
 ---------- --------- --------- --------- ---------
7844 TURNER SALESMAN  7698 08-SEP-81 1500   0     30

---- 第一个Select 语句，条件"not comm=null and comm!=0" 等价于NULL AND COMM!=0。对于任意一行，如果COMM 为不等于0 的数值，条件等价于NULL AND TRUE，结果为NULL；如果COMM 等于0，条件等价于NULL AND FALSE，结果为FALSE。所以，最终结果不返回行。

---- 第二个Select 语句的条件为第一个Select 语句条件的" 非"（NOT），对于任意一行，如果COMM 为不等于0 的数值，条件等价于NOT NULL，结果为NULL；如果COMM 等于0，条件等价于NOT FALSE，结果为TRUE。所以，最终结果返回行COMM 等于0 的行。

---- 2. 空值和比较操作符

---- （1）IS [NOT] NULL：是用来测试空值的唯一操作符（见" 空值的测试"）。

（2）=、!=、>=、<=、>、<
SQL >select ename,sal,comm from emp where sal >comm;
ENAME            SAL      COMM
---------- --------- ---------
ALLEN           1600       300
WARD            1250       500
TURNER          1500         0

---- sal 或comm 为空值的行，sal>comm 比较结果为NULL，所以凡是sal 或comm 为空值的行都没有返回。

---- （3）IN 和NOT IN 操作符

SQL >select ename,mgr from emp where mgr in (7902,NULL);
ENAME            MGR
---------- ---------
SMITH           7902

---- 在上述语句中，条件"mgr in (7902,NULL)" 等价于mgr=7902 or mgr=NULL。对于表EMP 中的任意一行，如果mgr 为NULL，则上述条件等价于NULL OR NULL，即为NULL；如果mgr 为不等于7902 的数值，则上述条件等价于FALSE OR NULL，即为NULL；如果mgr 等于7902，则上述条件等价于TRUE OR NULL，即为TRUE。所以，最终结果能返回mgr 等于7902 的行。

SQL >select deptno from emp where deptno not in (''''10'''',NULL);
no rows selected

---- 在上述语句中，条件"deptno not in (''''10'''',NULL)" 等价于deptno!=''''10'''' and deptno!=NULL，对于EMP 表中的任意一行，条件的结果只能为NULL 或FALSE，所以不返回行。

---- （4）any,some

SQL >select ename,sal from emp where sal > any(3000,null);
ENAME            SAL
---------- ---------
KING            5000

---- 条件"sal > any(3000,null)" 等价于sal >3000 or sal >null。类似前述（3）第一句，最终结果返回所有sal >3000 的行。

---- （5）All

SQL >select ename,sal from emp where sal > all(3000,null);
no rows selected

---- 条件"sal> all(3000,null)" 等价于sal >3000 and sal >null, 结果只能为NULL 或FALSE，所以不返回行。

---- （6）（not）between

SQL >select ename,sal from emp where sal between null and 3000;
no rows selected

---- 条件"sal between null and 3000" 等价于sal >=null and sal< =3000, 结果只能为NULL 或FALSE，所以不返回行。

SQL >select ename,sal from emp where sal not between null and 3000;
ENAME            SAL
---------- ---------
KING            5000

---- 条件"sal not between null and 3000" 等价于sal3000, 类似前述（3）的第一句，结果返回sal>3000 的行。

---- 下表为比较操作符和空值的小结：

---- 比较操作符

---- 表达式（例：A、B 是NULL、C=10）

---- 结果

IS NULL、IS NOT NULL
A IS NULL
TRUE

A IS NOT NULL
FALSE

C IS NULL
FALSE

C IS NOT NULL
TRUE
=、!=、>=、< =、>、< 
A = NULL
NULL

A > NULL
NULL

C = NULL
NULL

C > NULL
NULL
IN (=ANY)
A IN (10,NULL)
NULL

C IN (10,NULL)
TRUE

C IN (20,NULL)
NULL
NOT IN 
( 等 价 于 ！=ALL)
A NOT IN (20,NULL)
NULL

C NOT IN (20,NULL)
FALSE

C NOT IN (10,NULL)
NULL
ANY，SOME
A > ANY(5,NULL)
NULL

C > ANY(5,NULL)
TRUE

C > ANY(15,NULL)
NULL
ALL
A > ALL(5,NULL)
NULL

C > ALL(5,NULL)
NULL

C > ALL(15,NULL)
FALSE
(NOT)BETWEEN
A BETWEEN 5 AND NULL
NULL

C BETWEEN 5 AND NULL
NULL

C BETWEEN 15 AND NULL
FALSE

A NOT BETWEEN 5 AND NULL
NULL

C NOT BETWEEN 5 AND NULL
NULL

C NOT BETWEEN 15 AND NULL
TRUE

---- 3、空值和算术、字符操作符

---- （1）算术操作符：空值不等价于0，任何含有空值的算术表达式其运算结果都为空值，例如空值加10 为空值。

---- （2）字符操作符||：因为ORACLE 目前处理零个字符值的方法与处理空值的方法相同（日后的版本中不一定仍然

[1] [2] 下一页