oracle 对null值的处理

今天对oracle数据库中的null值进行了简单的总结。

NULL其实是数据库中特有的类型，它的特殊性使得在查询、处理、比较NULL值时和其他数据与众不同。当oracle数据库中某个列为null时，则这个列的值是未知的，是不确定的，既然是未知的，那么就存在无数种可能。因此，null并不是一个确定的值。

由于null存在着无数的可能，因此，除了对null的is null，is not null以外，对null的任何操作（=、!=、>、<、>=、<=、+、-、*、/）得到的结果仍然是null（也就是未知）。

那么在null的情况下，对null进行逻辑运算会是个什么情况呢？由于要考虑到null值的情况，所以布尔运算值从原来的true，false这两个值，变成了现在的三个值:true,false,null。在oracle 10g 的环境下面，对null进行了逻辑运算，得到了以下结果：null AND null、null OR null、null AND true和null OR false的值都是未知的，这些的结果仍然是null。但是为什么 null and false（false）,null or true（true）会得到一个确定的值呢？我们从null的概念考虑，由于null是未知，但是目前null的值就是布尔类型了，因此null只有可能是布尔类型中true或者false中的一个。根据true and false 和 false and false的结果全是false，也就是说不管null的值是true或者是false那么它与false进行and运算得到的结果仍然是false。同样的道理我们可以推算出null与true进行or运算时，得到的结果仍然是true。

在这里我们看一下null值在查询语句中where条件中的情况。

create table test_null(test_name varchar2(10),test_type varchar2(10));

insert into test_null values('test1','table');

insert into test_null values('test2','table');

insert into test_null values('test3','table');

insert into test_null values('t1','table');

commit;

 

SQL> select * from test_null;

TEST_NAME  TEST_TYPE
   ---------- ----------
   test1      table
   test2      table
   test3      table
   t1         table

 

SQL> select * from test_null where test_name in('test1','test2',null);

TEST_NAME  TEST_TYPE
    ---------- ----------
    test1      table
    test2      table

对test_name in ('test1','test2',null),可以这样理解，test_name = 'test1' or test_name = 'test2' or test_name = null;这时我们可以看成这样： TRUE OR TRUE OR NULL，根据上面的经验我们得到的结果是true（也就是有结果符合本条件）；

 

 

SQL> select * from test_null where test_name not in ('test1','test2',null);

未选定行

对test_name not in('test1','test2',null)，可以这样理解，test_name = 'test1' and test_name = 'test2' and test_name = null;这时我们可以看成这样：true and true and null得到的结果为null(也就是没有结果符合)。

 

null的not运算

既然我们有的时候可以把null看成是布尔类型，也就是可以存在not的运算了。下面我们看看对null值进行not的运算会是什么样的结果？

在pl/sql运算中我们得到的结果是：not null得到的结果仍然是null（由于null表示未知，而增加一个not后，并不能是null值成为一个确定的值，这里对null进行的是布尔操作）。

 

null和''的比较

以前我认为''等价于null，但是有些人喜欢钻牛角尖，所以我改一下说法，空字符串''是NULL的字符类型的表现格式,也许有人会认为,NULL就是NULL,本身没有类型的一说，但是我认为,NULL还是有类型的，只不过不同类型的NULL都用相同的关键字NULL来表示。而且,NULL本身也可以转化为任意类型的数据，因此给人的感觉是NULL没有数据类型。其实NULL不但有数据类型，还有默认的数据类型，那就是字符类型。不过上面说的这个默认的数据类型是在极限的情况下测试出来的，如果只是给出一个NULL,那么它是可以代表任意的类型的。

证明空字符串就是NULL是很容易:

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

         1
    ----------
             1

SQL> select dump(''),dump(null) from dual;

DUMP DUMP
    ---- ----
    NULL NULL

任意一个都足以证明空字符串’’就是null。

 

    这时我们会认为既然’’就是null，那么为什么没有’’ is ‘’的语句呢？原因就是is null是oracle 的语句，而’’ is ‘’不是oracle的语句，在编译期间就被oracle 的sql分析器给拦截了，根本就不能在运行期间给运行了。
      那么我为什么还要说’’是NULL的字符表示形式呢？因为’’和NULL还确实不完全一样，对于NULL来说，它表示了各种数据类型的NULL值。而对于空字符串’’来说，虽然它也具有NULL的可以任意转化为其他任何数据类型的特点，但是无论是从形式上还是从本质上它都表现出了字符类型的特点。例如：

      Create or replace package p_test as

           Function test_return(test_in number) return varchar2;

           Function test_return(test_in varchar2) return varchar2;

      End;
select p_test.test_return(‘’) from dual; 正确

select p_test.test_return(null) from dual; 错误

第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 'F_RETURN' 声明与此次调用相匹配。

从这一点上，我们就看出了’’已经具备了数据类型，所以将’’表述为空字符串是NULL的字符类型表现形式。

 

    看下面这个结果

    SQL> select null||'A'||'B'||null from dual;

    NULL

    ----

    AB

为什么用||操作符得到的结果不是null呢？原因就是null在oracle中的存储形式，下面我们就来看一下null字符在oracle中的存储形式，Oracle在存储数据时，先是存储这一列的长度，然后存储列数据本身。而对于NULL，只包含一个FF，没有数据部分。简单的说，Oracle用长度FF来表示NULL。由于Oracle在处理的数据存储的时候尽量避免0的出现，因此，认为这里FF表示的是长度为0也是有一定道理的。或者从另一方面考虑，NULL只有一个长度，而没有数据部分。