SQLを学んだ5回目

こんにちは。今日はSQL学習の5回目です。今日は一般関数を扱いたいと思います。条件式も見てみたいと思います。

使用するのはOracle Database 18cです。

一般関数
1. NVL、NVL2関数
2. NULLIF関数
条件式
1. CASE式
2. DECODE関数
おわりに

一般関数

NVL、NVL2関数

NVL関数はNULL値を指定した値に変換することができます。employeesテーブルのcommにNULL値があるので使ってみます。commは数値型なので０や１といった数値を指定してあげることで変換できます。

SELECT comm, NVL(comm, 0) FROM employees;

      COMM NVL(COMM,0)
---------- -----------
                     0
       100         100
       100         100
                     0
                     0
                     0
        19          19
       200         200

CommのNULL値を0に変換しました。NULLであると数値の足し算や掛け算をしてもNULLとなってしまいますが、0や1といった数値に変換してあげることで計算することができます。

SELECT comm, (comm+10)*10, (NVL(comm, 1)+10)*10 FROM employees;

      COMM (COMM+10)*10 (NVL(COMM,1)+10)*10
---------- ------------ -------------------
                                        110
       100         1100                1100
       100         1100                1100
                                        110
                                        110
                                        110
        19          290                 290
       200         2100                2100

また、NVL2を使うと、NULLである場合、NULLでない場合の戻り値を指定できます。

SELECT comm,　NVL2(comm, 1, 0) FROM employees;

      COMM NVL2(COMM,1,0)
---------- --------------
                        0
       100              1
       100              1
                        0
                        0
                        0
        19              1
       200              1

SELECT comm,　NVL2(comm, 'this is not null', 'this is null') FROM employees;

      COMM NVL2(COMM,'THISI
---------- ----------------
           this is null    
       100 this is not null
       100 this is not null
           this is null    
           this is null    
           this is null    
        19 this is not null
       200 this is not null

数値や文字列を返り値とし、null、非nullを区別することができます。

NULLIF関数

NULLIFを使うと、指定した値どうしが同じ場合にはNULLを返し、異なる場合は一つ目の値を返します。

SELECT salary, manager, NULLIF(salary, manager) FROM employees;

    SALARY    MANAGER NULLIF(SALARY,MANAGER)
---------- ---------- ----------------------
        10                                10
      9500                              9500
      2000        101                   2000
       100        100                       
       100        100                       
       100        100                       
       199        199                       
      4500                              4500
      1800        100                   1800
      2400        100                   2400
      1800        101                   1800

salaryとmanagerの数値が同じ場合はNULLを返し、空欄となりました。

条件式

Oracleには条件式としてCASEとDECODEというものがあります。CASE式は分岐としてよくみますが、DECODEとはどんなものなのでしょう。

CASE式

CASE式は以下の様に使用します。

CASE 式 WHEN 条件式 THEN 戻り値
END

条件に当てはまらない場合の処理はELSEを使用します。また、条件が複数ある場合はWHEN THENを繰り返して使う様です。

CASE 式 WHEN 条件式 THEN 戻り値
　　　　 WHEN 条件式 THEN 戻り値2
　　　　 WHEN 条件式 THEN 戻り値3
　　　　 WHEN 条件式 THEN 戻り値4
　　　　 ELSE デフォルトの戻り値
END

CASE式を使ってみます。dept_idによって給与にかける数値を変えます。

SELECT name, salary,
    CASE dept_id WHEN 10 THEN 10*salary
                 WHEN 20 THEN 20*salary
                 ELSE salary
    END result
FROM employees;

NAME           SALARY    DEPT_ID     RESULT
---------- ---------- ---------- ----------
King             4500         10      45000
John             1800         20      36000
David            2400         30       2400
Paul             1800         20      36000
Jones            2500         30       2500
Steven           2500         30       2500
Denver           2600         20      52000

CASE式はわかりやすく使いやすいですね。*式、条件式、戻り値、デフォルトの戻り値は全て同じ型の必要がある様です。

DECODE関数

DECODEはCASE式のCASEやWHEN,THENなどを省略した形の条件式です。DECODEで同様の式を作ると以下の様になります。

SELECT name, salary, dept_id,
    DECODE(dept_id, 10, 10*salary,
                    20, 20*salary,
                    salary)
result
FROM employees;

NAME           SALARY    DEPT_ID     RESULT
---------- ---------- ---------- ----------
King             4500         10      45000
John             1800         20      36000
David            2400         30       2400
Paul             1800         20      36000
Jones            2500         30       2500
Steven           2500         30       2500
Denver           2600         20      52000

THENやWHENを使わずに’,’で区切って使います。出力結果から同等の式であることがわかります。覚えてしまえばこちらの方が楽かもしれませんね。