漫 谈oracle 中 的 空 值

1、漫 谈oracle 中 的 空 值 摘自：计算机世界日报河 北 省 统 计 局 贾 书 民 - 在 数 据 库 中， 空 值 用 来 表 示 实 际 值 未 知 或 无 意 义 的 情 况。 在 一 个 表 中， 如 果 一 行 中 的 某 列 没 有 值， 那 么 就 称 它 为 空 值（NULL）。 任 何 数 据 类 型 的 列, 只 要 没 有 使 用 非 空（NOT NULL） 或 主 键（PRIMARY KEY） 完 整 性 限 制， 都 可 以 出 现 空 值。 在 实 际 应 用 中， 如 果 忽 略 空 值 的 存 在， 将 会 造 成 造 成 不 必 要 的 麻 烦。 -

2、例 如， 在 下 面 的 雇 员 表（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

3、 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 09-DEC-

4、82 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 12-JAN-83 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 10- 本 文 将 以 上 述EMP 表 为 例， 具 体 讨 论 一 下 空 值 在 日 常 应 用 中

5、 所 具 有 的 一 些 特 性。 - 一、 空 值 的 生 成 及 特 点 - 1. 空 值 的 生 成 - 如 果 一 列 没 有 非 空（NOT NULL） 完 整 性 限 制， 那 么 其 缺 省 的 值 为 空 值， 即 如 果 插 入 一 行 时 未 指 定 该 列 的 值， 则 其 值 为 空 值。 - 使 用SQL 语 句INSERT 插 入 行， 凡 未 涉 及 到 的 列， 其 值 为 空 值； 涉 及 到 的 列， 如 果 其 值 确 实 为 空 值， 插 入 时 可 以 用NULL 来 表 示（ 对 于 字 符 型 的 列， 也 可 以 用 来 表 示）。 - 例： 插

6、 入 一 行， 其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 三 列

7、由 于 插 入 时 未 涉 及， 也 为 空 值。 - 使 用SQL 语 句UPDATE 来 修 改 数 据， 空 值 可 用NULL 来 表 示（ 对 于 字 符 型 的 列， 也 可 以 用 来 表 示）。 例: SQL update emp set ename=NULL,sal=NULL where empno=1;- 2. 空 值 的 特 点 - 空 值 具 有 以 下 特 点： - * 等 价 于 没 有 任 何 值。 - * 与 0、 空 字 符 串 或 空 格 不 同。 - * 在where 条 件 中, Oracle 认 为 结 果 为NULL 的 条 件 为FALSE， 带

8、有 这 样 条 件 的select 语 句 不 返 回 行， 并 且 不 返 回 错 误 信 息。 但NULL 和FALSE 是 不 同 的。 - * 排 序 时 比 其 他 数 据 都 大。 - * 空 值 不 能 被 索 引。 - 二、 空 值 的 测 试 - 因 为 空 值 表 示 缺 少 数 据， 所 以 空 值 和 其 它 值 没 有 可 比 性， 即 不 能 用 等 于、 不 等 于、 大 于 或 小 于 和 其 它 数 值 比 较， 当 然 也 包 括 空 值 本 身（ 但 是 在decode 中 例 外， 两 个 空 值 被 认 为 是 等 价）。 测 试 空 值 只 能 用

9、比 较 操 作 符IS NULL 和IS NOT NULL。 如 果 使 用 带 有 其 它 比 较 操 作 符 的 条 件 表 达 式， 并 且 其 结 果 依 赖 于 空 值， 那 么 其 结 果 必 定 是NULL。 在where 条 件 中，Oracle 认 为 结 果 为NULL 的 条 件 为FALSE， 带 有 这 样 条 件 的select 语 句 不 返 回 行， 也 不 返 回 错 误 信 息。 - 例 如 查 询EMP 表 中MGR 为NULL 的 行： SQL select * from emp where mgr=; no rows selectedSQL selec

10、t * from emp where mgr=null; no rows selectedSQL 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. 空 值 和 逻 辑 操 作 符 - 逻 辑 操 作 符

11、 - 表 达 式 - 结 果 ANDNULL AND TRUENULLNULL AND FALSEFALSENULL AND NULLNULLORNULL OR TRUETRUENULL OR FALSENULLNULL OR NULLNULLNOTNOT NULLNULL- 可 以 看 到， 在 真 值 表 中， 除NULL AND FALSE 结 果 为FALSE、NULL OR TRUE 结 果 为TRUE 以 外， 其 它 结 果 均 为NULL。 - 虽 然 在where 条 件 中，Oracle 认 为 结 果 为NULL 的WHERE 条 件 为FALSE， 但 在 条 件 表

12、达 式 中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 selectedSQL select * f

13、rom 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 等

14、于0， 条 件 等 价 于NULL AND FALSE， 结 果 为FALSE。 所 以， 最 终 结 果 不 返 回 行。 - 第 二 个Select 语 句 的 条 件 为 第 一 个Select 语 句 条 件 的 非（NOT）， 对 于 任 意 一 行， 如 果COMM 为 不 等 于0 的 数 值， 条 件 等 价 于NOT NULL， 结 果 为NULL； 如 果COMM 等 于0， 条 件 等 价 于NOT FALSE， 结 果 为TRUE。 所 以， 最 终 结 果 返 回 行COMM 等 于0 的 行。 - 2. 空 值 和 比 较 操 作 符 - （1）IS NOT NUL

15、L： 是 用 来 测 试 空 值 的 唯 一 操 作 符（ 见 空 值 的 测 试）。 （2）=、!=、=、select ename,sal,comm from emp where sal comm;ENAME SAL COMM- - -ALLEN 1600 300WARD 1250 500TURNER 1500 0- sal 或comm 为 空 值 的 行，salcomm 比 较 结 果 为NULL， 所 以 凡 是sal 或comm 为 空 值 的 行 都 没 有 返 回。 - （3）IN 和NOT IN 操 作 符 SQL select ename,mgr from emp where

16、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。 所

17、以， 最 终 结 果 能 返 回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 a

18、ny(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， 所 以 不 返 回 行。

19、- （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 salselect ename,sal from emp where sal not between null and 3000;ENAME SAL- -KING 5000- 条 件sal not between null and 3000 等 价 于sal3000, 类 似 前 述（3） 的 第 一 句，

20、 结 果 返 回sal3000 的 行。 - 下 表 为 比 较 操 作 符 和 空 值 的 小 结： - 比 较 操 作 符 - 表 达 式（ 例：A、B 是NULL、C=10） - 结 果 IS NULL、IS NOT NULLA IS NULLTRUEA IS NOT NULLFALSEC IS NULLFALSEC IS NOT NULLTRUE=、!=、=、 NULLNULLC = NULLNULLC NULLNULLIN (=ANY)A IN (10,NULL)NULLC IN (10,NULL)TRUEC IN (20,NULL)NULLNOT IN ( 等 价 于 ！=ALL)

21、A NOT IN (20,NULL)NULLC NOT IN (20,NULL)FALSEC NOT IN (10,NULL)NULLANY，SOMEA ANY(5,NULL)NULLC ANY(5,NULL)TRUEC ANY(15,NULL)NULLALLA ALL(5,NULL)NULLC ALL(5,NULL)NULLC ALL(15,NULL)FALSE(NOT)BETWEENA BETWEEN 5 AND NULLNULLC BETWEEN 5 AND NULLNULLC BETWEEN 15 AND NULLFALSEA NOT BETWEEN 5 AND NULLNULLC N

22、OT BETWEEN 5 AND NULLNULLC NOT BETWEEN 15 AND NULLTRUE- 3、 空 值 和 算 术、 字 符 操 作 符 - （1） 算 术 操 作 符： 空 值 不 等 价 于0， 任 何 含 有 空 值 的 算 术 表 达 式 其 运 算 结 果 都 为 空 值， 例 如 空 值 加10 为 空 值。 - （2） 字 符 操 作 符|： 因 为ORACLE 目 前 处 理 零 个 字 符 值 的 方 法 与 处 理 空 值 的 方 法 相 同（ 日 后 的 版 本 中 不 一 定 仍 然 如 此）， 所 以 对 于|， 空 值 等 价 于 零 个 字

23、符 值。 例： SQL select ename,mgr,ename|mgr,sal,comm,sal+comm from emp;ENAME MGR ENAME|MGR SAL COMM SAL+COMM- - - - - -SMITH 7902 SMITH7902 800 ALLEN 7698 ALLEN7698 1600 300 1900WARD 7698 WARD7698 1250 500 1750JONES 7839 JONES7839 2975 MARTIN 7698 MARTIN7698 1250 1400 2650BLAKE 7839 BLAKE7839 2850 CLARK

24、7839 CLARK7839 2450 SCOTT 7566 SCOTT7566 3000 KING KING 5000 TURNER 7698 TURNER7698 1500 0 1500ADAMS 7788 ADAMS7788 1100 JAMES 7698 JAMES7698 950 FORD 7566 FORD7566 3000 MILLER 7782 MILLER7782 1300 - 我 们 可 以 看 到， 凡mgr 为 空 值 的，ename|mgr 结 果 等 于ename； 凡 是comm 为 空 值 的 行，sal+comm 均 为 空 值。 - 四、 空 值 和 函 数

25、 - 1 空 值 和 度 量 函 数 - 对 于 度 量 函 数， 如 果 给 定 的 参 数 为 空 值， 则 其（NVL、TRANSLATE 除 外） 返 回 值 为 空 值。 如 下 例 中 的ABS（COMM）， 如 果COMM 为 空 值，ABS(COMM) 为 空 值。 SQL select ename,sal,comm,abs(comm) from emp where salselect count(comm),sum(comm),avg(comm) from emp;COUNT(COMM) SUM(COMM) AVG(COMM)- - - 4 2200 550SQL selec

26、t count(nvl(comm,0),sum(nvl(comm,0),avg(nvl(comm,0)from emp;COUNT(NVL(COMM,0) SUM(NVL(COMM,0) AVG(NVL(COMM,0)- - - 14 2200 157.14286- 第 一 个SELECT 语 句 忽 略COMM 为 空 值 的 行， 第 二 个SELECT 语 句 使 用NVL 函 数 统 计 了 所 有 的COMM， 所 以 它 们 统 计 的 个 数、 平 均 值 都 不 相 同。 另 外 需 要 注 意 的 是， 在 利 用 组 函 数 进 行 数 据 处 理 时， 不 同 的 写 法

27、 具 有 不 同 的 不 同 含 义， 在 实 际 应 用 中 应 灵 活 掌 握。 例 如： SQL select deptno,sum(sal),sum(comm), sum(sal+comm),sum(sal)+sum(comm),sum(nvl(sal,0)+nvl(comm,0) from empgroup by deptno; DEPTNO SUM(SAL) SUM(COMM) SUM(SAL+COMM) SUM(SAL)+SUM(COMM) SUM(NVL(SAL,0)+NVL(COMM,0)- - - - - 10 8750 8750 20 10875 10875 30 9400 2200 7800 11600 11600- 可 以