条件式の使用

一般的な機能

一般的な関数は、データベース内のNULL値を処理するために使用されます。一般的なNULL処理関数の目的は、NULL値を代替値に置き換えることです。これらの機能について、以下で簡単に説明します。

NVL

NVL関数は、NULL値の代わりに代替値を使用します。

構文:

NVL( Arg1, replace_with )

構文では、両方のパラメーターが必須です。NVL関数は、すべてのタイプのデータ型で機能することに注意してください。また、元の文字列と置換のデータ型は互換性のある状態である必要があります。つまり、Oracleによって同じか暗黙的に変換可能である必要があります。

arg1が文字値の場合、オラクルは置換文字列をarg1と互換性のあるデータ型に変換してから比較し、expr1の文字セットでVARCHAR2を返します。arg1が数値の場合、Oracleは数値の優先順位が最も高い引数を判別し、他の引数をそのデータ型に暗黙的に変換して、そのデータ型を返します。

以下のSELECTステートメントは、従業員がまだどのジョブにも割り当てられていない場合、つまりJOB_IDがNULLの場合、「n / a」を表示します。それ以外の場合は、実際のJOB_ID値が表示されます。

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

NVL2

NVLの拡張機能として、Oracleは、NULL列の値だけでなくNOTNULL列の値も置換する関数を導入しました。NVL2関数を使用して、NULLおよびNULL以外の値の代替値を置き換えることができます。

構文:

NVL2( string1, value_if_NOT_null, value_if_null )

以下のSELECTステートメントは、従業員のJOB_CODEがNULLの場合、「ベンチ」を表示します。JOB CODEの明確なnull以外の値の場合、定数値「JobAssigned」が表示されます。

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

NULLIF

NULLIF関数は、2つの引数expr1とexpr2を比較します。expr1とexpr2が等しい場合、NULLを返します。それ以外の場合は、expr1を返します。他のnull処理関数とは異なり、最初の引数をNULLにすることはできません。

構文:

NULLIF (expr1, expr2)

最初の引数はNULLと評価される式にすることができますが、リテラルNULLにすることはできないことに注意してください。関数を実行するには、両方のパラメーターが必須です。

以下のクエリは、両方の入力値12が等しいため、NULLを返します。

SELECT	NULLIF (12, 12)
FROM DUAL;

同様に、両方の文字列が等しくないため、以下のクエリは「SUN」を返します。

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

COALESCE

NVLのより一般的な形式であるCOALESCE関数は、引数リストの最初のnull以外の式を返します。最小2つの必須パラメーターを取りますが、最大引数に制限はありません。

構文:

COALESCE (expr1, expr2, ... expr_n )

以下のSELECTクエリについて考えてみます。従業員の住所フィールドに入力された最初のnull以外の値を選択します。

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には、SQLステートメントでも条件を課すための条件関数DECODEおよびCASEが用意されています。

DECODE機能

この関数は、IF..THEN..ELSE条件付き手続き型ステートメントとSQLで同等です。DECODEは、すべてのデータ型の値/列/式で機能します。

構文:

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

DECODE関数は、式を各検索値と順番に比較します。式と検索引数の間に同等性が存在する場合、対応する結果を返します。一致しない場合は、デフォルト値が返されます(定義されている場合)。それ以外の場合はNULLです。タイプの互換性の不一致がある場合、Oracleは内部的に暗黙的な変換を行って結果を返します。

実際のところ、Oracleは、DECODE関数を使用している間、2つのnullを同等と見なします。

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ペアは、2つの引数としてカウントされます。この制限を超えないようにするには、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