ORACLE NVL 和 NVL2 函數的使用

標籤：eve   支援   rac   查詢   收入   ssi   11g   head   資料轉換   

NVL函數是一個空值轉換函式，在SQL查詢中主要用來處理null值。在不支援 null 值或 null 值無關緊要的情況下，可以使用 NVL( ) 來移去計算或操作中的 null 值。

Oracle在NVL函數的功能上擴充，提供了NVL2函數，使得不論目標值是否為null都能返回自訂的值。

工具/原料

• ORACLE 11g R2　
• PL/SQL Developer

方法/步驟

1.  

   1.NVL ( )文法：nvl(expr1,expr2)

   如果 expr1 是 null 值，則 nvl 函數返回 expr2 ，否則就返回 expr1 。 

   說明：參數 expr1、expr2 可以是任何資料類型，但應該保持相同。若兩者資料類型不一致，則Oracle資料庫會隱式的轉換其中一個的資料類型使其保持和另一個一致，若無法轉換則會返回錯誤。隱式轉換的實施規則如下：

    

        (1)若 expr1 是字元資料，則Oracle資料庫在比較之前就會把 expr2 轉換成 expr1 的資料類型並且返回 VARCHAR2 資料類型到 expr1 的字元集。

        (2) 若 expr1 是數值型，則Oracle資料庫決定哪個參數具有最高數值優先順序，並把另一個參數的資料類型隱式轉換成這種資料類型，並返回這種資料類型的資料。

   執行個體：返回員工姓名和獎金，若該員工沒有獎金則返回 Not Applicable

      SELECT last_name, NVL(TO_CHAR(commission_pct), ‘Not Applicable‘)        "COMMISSION" 

      FROM employees 

      WHERE last_name LIKE ‘B%‘ 

      ORDER BY last_name; 

2.  

   2.NVL2 ( )

   文法：nvl2(expr1,expr2,expr3)

   如果 expr1 不是 null 值，則 nvl 函數返回 expr2 ，否則就返回 expr3 。參數可以返回任何資料類型的值，但是 expr2 和 expr3 不能是 LONG 型的資料類型。

   說明：若 expr2 和 expr3 的資料類型不同：

        (1)若 expr2 是字元資料，則Oracle資料庫在比較之前就會把 expr3 轉換成 expr2 的資料類型除非 expr3 是null。在 這種情況下，隱式資料轉換是不必要的。Oracle資料庫返回 VARCHAR2 資料類型到 expr2 的字元集。

         (2) 若 expr2 是數值型，則Oracle資料庫決定哪個參數具有最高數值優先順序，並把另一個參數的資料類型隱式轉換成這種資料類型，並返回這種資料類型的資料。

   執行個體：返回員工收入，看員工收入(income)是否由工資(salary)加獎金(commission)組成，這取決於commission_pct列是否為null

      SELECT last_name, salary, NVL2(commission_pct, salary + (salary *       commission_pct), salary) income FROM employees 

      WHERE last_name like ‘B%‘ 

      ORDER BY last_name; 

    

ORACLE NVL 和 NVL2 函數的使用