Sql與oracle中null值

 　　1 null值的介紹

　　NULL 是數據庫中特有的數據類型，當一條記錄的某個列為 NULL ，則表示這個列的值是未知的、是不確定的。既然是未知的，就有無數種的可能性。因此， NULL 並不是一個確定的值。 這是 NULL 的由來、也是 NULL 的基礎，所有和 NULL 相關的操作的結果都可以從 NULL 的概念推導出來。

　　2 oracle中的null值介紹

　　在不知道具體有什麼數據的時候，即未知，可以用NULL， 稱它為空，ORACLE中，含有空值的表列長度為零。允許任何一種數據類型的字段為空，除了以下兩種情況：

　　a、主鍵字段(primary key);

　　b、定義時已經加了NOT NULL限制條件的字段

　　3 Oracle中null值說明：

　　a、等價於沒有任何值、是未知數。

　　b、NULL與0、空字符串、空格都不同。

　　c、對空值做加、減、乘、除等運算操作，結果 仍為空。

　　d、NULL的處理使用NVL函數。

　　e、比較時使用關鍵字用“is null”和“is not null”。

　　f、空值不能被索引，所以查詢時有些符合條件的數據可能查不出來， count(expr)中，用nvl(列名,0)處理後再查。

　　g、排序時比其他數據都大(索引默認是降序排列，小→大)， 所以NULL值總是排在最後。

　　IS NULL 和IS NOT NULL 是不可分割的整體，改為IS 或IS NOT都是錯誤的，從上面我們看到了NULL 和空字符串的區別。

　　任何和NULL 的比較操作，如<>、=、<=等都返回UNKNOWN(這裡的unknown就是null，它單獨使用和布爾值false類似).判斷和比較規則總結如下：

　　判斷和比較規則總結表

　　例如：使用方法：

　　SQL> select 1 from dual where null=null;

　　沒有查到記錄

　　SQL> select 1 from dual where null="';

　　沒有查到記錄

　　SQL> select 1 from dual where ''='';

　　沒有查到記錄

　　SQL> select 1 from dual where null is null;

　　1

　　---------

　　1

　　SQL> select 1 from dual where nvl(null,0)=nvl(null,0);

　　1

　　---------

　　1

　　4、null做一些算術運算，比如+，-，*，/等，結果 還是null，但是對於連接操作符||，null忽略,concat函數也忽略null

　　SQL> select null || 'abc' from dual;

　　NUL

　　---

　　abc

　　SQL> select concat(null,'abc') from dual;

　　CON

　　---

　　abc

　　SQL> select null+10 from dual;

　　NULL+10

　　----------

　　5、null相關函數規則

　　Oracle有nvl、nvl2、nullif、coalesce等函數專門處理null

　　5.1 nvl(expr1,expr2)

　　描述：如果expr1是null，那麼用expr2作為返回值，不是null則返回expr1.expr1與expr2一般是類型相同的，如果類型不同則會采用自動轉換，轉換失敗則報錯。

　　SQL*PLUS中數值類型右對齊，字符左對齊;通過第三條語句可以看出null和‘’還是有區別的。

　　SQL> select nvl(null,0) from dual;

　　NVL(NULL,0)

　　-----------

　　0

　　SQL> select nvl(to_char(null),0) from dual;

　　N

　　-

　　0

　　SQL> select nvl('',0) from dual;

　　N

　　-

　　0

　　5.2 nvl2函數

　　語法：nvl2(expr1,expr2,expr3)

　　描述：expr1如果是null，則返回expr3,否則返回expr2

　　expr2和expr3類型不同，expr3類型轉換為expr2類型

　　SQL> select nvl2(null,'1',2) from dual;

　　N

　　-

　　2

　　expr2為null，返回值類型和expr3一致

　　SQL> select nvl2(null,null,2) from dual;

　　NVL2(NULL,NULL,2)

　　-----------------

　　2

　　SQL> select nvl2(null,null,'2') from dual;

　　N

　　-

　　2

　　不同類型之間的轉換，自動轉換不成功，則報錯

　　SQL> select nvl2(null,1,'b') from dual;

　　select nvl2(null,1,'b') from dual

　　*

　　ERROR at line 1:

　　ORA-01722: invalid number(無效數字)

　　5.3 nullif函數

　　語法：nullif(expr1,expr2)

　　描述：判斷expr1和expr2是否相等，若相等則返回null，否則返回expr1.要求expr1與expr2類型必須相同

　　SQL> select nullif(1,3) from dual;

　　NULLIF(1,3)

　　-----------

　　1

　　SQL> select nullif(1,1) from dual;

　　NULLIF(1,1)

　　-----------

　　SQL> select nullif('1',1) from dual;

　　select nullif('1',1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

　　SQL> select nullif('ab','ab ') from dual;

　　NU

　　--

　　SQL> select nullif(null,1) from dual;

　　select nullif(null,1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected - got CHAR

　　SQL> select nullif(to_char(null),'1') from dual;

　　N

　　-

　　5.4 coalesce函數

　　語法：coalesce(expr1,expr2,…,exprn)

　　描述：從左到右返回第一個為非null的值，若所有的列表元素都為null，則返回null。要求所有都必須為同一類型。

　　SQL> select coalesce(null,null,null) from dual;

　　C

　　-

　　SQL> select coalesce(null,1,2) from dual;

　　COALESCE(NULL,1,2)

　　------------------

　　1

　　SQL> select coalesce(1,'1',1) from dual;

　　select coalesce(1,'1',1) from dual

　　*

　　ERROR at line 1:

　　ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

　　6 null與索引

　　Oracle中的B*Tree索引，並不存儲全為null的列，

　　雖然在表中建立了符合UNIQUE 索引，但是全為null的行還是可以插入的，而不是全為null的重復行則不可以插入。因為在UNIQUE約束中，(null，null)和(null，null)是不同的，當然在其他一些情況，比如說分組、集合操作中都認為全是null是相等的

　　SQL> create table t(a number,b number);

　　Table created.

　　SQL> create unique index idx_t on t(a,b);

　　Index created.

　　SQL> insert into t values(null,null);

　　1 row created.

　　SQL> insert into t values(1,null);

　　1 row created.

　　SQL> insert into t values(null,1);

　　1 row created.

　　SQL> commit;

　　SQL> insert into t values(1,null);

　　insert into t values(1,null)

　　*

　　ERROR at line 1:

　　ORA-00001: unique constraint (SCOTT.IDX_T) violated

　　7 null的排序

　　order by默認升序(asc),這時候null是排在最後的，如果指定降序那麼null是排在最前面的，認為null最大。

　　但是可以用nulls first和nulls last進行調整。

　　SQL> select * from emp

　　2 order by comm asc;

　　SQL> select * from emp

　　2 order by comm desc;

　　SQL> select *from emp

　　2 order by comm asc nulls first;

　　SQL> select *from emp

　　2 order by comm desc nulls last;

　　8 null與性能的關系

　　Not null約束，定義約束是要付出消耗性能的代價的，由下面的測試可以看出雖然約束檢查的很快，但是有時候還是很消耗資源的，至少在這個例子上是這樣的，不需要not null約束，除非必要，不要亂定義約束。

　　SQL> set serveroutput on

　　SQL> declare

　　2 v_value number not null :=0;

　　3 start_time number;

　　4 end_time number;

　　5 begin

　　6 start_time :=DBMS_UTILITY.GET_TIME;

　　7 FOR i in 0..100000000 LOOP

　　8 v_value :=i;

　　9 end LOOP;

　　10 end_time :=DBMS_UTILITY.GET_TIME;

　　11 DBMS_OUTPUT.PUT_LINE(end_time-start_time);

　　12 END;

　　13 /

　　1043

　　PL/SQL procedure successfully completed.

　　SQL> declare

　　2 v_value number;

　　3 start_time number;

　　4 end_time number;

　　5 begin

　　6 start_time :