04.17 oracle的空值介紹

在oracle數據庫中,空值用來表示實際值未知或無意義的情況. 在一個表中,如果一行中的某列沒有值,那麼就稱它為空值（NULL）.任何數據類型的列, 只要沒有使用非空（NOT NULL）或主鍵（PRIMARY KEY）完整性限制， 都 可以出現空值。

一、空值的生成與特點。

如果一列沒有非空（NOTNULL）完整性限制，那麼其缺省的值為空值，即如果插入一行時未指定該列的值，則其值為空值。

空值具有以下特點：

1）等價於沒有任何值。2）與0、空字符串或空格不同。3）在where條件中,Oracle認為結果為NULL的條件為FALSE，帶有這樣條件的select語句不返回行，並且不返回錯誤信息。但NULL和FALSE是不同的。4）排序時比其他數據都大。5）空值不能被索引。

二、空值和操作符

1.空值和比較操作符

（1）IS[NOT]NULL：是用來測試空值的唯一操作符（見"空值的測試"）。

sal或comm為空值的行，sal>comm比較結果為NULL，所以凡是sal或comm為空值的行都沒有返回。

（3）IN和NOTIN操作符

在上述語句中，條件"mgr in (7902,NULL)"等價於mgr=7902 or mgr=NULL。對於表EMP中的任意一行，如果mgr為NULL，則上述條件等價於NULL OR NULL，即為NULL；如果mgr為不等於7902的數值，則上述條件等價於FALSEORNULL，即為NULL；如果mgr等於7902，則上述條件等價於TRUE OR NULL，即為TRUE。所以，最終結果能返回mgr等於7902的行

在上述語句中，條件"deptno not in('10',NULL)"等價於deptno!='10' and deptno!=NULL，對於EMP表中的任意一行，條件的結果只能為NULL或FALSE，所以不返回行。

（4）any,some

條件"sal>any(3000,null)"等價於sal>3000 or sal>null。類似前述（3）第一句，最終結果返回所有sal>3000的行。

（5）All

條件"sal>all(3000,null)"等價於sal>3000 and sal>null,結果只能為NULL或FALSE，所以不返回行。

（6）（not）between

條件"sal between null and 3000"等價於sal>=null and sal<=3000,結果只能為NULL或FALSE，所以不返回行。

2、空值和算術、字符操作符

（1）算術操作符：空值不等價於0，任何含有空值的算術表達式其運算結果都為空值，例如空值加10為空值。

（2）字符操作符||：因為ORACLE目前處理零個字符值的方法與處理空值的方法相同（日後的版本中不一定仍然如此），所以對於||，空值等價於零個字符值。例：

我們可以看到，凡mgr為空值的，ename||mgr結果等於ename；凡是comm為空值的行，sal+comm均為空值。

三、空值和函數

1．空值和度量函數

對於度量函數，如果給定的參數為空值，則其（NVL、TRANSLATE除外）返回值為空值。如下例中的ABS（COMM），如果COMM為空值，ABS(COMM)為空值。

2.空值和組函數

組函數忽略空值。在實際應用中，根據需要可利用nvl函數用零代替空值。例：

第一個SELECT語句忽略COMM為空值的行，第二個SELECT語句使用NVL函數統計了所有的COMM，所以它們統計的個數、平均值都不相同。另外需要注意的是，在利用組函數進行數據處理時，不同的寫法具有不同的不同含義，在實際應用中應靈活掌握。例如：

可以看到SUM(SAL+COMM)、SUM(SAL)+SUM(COMM)、SUM(NVL(SAL,0)+NVL(COMM,0))的區別：SUM(SAL+COMM)為先加然後計算各行的和，如果SAL、COMM中有一個為NULL，則該行忽略不計；SUM(SAL)+SUM(COMM)為先計算各行的合計然後再加，SAL、COMM中的NULL都忽略不計，但如果SUM(SAL)、SUM(COMM)二者的結果之中有一個為NULL，則二者之和為NULL；在SUM(NVL(SAL,0)+NVL(COMM,0))裡，SAL、COMM中的NULL按0處理。

四、空值的其它特性

1.空值在排序時大於任何值。

2.空值不能被索引。雖然在某列上建立了索引，但是對該列的空值查詢來說，因為空值沒有被索引，所以不能改善查詢的效率。例如下面的查詢不能利用在MGR列上創建的索引。

另外正是因為空值不被索引，所以可在含有空值的列上建立唯一性索引（UNIQUEINDEX）。