Oracle中的null

最近在 论坛 上经常看到，很多人提出和 NULL有关的问题。NULL其实是数据库中特有的类型，Oracle中很多容易出现的错误都是和NULL有关的。

打算简单的总结一下NULL的相关知识。

这一篇主要打算NULL的基础概念和由来，并介绍对NULL的操作的基本特点。

 

 

 

NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的值。

这是NULL的由来、也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概念推导出来。

判断一个字段是否为NULL，应该用IS NULL或IS NOT NULL，而不能用‘=’。对NULL的判断只能定性，既是不是NULL（IS NULL/IS NOT NULL），而不能定值。简单的说，由于NULL存在着无数的可能，因此两个NULL不是相等的关系，同样也不能说两个NULL就不相等，或者比较两个NULL的大小，这些操作都是没有意义，得不到一个确切的答案的。因此，对NULL的=、!=、>、<、>=、<=等操作的结果都是未知的，也就算说，这些操作的结果仍然是NULL。

同理，对NULL进行+、-、*、/等操作的结果也是未知的，所以也是NULL。

所以，很多时候会这样总结NULL，除了IS NULL、IS NOT NULL以外，对NULL的任何操作的结果还是NULL。

上面这句话总结的很精辟，而且很好记，所以很多时候人们只记得这句话，而忘了这句话是如何得到的。其实只要清楚NULL的真正含义，在处理NULL的时候就不会出错。

说了怎么多，来看一个经典的例子：

SQL> CREATE OR REPLACE PROCEDURE P1 (P_IN IN NUMBER) AS
2 BEGIN
3 IF P_IN >= 0 THEN
4 DBMS_OUTPUT.PUT_LINE('TRUE');
5 ELSE
6 DBMS_OUTPUT.PUT_LINE('FALSE');
7 END IF;
8 END;
9 /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE P2 (P_IN IN NUMBER) AS
2 BEGIN
3 IF P_IN < 0 THEN
4 DBMS_OUTPUT.PUT_LINE('FALSE');
5 ELSE
6 DBMS_OUTPUT.PUT_LINE('TRUE');
7 END IF;
8 END;
9 /

过程已创建。

上面两个过程是否是等价的？对于熟悉C或JAVA的开发人员来说，可能认为二者是等价的，但是在数据库中，则还要考虑到NULL的情况。

当输入为NULL时，可以看到上面两个过程不同的输出：

SQL> SET SERVEROUT ON
SQL> EXEC P1(NULL)
FALSE

PL/SQL 过程已 成功 完成。

SQL> EXEC P2(NULL)
TRUE

PL/SQL 过程已成功完成。

输入为NULL时，上面两个过程中的判断的结果都是一样的，不管是NULL >= 0还是NULL < 0结果都是未知，所以两个判断的结果都是NULL。最终，在屏幕上输出的都是ELSE后面跟的输出值。

由于NULL所具有的特殊性，在处理数据库相关问题时应该对NULL的情况额外考虑，否则很容易造成错误。

由于引入了NULL，在处理逻辑过程中一定要考虑NULL的情况。同样的，数据库中的布尔值的处理，也是需要考虑NULL的情况，这使得布尔值从原来的TRUE、FALSE两个值变成了TRUE、FALSE和NULL三个值。

下面是TRUE和FALSE两种情况进行布尔运算的结果：

AND操作：

AND	TRUE	FALSE
TRUE	TRUE	FALSE
FALSE	FALSE	FALSE

OR操作：

OR	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE

 

上面是熟悉的TRUE和FALSE两个值进行布尔运算的结果，如果加上一个NULL的情况会怎样？NULL的布尔运算是否会像NULL的算术运算那样结果都是NULL呢？下面通过一个过程来进行说明：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
3 V_BOOL1 T_BOOLEAN;
4 V_BOOL2 T_BOOLEAN;
5
6 PROCEDURE P(P_IN1 BOOLEAN, P_IN2 BOOLEAN, P_OPERATOR IN VARCHAR2) AS
7 V_RESULT BOOLEAN;
8 BEGIN
9 IF P_IN1 IS NULL THEN
10 DBMS_OUTPUT.PUT('NULL ');
11 ELSIF P_IN1 THEN
12 DBMS_OUTPUT.PUT('TRUE ');
13 ELSE
14 DBMS_OUTPUT.PUT('FALSE ');
15 END IF;
16
17 IF P_OPERATOR = 'AND' THEN
18 DBMS_OUTPUT.PUT('AND ');
19 V_RESULT := P_IN1 AND P_IN2;
20 ELSIF P_OPERATOR = 'OR' THEN
21 DBMS_OUTPUT.PUT('OR ');
22 V_RESULT := P_IN1 OR P_IN2;
23 ELSE
24 RAISE_APPLICATION_ERROR('-20000', 'INPUT PARAMETER P_OPERATOR ERROR');
25 END IF;
26
27 IF P_IN2 IS NULL THEN
28 DBMS_OUTPUT.PUT('NULL');
29 ELSIF P_IN2 THEN
30 DBMS_OUTPUT.PUT('TRUE');
31 ELSE
32 DBMS_OUTPUT.PUT('FALSE');
33 END IF;
34
35 IF V_RESULT IS NULL THEN
36 DBMS_OUTPUT.PUT(':NULL');
37 ELSIF V_RESULT THEN
38 DBMS_OUTPUT.PUT(':TRUE');
39 ELSE
40 DBMS_OUTPUT.PUT(':FALSE');
41 END IF;
42 DBMS_OUTPUT.NEW_LINE;
43 END;
44
45 BEGIN
46 V_BOOL1(1) := TRUE;
47 V_BOOL1(2) := FALSE;
48 V_BOOL1(3) := NULL;
49 V_BOOL2 := V_BOOL1;
50 FOR I IN 1..V_BOOL1.COUNT LOOP
51 FOR J IN 1..V_BOOL2.COUNT LOOP
52 P(V_BOOL1(I), V_BOOL2(J), 'AND');
53 P(V_BOOL1(I), V_BOOL2(J), 'OR');
54 END LOOP;
55 END LOOP;
56 END;
57 /
TRUE AND TRUE:TRUE
TRUE OR TRUE:TRUE
TRUE AND FALSE:FALSE
TRUE OR FALSE:TRUE
TRUE AND NULL:NULL
TRUE OR NULL:TRUE
FALSE AND TRUE:FALSE
FALSE OR TRUE:TRUE
FALSE AND FALSE:FALSE
FALSE OR FALSE:FALSE
FALSE AND NULL:FALSE
FALSE OR NULL:NULL
NULL AND TRUE:NULL
NULL OR TRUE:TRUE
NULL AND FALSE:FALSE
NULL OR FALSE:NULL
NULL AND NULL:NULL
NULL OR NULL:NULL

PL/SQL 过程已成功完成。

由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，这些的结果仍然是NULL。

那么为什么NULL AND FALSE和NULL OR TRUE得到了一个确定的结果呢？仍然从NULL的概念来考虑。NULL是未知的，但是目前NULL的类型是布尔类型，因此NULL只有可能是TRUE或者FALSE中的一个。

而根据前面的表格，TRUE AND FALSE和FALSE AND FALSE的结果都是FALSE，也就是说不管NULL的值是TRUE还是FALSE，它与FALSE进行AND的结果一定是FALSE。

同样的道理，TRUE AND TRUE和FALSE AND TRUE的结果都是TRUE，所以不管NULL取何值，NULL和TRUE的OR的结果都是TRUE。

AND操作图表变为：

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR操作图表变为：

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

最后，仍然来看一个例子：

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PLAN_TABLE TABLE
T TABLE
T1 TABLE
T2 TABLE
T3 TABLE
TEST TABLE
TEST1 TABLE
TEST_CORRUPT TABLE
T_TIME TABLE

已选择9行。

SQL> SELECT * FROM TAB WHERE TNAME IN ('T', 'T1', NULL);

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
T1 TABLE

SQL> SELECT * FROM TAB WHERE TNAME NOT IN ('T', 'T1', NULL);

未选定行

对于IN和NOT IN与NULL的关系前面并没有说明，不过可以对其进行简单的变形：

TNAME IN (‘T’, ‘T1’, NULL) < = > TNAME = ‘T’ OR TNAME = ‘T1’ OR TNAME = NULL

根据前面的结果，当查询到T或T1这两条记录时，WHERE条件相当于TRUE AND FALSE AND NULL，其结果是TRUE，因此返回了两条记录。

TNAME NOT IN (‘T’, ‘T1’, NULL) < = > TNAME != ‘T’ AND TNAME != ‘T1’ AND TNAME != NULL。

WHERE条件相当于TRUE AND TRUE AND NULL，或TRUE AND FALSE AND NULL，其最终结果是NULL或者FALSE，所以，查询不会返回记录。

对于TRUE和FALSE的NOT运算很简单，NOT TRUE=FALSE，NOT FALSE=TRUE，那么如果包含NULL的情况呢，首先还是用事实来说话：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
3 V_BOOL T_BOOLEAN;
4
5 PROCEDURE P(P_IN BOOLEAN) AS
6 V_RESULT BOOLEAN;
7 BEGIN
8 IF P_IN IS NULL THEN
9 DBMS_OUTPUT.PUT('NOT NULL');
10 ELSIF P_IN THEN
11 DBMS_OUTPUT.PUT('NOT TRUE');
12 ELSE
13 DBMS_OUTPUT.PUT('NOT FALSE');
14 END IF;
15
16 V_RESULT := NOT P_IN;
17
18 IF V_RESULT IS NULL THEN
19 DBMS_OUTPUT.PUT(':NULL');
20 ELSIF V_RESULT THEN
21 DBMS_OUTPUT.PUT(':TRUE');
22 ELSE
23 DBMS_OUTPUT.PUT(':FALSE');
24 END IF;
25 DBMS_OUTPUT.NEW_LINE;
26 END;
27
28 BEGIN
29 V_BOOL(1) := TRUE;
30 V_BOOL(2) := FALSE;
31 V_BOOL(3) := NULL;
32 FOR I IN 1..V_BOOL.COUNT LOOP
33 P(V_BOOL(I));
34 END LOOP;
35 END;
36 /
NOT TRUE:FALSE
NOT FALSE:TRUE
NOT NULL:NULL

PL/SQL 过程已成功完成。

现在我们看到了一个很有趣的结果，NOT NULL的结果仍然是NULL。可能很多人对此并不理解。下面还是从NULL的基本概念来解释。

NULL表示的是未知的含义，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果是TRUE，NOT TRUE将变为FALSE，如果是FALSE，NOT FALSE将变为TRUE，所有，即使进行了NOT操作，NULL本身的不确定性是仍然存在的。这就是最终结果仍然是NULL的原因。

这里需要注意：这个NOT NULL是一个布尔操作，要和SQL中的NOT NULL约束进行区分。NOT NULL约束是一个定性的描述，只是表示列中的数据不允许为NULL。而这里的布尔操作，却是在进行求值，要得到对NULL取非的结果，所以仍然得到NULL。

NOT TRUE	NOT FALSE	NOT NULL
FALSE	TRUE	NULL

 

发现很多人对空字符串’’不是很清楚，这里简单总结一下。

以前我总说空字符串’’等价于NULL，但是有些人喜欢钻牛角尖，所以我改一下说法，空字符串’’是NULL的字符类型的表现格式。

也许有人会认为，NULL就是NULL，本身没有类型的一说，但是我认为，NULL还是有类型的，只不过不同类型的NULL都用相同的关键字NULL来表示。而且，NULL本身也可以转化为任意类型的数据，因此给人的感觉是NULL没有数据类型。

其实NULL不但有数据类型，还有默认的数据类型，那就是字符类型。至于这个答案是如何推断出来的，请看：http://yangtingkun.itpub.net/post/468/50132

不过上面说的这个默认的数据类型是在极限的情况下测试出来的，如果只是给出一个NULL，那么它是可以代表任意的类型的。

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

SQL> SELECT 1 FROM DUAL WHERE '' = '';

未选定行

SQL> SELECT 1 FROM DUAL WHERE '' IS NULL;

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

SQL> SELECT DUMP(''), DUMP(NULL) FROM DUAL;

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

上面三个SQL语句，任意一个都足以证明空字符串’’就是NULL。

有些人可能会说，既然’’就是NULL，为什么不能进行IS ’’的判断呢?

SQL> SELECT 1 FROM DUAL WHERE '' IS '';
SELECT 1 FROM DUAL WHERE '' IS ''
*第 1 行出现错误:
ORA-00908: 缺失 NULL 关键字

其实从上面的错误信息就可以看到答案。原因就是IS NULL是Oracle的语法，在Oracle运行的时刻’’是NULL，但是现在Oracle还没有运行这句SQL，就由于语法不正确被SQL分析器挡住了。Oracle的语法并不包含IS ’’的写法，所以，这一点并不能称为’’不是NULL的理由。

那么我为什么还要说’’是NULL的字符表示形式呢？因为’’和NULL还确实不完全一样，对于NULL来说，它表示了各种数据类型的NULL值。而对于空字符串’’来说，虽然它也具有NULL的可以任意转化为其他任何数据类型的特点，但是无论是从形式上还是从本质上它都表现出了字符类型的特点。

下面通过一个例子来证明’’本质是字符类型的NULL。

SQL> CREATE OR REPLACE PACKAGE P_TEST_NULL AS
2 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2;
3 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2;
4 END;
5 /

程序包已创建。

SQL> CREATE OR REPLACE PACKAGE BODY P_TEST_NULL AS
2
3 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2 AS
4 BEGIN
5 RETURN 'NUMBER';
6 END;
7
8 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2 AS
9 BEGIN
10 RETURN 'VARCHAR2';
11 END;
12
13 END;
14 /

程序包体已创建。

SQL> SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;

P_TEST_NULL.F_RETURN(3)
------------------------------------------------------------
NUMBER

SQL> SELECT P_TEST_NULL.F_RETURN('3') FROM DUAL;

P_TEST_NULL.F_RETURN('3')
------------------------------------------------------------
VARCHAR2

SQL> SELECT P_TEST_NULL.F_RETURN('') FROM DUAL;

P_TEST_NULL.F_RETURN('')
------------------------------------------------------------
VARCHAR2

SQL> SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL;
SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL
*第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 'F_RETURN' 声明与此次调用相匹配

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