ORACLE中小心与NULL有关的操作

　　
ORACLE中小心与NULL有关的操作

　　ORACLE中与NULL有关的操作
　　1.更新数据时注意NULL操作
　　(1)更新时,因为NULL导致更新后数据丢失.
　　创建表test_1和表test_2,两张表的测试数据如下:
　　SQL> select * from test_1;
　　 
　　ID1   ID2
　　----- -----
　　1     16
　　2     17
　　      21
　　 
　　SQL> SELECT * FROM TEST_2;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　      19
　　 
　　接下来利用表TEST_2去更新表TEST_1,关联字段为ID1,用TEST_2表ID2字段的值去更新TEST_1表ID2字段的值.
　　SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1);
　　 
　　3 rows updated
　　 
　　SQL> SELECT * FROM TEST_1;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　显然TEST_1表记录被做了更新,但却丢失了一条数据.
　　SQL> rollback;
　　 
　　Rollback complete
　　 
　　再利用表TEST_1和表TEST_2进行关联操作如下:
　　 
　　SQL> SELECT A.* FROM TEST_1 A,
　　  2  TEST_2 B
　　  3  WHERE A.ID1=B.ID1;
　　 
　　ID1   ID2
　　----- -----
　　1     16
　　2     17
　　从上面关联之后的结果来看,很显然所得出的结果跟我们想要的结果不一样,关联之后所得的结果数据中没有ID1为NULL的数据记录.
　　那么为什么以上操作会造成数据的丢失呢.要回答这个问题,首先得回答下面这个问题.
　　在ORACLE数据库中NULL=NULL?
　　对于这个问题有人回答是相等的,也有人回答是不等的,那么到底是相等的还是不相等的呢?下面在ORACLE数据库环境中做下实验.
　　SQL> select * from dual;
　　 
　　DUMMY
　　-----
　　 
　　X
　　SQL> select * from dual where 1=1;
　　 
　　DUMMY
　　-----
　　X
　　SQL> select * from dual where 1=2;
　　 
　　DUMMY
　　-----
　　 
　　通过上述SQL操作知道,可以用DUAL表来证明一下NULL等不等于NULL.
　　SQL> select * from dual where null=null;
　　 
　　DUMMY
　　-----
　　 
　　结果显示,NULL等于NULL是不对的.
　　SQL> select * from dual where null<>null;
　　 
　　DUMMY
　　-----
　　 
　　结果显示,NULL不等于NULL是不对的.
　　从上面SQL可以看出,在ORACLE中null=null和null<>null都是不对的.再看下面SQL
　　SQL> select * from dual where null is null;
　　 
　　DUMMY
　　-----
　　X
　　结果显示,NULL是可能等于NULL的.
　　由此可以看出,在ORACLE数据库中NULL表示的是一个未知的东西.这跟SQL Server和Sybase等其他版本的数据库是不一样的,他们认为NULL=NULL是正确的.这就可以解释为什么在做更新及关联操作时,结果表中数据丢失的原因了.
　　 
　　(2)更新时,因为NULL导致更新后数据出错.
　　SQL> select * from test_1;
　　 
　　ID1   ID2
　　----- -----
　　1     16
　　2     17
　　      21
　　3     18
　　SQL> select * from test_2;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　4     
　　利用表TEST_2去更新表TEST_1,关联字段为ID1,用TEST_2表ID2字段的值去更新TEST_1表ID2字段的值.
　　SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1);
　　 
　　4 rows updated
　　 
　　SQL> select * from test_1;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　3     
　　从上面结果可以看出,更新后数据不仅丢失了ID1为空的数据,而且使得ID1=3这条数据发生了改变.可以看出上述更新操作是不合理的,在进行更新操作时并没有考虑NULL的情况.
　　SQL> rollback;
　　 
　　Rollback complete
　　 
　　正确的更新操作如下:
　　SQL> update test_1 a set id2=(select id2 from test_2 b where a.id1=b.id1)
　　  2  where exists (select 1 from test_2 b where a.id1=b.id1);
　　 
　　2 rows updated
　　 
　　SQL> SELECT * FROM TEST_1;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　      21
　　3     18
　　 
　　SQL> COMMIT;
　　 
　　Commit complete
　　 
　　SQL> SELECT * FROM TEST_1;
　　 
　　ID1   ID2
　　----- -----
　　1     26
　　2     27
　　      21
　　3     18
　　 
　　2.索引与NULL
　　在ORACLE数据库中含有NULL的字段是不能走索引的,这是很多数据库开发者都知道的.那么为什么含有NULL的字段不能走索引呢.下面通过实验来说明一下原因.
　　<1>创建测试用的数据表TEST_3.
　　SQL> drop table test_3;
　　 
　　Table dropped
　　 
　　SQL> create table TEST_3
　　  2  (
　　  3    IP_ID NUMBER(4),
　　  4    NAMENO  NUMBER(4)
　　  5  );
　　 
　　Table created
　　 
　　SQL> INSERT INTO TEST_3 VALUES(1,2);
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_3 VALUES(1,NULL);
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_3 VALUES(NULL,1);
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_3 VALUES(NULL,NULL);
　　 
　　1 row inserted
　　 
　　SQL> COMMIT;
　　 
　　Commit complete
　　 
　　SQL> SELECT * FROM TEST_3;
　　 
　　IP_ID NAMENO
　　----- ------
　　    1      2
　　    1 
　　           1
　　 
　　<2>根据表test_3的IP_ID和NAMENO字段建立索引in_test_3.
　　SQL> create unique index in_test_3 on test_3(IP_ID,NAMENO);
　　 
　　Index created
　　 
　　SQL> analyze index in_test_3 validate structure;
　　 
　　Index analyzed
　　 
　　<3>查看索引存储的行数,很显然索引中只存储了3行数据,而实际有四行,未存储的就是都为NULL的数据.
　　SQL> select a.name,a.lf_rows from index_stats a;
　　 
　　NAME                              LF_ROWS
　　------------------------------ ----------
　　IN_TEST_3                               3
　　 
　　SQL> INSERT INTO TEST_3 VALUES(1,NULL);
　　 
　　INSERT INTO TEST_3 VALUES(1,NULL)
　　 
　　ORA-00001: 违反唯一约束条件 (ACCOUNT.IN_TEST_3)
　　 
　　因为建立的是唯一索引,所以再次重复插入违反了唯一约束条件. 
　　SQL> INSERT INTO TEST_3 VALUES(NULL,NULL);
　　 
　　1 row inserted
　　 
　　因为索引不能存储NULL,所以当再次插入为NULL的数据记录时,不受索引唯一性的约束.
　　SQL> COMMIT;
　　 
　　Commit complete
　　 
　　SQL> SELECT * FROM TEST_3;
　　 
　　IP_ID NAMENO
　　----- ------
　　    1      2
　　    1 
　　           1
　　 
　　<4>NULL对COUNT()、MAX()和MIN()影响
　　SQL> SELECT A.IP_ID,COUNT(A.IP_ID) FROM TEST_3 A GROUP BY A.IP_ID;
　　 
　　IP_ID COUNT(A.IP_ID)
　　----- --------------
　　    1              2
　　                   0
　　 
　　SQL> SELECT B.IP_ID,B.NAMENO,COUNT(*) FROM TEST_3 B GROUP BY B.IP_ID,B.NAMENO;
　　 
　　IP_ID NAMENO   COUNT(*)
　　----- ------ ----------
　　           1          1
　　                      2
　　    1      2          1
　　    1                 1
　　 
　　SQL> SELECT MIN(A.IP_ID),MAX(A.IP_ID) FROM TEST_3 A;
　　 
　　MIN(A.IP_ID) MAX(A.IP_ID)
　　------------ ------------
　　           1            1
　　 
　　3.在数据库中,NULL与空字符串存储到数据库中之后的表现形式都是一样的,即都是不可见的.那么NULL与空字符串到底有区别没有呢?在ORACLE数据库中' '是一个确定的值,而NULL则可以表示任何的一个值.
　　<1>创建试验数据表TEST_4.
　　SQL> create table TEST_4
　　  2  (
　　  3    IP_ID VARCHAR2(4),
　　  4    NAMENO  VARCHAR2(4)
　　  5  );
　　 
　　Table created
　　 
　　SQL> INSERT INTO TEST_4 VALUES('1','2');
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_4 VALUES('1',' ');
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_4 VALUES(' ','1');
　　 
　　1 row inserted
　　 
　　SQL> INSERT INTO TEST_4 VALUES(' ',' ');
　　 
　　1 row inserted
　　 
　　SQL> COMMIT;
　　 
　　Commit complete
　　 
　　<2>创建索引及查看索引存储情况,跟上面为NULL进行对比.
　　SQL> CREATE UNIQUE INDEX IN_TEST_4 ON TEST_4(IP_ID,NAMENO);
　　 
　　Index created
　　 
　　SQL> ANALYZE INDEX IN_TEST_4 VALIDATE STRUCTURE;
　　 
　　Index analyzed
　　 
　　SQL> SELECT B.name,B.lf_rows FROM INDEX_STATS B;
　　 
　　NAME                              LF_ROWS
　　------------------------------ ----------
　　IN_TEST_4                               4
　　 
　　在ORACLE数据库中空字符串是可以存储到索引中的,显然这与NULL是不同的.
　　SQL> INSERT INTO TEST_4 VALUES(' ',' ');
　　 
　　INSERT INTO TEST_4 VALUES(' ',' ')
　　 
　　ORA-00001: 违反唯一约束条件 (ACCOUNT.IN_TEST_4)
　　 
　　因为在数据库中' '被认为是一个确定的值,可以存储在索引中,索引当再次重复插入时,提示违反了唯一约束条件.
　　SQL> SELECT * FROM TEST_4;
　　 
　　IP_ID NAMENO
　　----- ------
　　1     2
　　1     
　　      1
　　 
　　SQL> SELECT MAX(A.IP_ID),MIN(A.IP_ID) FROM TEST_4 A;
　　 
　　MAX(A.IP_ID) MIN(A.IP_ID)
　　------------ ------------
　　1            
　　 
　　与NULL相比,空字符串在数据库中是可以被MIN()与MAX()统计的,而且空字符串比不是空字符串的值要少.
　　SQL> SELECT A.IP_ID,COUNT(DISTINCT A.IP_ID) FROM TEST_4 A GROUP BY A.IP_ID;
　　 
　　IP_ID COUNT(DISTINCTA.IP_ID)
　　----- ----------------------
　　                           1
　　1                          1
　　与NULL相比,空字符串是可以被COUNT()统计的.
　　SQL> SELECT A.IP_ID,A.NAMENO,COUNT(*) FROM TEST_4 A GROUP BY A.IP_ID,A.NAMENO;
　　 
　　IP_ID NAMENO   COUNT(*)
　　----- ------ ----------
　　      1               1
　　1     2               1
　　1                     1
　　                      1
　　在统计数据表中的记录条数时,NULL跟NULL被认为是不一样的,但却属于同一类型.而空字符串则不同,空字符串被认为不是同一类型的.为形成对比做如下调整.
　　SQL> select * from test_4 t;
　　 
　　IP_ID NAMENO
　　----- ------
　　1     2
　　1     
　　 
　　 
　　SQL> select a.ip_id,a.nameno,count(*)  from test_4 a group by a.ip_id,a.nameno; 
　　 
　　IP_ID NAMENO   COUNT(*)
　　----- ------ ----------
　　                      1
　　1     2               1
　　1                     1
　　                      1
　　显然,空字符串在被统计时,被认为是不一样的.
　　 
　　  从上面的实验可以看出,在数据库中,不仅要小心与NULL有关的操作,而且还要做到能对NULL和空字符串进行有效的区分.这样能避免某些错误的发生.