【SilverSQL】第4章

# 単一行ファンクションを使用した出力のカスタマイズ
* 単一行関数とは、行ごとに処理を行い、1行につき1件の結果を返す関数です。SELECT句、WHERE句、HAVING句、ORDER BY句などで使用することができる。
* 単一行関数のネストレベルには制限ない。また、指定した引数と返される結果のデータ型は、必ずしも一致するとは限らない。

### ファンクション
<table>
  <caption>主なファンクション</caption>
  <thead>
    <tr>
      <th>分類</th>
      <th>ファンクション</th>
      <th>説明</th>
    </tr>
  </thead>
  <tr>
    <td>文字ファンクション</td>
    <td>LOWER</td>
    <td>文字列を小文字に変換する</td>
  </tr>
  <tr>
    <td></td>
    <td>UPPER</td>
    <td>文字列を大文字に変換する</td>
  </tr>
  <tr>
    <td></td>
    <td>INITCAP</td>
    <td>文字列の先頭文字を大文字に、それ以降を小文字に変換する</td>
  </tr>
  <tr>
    <td></td>
    <td>CONCAT</td>
    <td>文字列を連結する</td>
  </tr>
  <tr>
    <td></td>
    <td>SUBSTR</td>
    <td>文字列から部分文字列を抜き出す</td>
  </tr>
  <tr>
    <td></td>
    <td>LPAD</td>
    <td>文字列の左側に文字を埋め込む</td>
  </tr>
  <tr>
    <td></td>
    <td>RPAD</td>
    <td>文字列の右側に文字を埋め込む</td>
  </tr>
  <tr>
    <td></td>
    <td>REPLACE</td>
    <td>文字列を置換する</td>
  </tr>
  <tr>
    <td></td>
    <td>TRIM</td>
    <td>文字列の前後にある文字を取り除く</td>
  </tr>
  <tr>
    <td></td>
    <td>LENGTH</td>
    <td>文字列の文字数を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>INSTR</td>
    <td>文字列を位置を戻す</td>
  </tr>
  <tr>
    <td>数値ファンクション</td>
    <td>ROUND</td>
    <td>数値を四捨五入する</td>
  </tr>
  <tr>
    <td></td>
    <td>TRUNC</td>
    <td>数値を切り捨てる</td>
  </tr>
  <tr>
    <td></td>
    <td>MOD</td>
    <td>数値を除算したときの余りを戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>POWER</td>
    <td>数値のべき乗を戻す</td>
  </tr>
  <tr>
    <td>日時ファンクション</td>
    <td>ADD_MONTHS</td>
    <td>数か月前または数か月後の日時を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>MONTHS_BETWEEN</td>
    <td>2つの日時の期間を月単位で戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>NEXT_DAY</td>
    <td>特定の曜日の日付を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>LAST_DAY</td>
    <td>月の最終日の日付を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>SYSDATE</td>
    <td>データベースサーバーのOSの日時を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>ROUND</td>
    <td>日時を丸める</td>
  </tr>
  <tr>
    <td></td>
    <td>TRUNC</td>
    <td>日時を切り捨てる</td>
  </tr>
  <tr>
    <td>変換ファンクション</td>
    <td>TO_CHAR</td>
    <td>データを文字型へ変換する</td>
  </tr>
  <tr>
    <td></td>
    <td>TO_NUMBER</td>
    <td>データを数値型へ変換する</td>
  </tr>
  <tr>
    <td></td>
    <td>TO_DATE</td>
    <td>データを日時型へ変換する</td>
  </tr>
  <tr>
    <td>NULL関連ファンクション</td>
    <td>NVL</td>
    <td>NULLを指定した値に置き換える</td>
  </tr>
  <tr>
    <td></td>
    <td>NVL2</td>
    <td>NULLをどうかに応じて異なる値を戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>NULLIF</td>
    <td>値が等しい場合にNULLを戻す</td>
  </tr>
  <tr>
    <td></td>
    <td>COALESCE</td>
    <td>複数の値のうち、最初かに見つかった非NULL値を戻す</td>
  </tr>
</table>

#### SUBSTR

* 1つ目の引数で指定された文字列のm文字目からn文字分の文字列を返しますが、2つ目の引数に負の値が指定された場合は、文字列の末尾から数えてm文字目からn文字分の文字列を返します。
* 2つ目の引数に-6が指定されていますので、文字列の末尾から数えて6文字目である「W」から3文字分の文字列を返す。

```sql:例
SELECT 
SUBSTR('Oracle Web問題集', -6, 3)
FROM 
dual;
```
```:結果
Web
```

#### TRIM
文字列の前後にある指定された文字を削除

```
TRIM( [trim_char FROM] str)
```

* LEADINGを指定した場合、文字列strの先頭から連続した文字trim_charを探し、削除。
* TRAILINGを指定した場合、文字列strの末尾から連続した文字trim_charを探し、削除。
* BOTHを指定した場合、文字列strの先頭および末尾から連続した文字trim_charを探し、削除。
* trim_charおよびFROMを省略した場合、文字列strの先頭および末尾から連続した空白文字を探し、削除。

```:例
SQL> SELECT TRIM(LEADING '*' FROM '**ABC**') FROM dual;
```
```:結果
ABC**
```

#### INSTR
引数で指定した文字列の出現した位置を数値で戻す
```
INSTR(str, serach [, pos [, n]])
INSTR(文字列, 検索文字列, 開始位置, 何番目に現れるか)
```

* 文字列strのpos文字目から文字列searchを検索し、文字列searchがn回目に出現する位置を戻す。
* posおよびnを省略した場合は1
* 文字列strに文字列searchがなかった場合、戻り値は0

1. 文字列：検索対象（この例では prod_name）
2. 検索文字列：探したい文字（この例では ' ' スペース）
3. 開始位置：検索を始める文字の位置（1 は文字列の先頭から）※省略した場合は1
4. 出現回数：検索文字列の何番目を探すか（ここでは2番目のスペース）※省略した場合は1

```:例
INSTR(prod_name, ' ', 1, 2) <> 0
SQL> SELECT INSTR('ABCDEABCDE', 'C') FROM dual;
SQL> SELECT INSTR('ABCDEABCDE', 'C', 6, 1) FROM dual;
```
```:結果
INSTR('ABCDEABCDE', 'C'), INSTR('ABCDEABCDE', 'C', 6, 1)
                       3                               8
```

#### ROUND
数値を四捨五入する
```
ROUND(数値[, n])
```
* 数値：四捨五入をする数値
* n：小数点以下n桁に丸める
* 例) n=1　小数点2桁目で四捨五入され、小数点以下1桁に丸められる
* n=2　小数点3桁目で四捨五入され、小数点以下2桁に丸められる
* 例) n=-1 一の位で四捨五入される
* n=-2 十の位で四捨五入される
* n=-3 百の位で四捨五入される

```:例
SQL> SELECT ROUND(18.167), ROUND(18.167, 1), ROUND(18.167, -1) FROM dual;
```
```:結果
ROUND(18.167), ROUND(18.167, 1), ROUND(18.167, -1)
           19              18.6                 20
```

##### ROUNDファンクションに指定できる日時書式モデルと丸めの処理
* 書式モデルを省略すると「DD」で四捨五入

<table>
  <caption>ROUNDファンクションに指定できる日時書式モデルと丸めの処理</caption>
  <thead>
    <tr>
      <th>書式モデル</th>
      <th>丸め処理</th>
    </tr>
  </thead>
  <tr>
    <td>DD</td>
    <td>時間が00:00:00 ～ 11:59:59の場合、当日の00:00:00を戻す。
    <br>12:00:00 ～ 23:59:59の場合、翌日の00:00:00を戻す。</td>
  </tr>
  <tr>
    <td>MM</td>
    <td>日にちが1日～15日の場合、当日の1日を戻す。
    <br>16日～末日の場合、翌日の1日を戻す。</td>
  </tr>
  <tr>
    <td>RRまたはYY</td>
    <td>月が1月～6月の場合、当年の1月1日を戻す。
    <br>7月～12月の場合、翌年の1月1日を戻す。</td>
  </tr>
</table>

```:例
SQL> SELECT ROUND(TO_DATE('2021/09/16 10:11:12'), 'DD') FROM dual;
```
```:結果
2021/09/16 00:00:00
```

#### TRUNC
数値を切り捨てる
```
TRUNC(数値[, n])
```

* 数値：切り捨てを行う数値
* n：小数点以下n桁に切り捨てる
* 例) n=1 小数点以下2桁目で切り捨て、小数点以下1桁の数値を返す
* n=2 小数点以下3桁目で切り捨て、小数点以下2桁の数値を返す
* 例) n=-1 一の位で切り捨てられる
* n=-2 十の位で切り捨てられる
* n=-3 百の位で切り捨てられる

##### TRUNCファンクションに指定できる日時書式モデルと丸めの処理

<table>
  <caption>TRUNCファンクションに指定できる日時書式モデルと丸めの処理</caption>
  <thead>
    <tr>
      <th>書式モデル</th>
      <th>丸め処理</th>
    </tr>
  </thead>
  <tr>
    <td>DD</td>
    <td>時間を切り捨て、当日の00:00:00を戻す。</td>
  </tr>
  <tr>
    <td>MM</td>
    <td>日にちを切り捨て、当月の1日を戻す。</td>
  </tr>
  <tr>
    <td>RRまたはYY</td>
    <td>月を切り捨て、当年の1月1日を戻す。</td>
  </tr>
</table>

```:例
SQL> SELECT TRUNC(TO_DATE('2021/09/16 10:11:12'), 'DD') FROM dual;
```
```:結果
2021/09/16 00:00:00
```

#### MOD
数値を除算したときの余りを戻す

```
MOD(n, div)
```
* 数値nを数値divで割ったときの余りを戻す。

```:例
SQL> SELECT MOD(9, 4), MOD(9, 3), MOD(4, 9) FROM dual;
```
```:結果
MOD(9, 4)  MOD(9, 3)   MOD(4, 9)
        1          0           4
```

#### POWER
数値のべき乗を戻す

```
POWER(n, m)
```
* 数値nのm乗を戻す。

```:例
SQL> SELECT POWER(2, 2), POWER(3, 2), POWER(3, 3) FROM dual;
```
```:結果
POWER(2, 2)  POWER(3, 2)   POWER(3, 3)
          4            9            27
```

#### MONTHS_BETWEEN

```
MONTHS_BETWEEN(日付1, 日付2)
```
* 日付1が日付2よりも過去の日付の場合は、負の値を返します。

#### NVL
NVL関数は値がNULL値であった場合に、別の値を返す関数です。

```
NVL(式1, 式2)
```

* 第1引数の値がNULL値の場合、第2引数の値を返します。第1引数の値がNULL値でなければ、そのまま第1引数の値を返す。
* 第2引数に指定する式は、第1引数のデータ型と同じデータ型でなければならない(暗黙的なデータ変換が行われる場合はエラーとならない)。異なるデータ型を返す式を指定するとエラーとなる。

#### NVL2
NVL2関数は値がNULL値か否かを調べ、NULL値以外の場合は値A、NULL値の場合は値Bを返す関数です。

```
NVL2(式1, 式2, 式3)
```

* 第1引数の値がNULL値以外の場合、第2引数の値を返す。
* 第1引数の値がNULL値の場合、第3引数の値を返す。
* 第2引数と第3引数は同じデータ型でなければならない(暗黙的なデータ変換が行われる場合はエラーとならない)。異なるデータ型を返す式を指定するとエラーとなる。

#### NULLIF
NULLIF関数は2つの値を比較して、等しい場合にNULLを返す関数。

```
NULLIF(式1, 式2)
```

* 第1引数と第2引数が等しい場合はNULL値を、等しくない場合は第1引数の値を返す。
* 第1引数にはリテラルのNULL値以外の値を指定しなければなりません。
* 上記のように第1引数にリテラルのNULL値は指定できませんが、第1引数で指定した列の値などが結果としてNULL値となる場合は問題ない。
* 第1引数が結果としてNULL値となる場合は、第2引数がいかなる値であっても、NULL値が返されれる。（第2引数がNULL値であれば等しいのでNULL値、第2引数がNULL値以外であれば等しくないので第1引数のNULL値が返される為）


#### COALESCE
COALESCE関数は引数の値を判定し、最初に見つかったNULL値以外の値を返す関数。

```
COALESCE(式1, 式2 [, 式3 ...])
```

* 引数に指定された式全てがNULL値の場合、COALESCE関数はNULL値を返す。
* COALESCE関数の引数は、すべて同じデータ型の値でなければなりません。異なるデータ型の値を指定するとエラーとなります(暗黙的なデータ変換は行われません)。

#### CASE
```sql
CASE 式 WHEN 条件1 THEN 戻り値1
[WHEN 条件2 THEN 戻り値2 ...]
[ELSE デフォルトの戻り値]
END
```

* 単純CASE式は、式の値と、条件の値を条件1から順に判定し、値が合致した条件に対応する戻り値を返す。
* 式の値がどの条件にも合致しない場合は、ELSE句に指定されたデフォルトの戻り値が返されますが、ELSE句が指定されていない場合はNULL値を返す。