kxbin
成功留给有准备的人
posts - 10,  comments - 35,  trackbacks - 0

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;

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 FA发现很多人对空字符串’’不是很清楚,这里简单总结一下。

以前我总说空字符串’’等价于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 

3 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2 AS
4 BEGIN
5 RETURN 'NUMBER';
6 END;

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的字符类型表现形式。LSE AND NULL,其最终结果是NULL或者FALSE,所以,查询不会返回记录。
 
 

posted on 2012-08-30 16:13 kxbin 阅读(318) 评论(0)  编辑  收藏 所属分类: ORACLE转发

只有注册用户登录后才能发表评论。


网站导航:
 
你恨一个人是因为你爱他;你喜欢一个人,是因为他身上有你没有的;你讨厌一个人是因为他身上有你有的东西;你经常在别人面前批评某人,其实潜意识中是想接近他。

<2024年12月>
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

常用链接

留言簿(5)

随笔档案

文章分类

文章档案

相册

收藏夹

J2EE

java技术网站

Linux

平时常去的网站

数据库

电影网站

网站设计

搜索

  •  

最新评论

阅读排行榜

评论排行榜