论oracle中空值问题

专业：

班级：

姓名：

学号：

1.问题提出

用Oracle做数据查询，返回结果为空值，这是为什么？

2.空值特点及其生成

2.1空值的特点

1)等价于没有任何值。

2)与 0、空字符串或空格不同。

3)在where条件中, Oracle认为结果为NULL的条件为FALSE，带有这样条件的select 语

句不返回行，并且不返回错误信息。但NULL和FALSE是不同的。

4)排序时比其他数据都大。

5)空值不能被索引。

2.2空值的生成

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

3.空值的理论

3.1.空值和逻辑操作符

（1）逻辑操作符、表达式及结果

AND

NULL AND TRUE

NULL

NULL AND FALSE

FALSE

NULL AND NULL

NULL

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。

（2）举例说明空值和逻辑操作符的用法：

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的行。

3.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”等价于sal;3000,类似前述（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 AN

D NULL

NULL

C BETWEEN 15 AN

D NULL

FALSE

A NOT BETWEEN 5 AND NULL

NULL

C NOT BETWEEN 5 AN

D NULL

NULL

C NOT BETWEEN 15 AN

D NULL

TRUE

3.2. 空值和算术、字符操作符

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

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

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 1900

WARD 7698 W ARD7698 1250 500 1750

JONES 7839 JONES7839 2975

MARTIN 7698 MARTIN7698 1250 1400 2650

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均为空值。

4.解决方案

用NVL( string1, replace_with) 来解决这个问题。

NVL函数的功能是：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数的都为NULL ，则返回NULL。通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值

select nvl(sum(t.dwxhl),1) from tb_jhde t where zydm=-1

这里关心的nvl的用法，nvl（arg,value）代表如果前面的arg的值为null那么返回的值为后面的value

如: NVL(a,b)就是判断a是否是NULL,如果不是返回a的值，如果是返回b的值通过查询获得某个字段的合计值，如果这个值位null将给出一个预设的默认值

Oracle下Nvl函数nvl( ) 函数从两个表达式返回一个非null 值。

语法

NVL(eExpression1, eExpression2)

参数

eExpression1, eExpression2

如果eExpression1 的计算结果为null 值，则NVL( ) 返回eExpression2。如果eExpression1 的计算结果不是null 值，则返回eExpression1。eExpression1 和eExpression2 可以是任意一种数据类型。如果eExpression1 与eExpression2 的结果皆为null 值，则NVL( ) 返回.NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或null 值

说明

在不支持null 值或null 值无关紧要的情况下，可以使用NVL( ) 来移去计算或操作中的null 值。select nvl(https://www.360docs.net/doc/5c10320076.html,,'空得') as name from student a join school b on a.ID=b.ID 注意：两个参数得类型要匹配问：什么是NULL？答：在我们不知道具体有什么数据的时候，也即未知，可以用NULL，我们称它为空，ORACLE中，含有空值的表列长度为零。ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：

1、主键字段（primary key），

2、定义时已经加了NOT NULL限制条件的字段

5.空值的其它特性

1)等价于没有任何值、是未知数。

2)NULL与0、空字符串、空格都不同。

3)对空值做加、减、乘、除等运算操作，结果仍为空。

4)NULL的处理使用NVL函数。

5)比较时使用关键字用“is null”和“is not null”。

6)空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(*)中，用nvl(列

名,0)处理后再查。

7)排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最

后。

8)使用方法：

SQL> select 1 from dual where null=null; 没有查到记录

SQL> select 1 from dual where null=''; 没有查到记录

SQL> select 1 from dual where ''=''; 没有查到记录

SQL> select 1 from dual where null is null; 1 --------- 1

SQL> select 1 from dual where nvl(null,0)=nvl(null,0); 1 --------- 1

对空值做加、减、乘、除等运算操作，结果仍为空。

SQL> select 1+null from dual;

SQL> select 1-null from dual;

SQL> select 1*null from dual;

SQL> select 1/null from dual; 查询到一个记录. 注：这个记录就是SQL语句中的那个null 设置某些列为空值update table1 set 列1=NULL where 列1 is not null;

现有一个商品销售表sale，表结构为：month char(6)--月份sellnumber(10,2)--月销售金额create table sale (month char(6),sell number); insert into sale values('200001',1000); insert into sale values('200002',1100); insert into sale values('200003',1200); insert into sale values('200004',1300); insert into sale values('200005',1400); insert into sale values('200006',1500); insert into sale values('200007',1600); insert into sale values('200101',1100); insert into sale values('200202',1200); insert into sale values('200301',1300); insert into sale values('200008',1000); insert into sale(month) values('200009'); （注意：这条记录的sell值为空）

commit; 共输入12条记录

SQL> select * from sale where sell like '%';

MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000

9)查询到11记录. 结果说明：查询结果说明此SQL语句查询不出列值为NULL的字段此

时需对字段为NULL的情况另外处理。

SQL> select * from sale where sell like '%' or sell is null;

SQL> select * from sale where nvl(sell,0) like '%';

MONTH SELL ------ --------- 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300 200008 1000 200009 查询到12记录. Oracle的空值就是这么的用法，我们最好熟悉它的约定，以防查出的结果不正确

6.结论、

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

错误！未找到目录项。