SQL - 使用條件表示式

通用函式

通用函式用於處理資料庫中的 NULL 值。通用 NULL 處理函式的目的是用備用值替換 NULL 值。我們將在下面簡要介紹這些函式。

NVL

NVL 函式用備用值替換 NULL 值。

語法

NVL( Arg1, replace_with )

在語法中，兩個引數都是必須的。請注意，NVL 函式適用於所有資料型別。並且原始字串和替換字串的資料型別必須處於相容狀態，即相同或可由 Oracle 隱式轉換。

如果 arg1 是字元值，則 Oracle 將替換字串轉換為與 arg1 相容的資料型別，然後進行比較，並以 expr1 的字元集返回 VARCHAR2。如果 arg1 是數值，則 Oracle 確定具有最高數值優先順序的引數，將另一個引數隱式轉換為該資料型別，並返回該資料型別。

下面的 SELECT 語句將顯示 'n/a'，如果員工尚未分配任何工作，即 JOB_ID 為 NULL。否則，它將顯示實際的 JOB_ID 值。

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

作為 NVL 的增強，Oracle 引入了一個函式，不僅可以為 NULL 列值替換值，還可以為非 NULL 列替換值。NVL2 函式可用於為 NULL 值和非 NULL 值替換備用值。

語法

NVL2( string1, value_if_NOT_null, value_if_null )

下面的 SELECT 語句將在員工的 JOB_CODE 為 NULL 時顯示 'Bench'。對於 JOB CODE 的確定非空值，它將顯示常量值 'Job Assigned'。

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

NULLIF 函式比較兩個引數 expr1 和 expr2。如果 expr1 和 expr2 相等，則返回 NULL；否則，返回 expr1。與其他空值處理函式不同，第一個引數不能為 NULL。

語法

NULLIF (expr1, expr2)

請注意，第一個引數可以是計算結果為 NULL 的表示式，但不能是文字 NULL。兩個引數對於函式執行都是必須的。

下面的查詢返回 NULL，因為兩個輸入值 12 相等。

SELECT	NULLIF (12, 12)
FROM DUAL;

類似地，下面的查詢返回 'SUN'，因為兩個字串不相等。

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

COALESCE

COALESCE 函式是 NVL 的更通用形式，它返回引數列表中的第一個非空表示式。它至少需要兩個必須的引數，但最多引數沒有限制。

語法

COALESCE (expr1, expr2, ... expr_n )

考慮下面的 SELECT 查詢。它為員工的地址欄位選擇第一個非空值。

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

有趣的是，COALESCE 函式的工作方式類似於 IF..ELSIF..ENDIF 結構。上面的查詢可以改寫為 -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

條件函式

Oracle 提供了條件函式 DECODE 和 CASE，即使在 SQL 語句中也能施加條件。

DECODE 函式

該函式是 IF..THEN..ELSE 條件過程語句的 SQL 等價物。DECODE 可處理所有資料型別的值/列/表示式。

語法

DECODE (expression, search, result [, search, result]... [, default])

DECODE 函式按順序將表示式與每個搜尋值進行比較。如果表示式和搜尋引數之間存在相等性，則返回相應的result。如果沒有匹配，則返回預設值（如果已定義），否則返回 NULL。如果存在任何型別相容性不匹配，Oracle 會在內部進行可能的隱式轉換以返回結果。

事實上，Oracle 在使用 DECODE 函式時認為兩個空值是等價的。

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

如果表示式為 null，則 Oracle 返回第一個也為 null 的搜尋的結果。DECODE 函式中的最大元件數為 255。

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

CASE 表示式

CASE 表示式基於與 DECODE 相同的概念，但在語法和用法上有所不同。

語法

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

Oracle 從左到右開始搜尋，直到找到一個真條件，然後返回與其關聯的結果表示式。如果找不到任何為真的條件，並且存在 ELSE 子句，則 Oracle 返回用 else 定義的結果。否則，Oracle 返回 null。

CASE 表示式中的最大引數數為 255。所有表示式都計入此限制，包括簡單 CASE 表示式的初始表示式和可選的 ELSE 表示式。每個 WHEN ... THEN 對計為兩個引數。為了避免超過此限制，您可以巢狀 CASE 表示式，以便 return_expr 本身是 CASE 表示式。

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM    CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1

列印頁面