13章 単一行関数(一般関数)
では、本章では一般関数について書いていきたいと思います。
一般関数はNULLを操作する関数となります。
一般関数
| NVL |
NULL値を別の値に変換して表示 |
| NVL2 |
NULL値かどうかにより、値を変換して表示 |
| NULLIF |
NULLがどうかを評価し表示 |
| COALESCE |
NULLではない値を表示 |
NVL関数
NVL関数はNULLを別の値に変換する関数です。
SQL> SELECT first_name,salary,NVL(salary,0) FROM employees;
FIRST_NAME SALARY NVL(SALARY,0)
------------------------------ ---------- -------------
Suzuki 10000000 10000000
Sato 8000000 8000000
Yamamoto 5000000 5000000
…
Ito 0
20 rows selected.
NLVは第1引数に対象データを指定し、
第2引数に対象データがNULLの場合に表示したい値を指定します。
対象データがNULLでない場合は、対象データの値を表示します。
上記例は、ItoさんはsalaryがNULLのため、0が表示されています。
またSatoさんはsalaryがNULLでないため、salaryの値が表示されています。
NVL関数を使用する応用として、NULLを別の値に変換して、計算式を使用することができます。
SQL> SELECT first_name,salary,salary*2,NVL(salary,3000000)*2
2> FROM employees;
FIRST_NAME SALARY SALARY*2 NVL(SALARY,3000000)*2
------------------- ---------- ---------- ---------------------
Suzuki 10000000 20000000 20000000
Sato 8000000 16000000 16000000
Yamamoto 5000000 10000000 10000000
…
Ito 6000000
20 rows selected.
上記例はNVL関数を使用して、SALARY列がNULLの場合、3000000に変換しています。
その値に対し、2倍の計算を行っています。
NULL値は計算式を使用しても結果はNULLでしたが、
数値に変換することにより、計算式が使用できます。
NVL2関数
続いてNVL2関数です。NVL関数はNULLの場合だけ、ほかの値に変更する関数でしたが、
NULLでない場合にも、別の値に変更することができます。
SQL> SELECT first_name,salary,NVL2(salary,0,99999)
2> FROM employees;
FIRST_NAME SALARY NVL2(SALARY,0,99999)
------------------------------ ---------- --------------------
Suzuki 10000000 0
Sato 8000000 0
Yamamoto 5000000 0
…
Ito 99999
20 rows selected.
上記例ではSALARYの値がNULLでない場合は0を表示し、
NULLの場合は99999を表示しています。
この様にNVL2関数はNULLの場合、
NULLでない場合の両方に値を指定することができる関数です。
NULLIF関数
続いてNULLIF関数のご紹介です。
NULLIF関数は2つの引数を比較して同じ値の場合、NULLを表示します。
SQL> SELECT NULLIF('TEST','TEST') FROM dual;
NULL
----
今回の実行例は対象データ1,2共に、TESTという値で同じ文字の為、
実行結果にNULLが表示されます。
SQL> SELECT NULLIF('TEST','AAAA') FROM dual;
NULL
----
TEST
今回の実行例は対象データ１はTEST,対象データ2はAAAAなので、
別の値の為、対象データ1が表示されます。
このようにNULLIF関数は対象列の値がNULLかどうか判別するときに使用します。
COALESCE関数
では、最後にCOALESCE関数ですが、
この関数は複数の対象データに対しNULLかどうかを判別することができます。
対象データ1がNULLでない場合は、対象データ1を表示し、
NULLの場合は、対象データ2を参照し、NULLでない場合は、
対象データ2を表示します。
そうでない場合は、対象データ3を参照し…というようにNULLの場合は
次の対象データを参照していきます。
この関数で指定できる対象データの上限は256のようです。
SQL> SELECT first_name,COALESCE(salary,manager_id) FROM employees;
FIRST_NAME COALESCE(SALARY,MANAGER_ID)
------------------------------ ---------------------------
Suzuki 10000000
Sato 8000000
Yamamoto 5000000
…
Ito 16
20 rows selected.
SuzukiさんはsalaryがNULLでない為、salaryが表示されていますが、
ItoさんはsalaryがNULLのため、manager_idを参照していますが、
manager_idはNULLでない為、manager_idが表示されています。
いかがでしたでしょうか。5回に渡り単一行関数を学んできました。
覚えることがたくさんありましたが、いくつの引数が必要かまで覚える必要はありません。
いつか使用するタイミングがあり、この関数が使えるかもと思い出せればそれで良いです。
今回紹介した関数もごく一部ですので、使用するタイミングがあれば、
その他の関数も調べてみると良いでしょう。