2024年1月20日发(作者：)

oracle 中 的 空 值技巧（Oracle中的空值技巧）

Talking about empty values in Oracle

In a database, null values are used to indicate the actual value

is unknown or meaningless. In a table, if a column in a row has

no value, it is called null (NULL). Any column of a data type

can have null values whenever it does not use a KEY (NOT NULL)

or a primary key (PRIMARY,) integrity limit. In practical

applications, if you ignore the presence of null values, you

will cause unnecessary trouble.

- for example, in the following table (EMP), employee name

(ENAME) for KING, because KING is the highest officials

(PRESIDENT), he did not charge (MGR), so the MGR is a null value.

Since not all employees have COMM, the column COMM allows for

a null value. All other lines other than 300, 500, 1400, 0, COMM

are null values.

EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO

----------------------------------------------------

7369 SMITH CLERK 7902 17-DEC-80 80020

7499 ALLEN SALESMAN 7698 20-FEB-81 160030030

7521 WARD SALESMAN 7698 22-FEB-81 125050030

7566 JONES MANAGER 7839 02-APR-81 297520

7654 MARTIN SALESMAN 7698 28-SEP-81 1250140030

7698 BLAKE MANAGER 7839 01-MAY-81 285030

7782 CLARK MANAGER 7839 09-JUN-81 245010

7788 SCOTT ANALYST 7566 09-DEC-82 300020

7839 KING PRESIDENT 17-NOV-81 500010

7844 TURNER SALESMAN 7698 08-SEP-81 1500030

7876 ADAMS CLERK 7788 12-JAN-83 110020

7900 JAMES CLERK 7698 03-DEC-81 95030

7902 FORD ANALYST 7566 03-DEC-81 300020

7934 MILLER CLERK 7782 23-JAN-82 130010

This article will take the above EMP table as an example to

discuss some of the features of null values in everyday

applications.

---- the generation and characteristics of null value

---- the generation of 1. null values

If a column has no non empty (NOT NULL) integrity constraints,

then the default value is null, i.e. if you insert a row when

the column value is not specified, the value is null.

INSERT - use the SQL statement to insert the line, those

involved, its value is null; related to the column, if its value

is null, the insertion of NULL can be used to express (for

character columns, can also be used to represent '').

Example: insert a row whose EMPNO is 1, ENAME is'JIA', SAL is

10000, and job and comm are null values.

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家1000

----可以看到新插入的一行，除工作和通讯为空值外，经理、姓名和雇佣、deptno三列由于插入时未涉及，也为空值。

----使用SQL语句更新来修改数据，空值可用空来表示（对于字符型的列，也可以用”来表示例）：

SQL >更新EMP组ename = null，萨尔= null，empno = 1；

---- 2。空值的特点

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

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

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

---- *在哪里条件中，Oracle认为结果为空的条件为虚假，带有这样条件的选择语句不返回行，并且不返回错误信息。但空和假是不同的。

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

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

----二、空值的测试

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

----例如查询EMP表中经理为空的行：

SQL SELECT * FROM EMP在经理> =”；

没有行选择

SQL SELECT * FROM EMP在经理> = null；

没有行选择

SQL SELECT * FROM EMP在经理>>是空的；

EMPNO ename工作经理姓名和雇佣萨尔通讯部门

---------- --------- --------- --------- ---------

7839王校长17-nov-81 5000 10

----第1、2句写法不妥，哪里条件结果为空，不返回行。第三句正确，返回经理为空值的行。

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

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

----逻辑操作符

----表达式

----结果

和

空的和真实的

无效的

空、假

错误的

空和空

无效的

或

空的或真实的

真的

空或假

无效的

null或空

无效的

不

不为空

无效的

----可以看到，在真值表中，除空、假结果为假、空或真结果为真以外，其它结果均为空。

----虽然在哪里条件中，Oracle认为结果为空的哪里条件为虚假，但在条件表达式中空不同于假。例如在不（空假）和不（为空）二者

中仅有一处假和真的区别，

但不（空假）的结果为真不而（为空）的结果为空。

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

警告：SELECT * FROM emp不通讯= null和通讯！= 0；

没有行选择

警告：SELECT * FROM emp不（不通信= null和通讯！= 0）；

EMPNO ename工作经理姓名和雇佣萨尔通讯部门

---------- --------- --------- --------- ---------

7844 7698 1500 0 30 08-sep-81特纳的推销员

----第一个选择语句，条件”不是通信= null和通讯！= 0”等价于空和通讯！= 0。对于任意一行，如果通讯为不等于0的数值，条件等价于为真，结果为无效；如果通讯等于0，条件等价于空、假、结果为假。所以，最终结果不返回行。

----第二个选择语句的条件为第一个选择语句条件的”非”（不），对于任意一行，如果通讯为不等于0的数值，条件等价于不空，结果为无效；如果通讯等于0、条件等价于不假，结果为真实。所以，最终结果返回行通讯等于0的行。

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

----（1）是[不]零：是用来测试空值的唯一操作符（见”空值的测

试”）。

（2）=！=、>、<、>、<

SQL >选择ename，萨尔从EMP在萨尔>通讯通讯；

ENAME SAL通讯

---------- --------- ---------

艾伦1600 300

1250号病房500

特纳1500 0

----萨尔或通讯为空值的行，萨尔>通讯比较结果为空，所以凡是萨尔或通讯为空值的行都没有返回。

----（3）在和不操作符

SQL >选择ename，经理从EMP在经理（7902，null）；

一个经理

---------- ---------

史密斯7902

----在上述语句中，条件”经理（7902，null）”等价于MGR = 7902或经理=零。对于表EMP中的任意一行，如果经理为空，则上述条件

等价于null或空，即为无效；如果经理为不等于7902的数值，则上述条件等价于虚假或无效，即为无效；如果经理等于7902，则上述条件等价于真或空，即为真实。所以，最终结果能返回经理等于7902的行。

SQL SELECT DEPTNO从EMP表>>在不在（10’，空）；

没有行选择

----在上述语句中，条件”表不在（10’，空）”等价于deptno！=

10’和表！= null，对于EMP表中的任意一行，条件的结果只能为空或虚假，所以不返回行。

----（4）任何，一些

SQL >选择ename，萨尔从EMP在萨尔>任何（3000，null）；

ENAME SAL

---------- ---------

王5000

----条件”萨尔>任何（3000，null）”等价于萨尔> 3000或萨尔>空。类似前述（3）第一句，最终结果返回所有工资超过3000的行。

----（5）全部

SQL >选择ename，萨尔从EMP在萨尔>所有（3000，null）；

没有行选择

----条件”萨尔>所有（3000，null）”等价于萨尔> 3000和萨尔>无效，结果只能为空或虚假，所以不返回行。

----（6）（不）

SQL >选择ename，萨尔从EMP在萨尔零和3000之间；

没有行选择

----条件”萨尔为3000”等价于萨尔> = null和萨尔<= 3000之间，结果只能为空或虚假，所以不返回行。

SQL >选择ename，萨尔从EMP在萨尔不是零和3000之间；

ENAME SAL

---------- ---------

王5000

----条件”萨尔之间不为3000”等价于sal3000，类似前述（3）的第一句，结果返回工资超过3000的行。

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

----比较操作符

----表达式（例：一、B是空、c = 10）

----结果

是NULL，不是空的。

一个是空的

真的

A不是空的。

错误的

C是空的

错误的

c不是空的。

真的

=、！=、>、<、>、

= null

无效的

一个大于零

无效的

C = null

<

无效的

C、空

无效的

在（=）

a（10，空）

无效的

c（10，null）

真的

c（20，null）

无效的

不在

（等价于！=所有）

不在（20，空）

无效的

c不在（20，空）

错误的

c不在（10，空）

无效的

有一些

任何（5，空）

无效的

任何（5，空）

真的

任何（15，空）

无效的

所有

>全部（5，空）

无效的

全部（5，空）

无效的

全部（15，空）

错误的

（不）之间

a在5和null之间

无效的

c在5和null之间

无效的

c在15和null之间

错误的

a不在5和null之间。

无效的

c不在5和null之间

无效的

c不在15和null之间

真的

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

----（1）算术操作符：空值不等价于0，任何含有空值的算术表达

式其运算结果都为空值，例如空值加10为空值。

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

|，空值等价于零个字符值。例：

SQL >选择ename，经理，雇员| | MGR，萨尔，通讯，萨尔+通讯从EMP；

一个经理不| |经理萨尔萨尔+ COMM通信

---------- --------- ------------- --------- ---------

---------

史密斯7902 smith7902 800

艾伦7698 allen7698 1600 300 1900

7698 1250 500 1750 ward7698病房

jones 7839 jones7839 2975

martin 7698 martin7698 1250 1400.

blake 7839 blake7839 2850

clark 7839 clark7839 2450

scott 7566 scott7566 3000

king king 5000

turner 7698 turner7698 1500 0 1500

adams 7788 adams7788 1100

james 7698 james7698 950

ford 7566 ford7566 3000

miller 7782 miller7782 1300

- - - - 我 们 可 以 看 到, 凡mgr 为 空 值 的, ename | | mgr

结 果 等 于ename; 凡 是comm 为 空 值 的 行, sal + comm 均 为

空 值.

- - - - 四、 空 值 和 函 数

- - - - 1. 空 值 和 度 量 函 数

- - - - 对 于 度 量 函 数, 如 果 给 定 的 参 数 为 空 值, 则

其 (nvl、translate 除 外) 返 回 值 为 空 值. 如 下 例 中 的abs (comm), 如 果comm 为 空 值, abs (comm) 为 空 值.

sql sub select ename, sal, comm, abs (comm) from emp where sal

< 1500;

ename sal comm ((comm)

---------- --------- --------- ---------

smith 800

ward 1 500

martin 1250 1400 1400

adams 1100

james 950

miller 1300

- - - - 2. 空 值 和 组 函 数

- - - - 组 函 数 忽 略 空 值. 在 实 际 应 用 中, 根 据 需 要

可 利 用nvl 函 数 用 零 代 替 空 值. 例:

sql "select count (comm), sum (comm), avg (comm) from emp;

count (comm) sum (comm) avg (comm)

----------- --------- ---------

4 2200 550

sql "select count (nvl (t, 0)), sum (nvl (t, 0)), avg (nvl (t,

0))

from emp;

count (nvl (t, 0) sum (nvl (t, 0)), avg (nvl (t, 0))

------------------ ---------------- ----------------

14 2200 157.14286

- - - - 第 一 个select 语 句 忽 略comm 为 空 值 的 行, 第 二

个select 语 句 使 用nvl 函 数 统 计 了 所 有 的comm, 所 以

它 们 统 计 的 个 数、 平 均 值 都 不 相 同. 另 外 需 要 注

意 的 是, 在 利 用 组 函 数 进 行 数 据 处 理 时, 不 同 的

写 法 具 有 不 同 的 不 同 含 义, 在 实 际 应 用 中 应 灵

活 掌 握. 例 如:

sql sub select deptno, sum (sal), sum (comm)

sum (sal + comm), sum (sal) + sum (comm), sum (nvl (sal, 0) +

nvl (t, 0))

from emp

group by deptno;

deptno sum (sal), sum (comm) sum (sal + comm), sum (sal)

+ sum (comm) sum (nvl (sal, 0) + nvl (t, 0))

--------- --------- --------- ------------- -------

10 8750 8750

20 10875 10875

30 9400 2200 7800 11600 11600

----可以看到总和（SAL +通讯）、总和（SAL）+和（通信）、总和（NVL（Sal，0）+ NVL（通讯，0））的区别：总和（SAL +通讯）为先加然后计算各行的和，如果萨尔、通讯中有一个为空，则该行忽略不计；和（SAL）+和（通信）为先计算各行的合计然后再加，萨尔、通讯中的空都忽略不计，但如果总和（SAL）、总和（通讯）二者的结果之中有一个为空，则二者之和为无效；在总和（NVL（Sal，0）+ NVL（通讯，0））里，萨尔、通讯中的空按0处理。

----五、空值的其它特性

---- 1。空值在排序时大于任何值例如：

SQL >选择姓名，通讯，从EMP表30按通讯；

ename通讯

---------- ---------

特纳0

艾伦300

500号病房

马丁1400

布莱克

詹姆斯

---- 2。空值不能被索引。虽然在某列上建立了索引，但是对该列的空值查询来说，因为空值没有被索引，所以不能改善查询的效率。例如下面的查询不能利用在经理列上创建的索引。

SQL >选择ename从EMP，经理是空的；

ename

----------

国王

----另外正是因为空值不被索引，所以可在含有空值的列上建立唯一性索引（唯一索引）。例如，可以在EMP表的通讯列上建立唯一性索引：

SQL >创建唯一索引emp_comm EMP（通讯）；

建立索引。