現在勉強しているオラクルマスターブロンズ（SQL）についてまとめたものです。
自分のアウトプット用なので見やすさなどは期待しないでください。
データ型の変換と変換関数
変換関数は、引数として受け取ったデータのデータ型を変換して戻す関数で、暗黙的なデータ型変換と明示的なデータ型変換がある。
暗黙的なデータ型変換
Oracleサーバーが自動的に実行するデータ型の変換を暗黙的なデータ型変換と呼ぶ。
例えば、関数の引数の場合受け入れるデータ型が決まっているため、想定されているものと別のデータ型のデータを指定すると、そのままでは正しく処理できない。
そのようなときにOracleサーバーによって暗黙的なデータ型変換が行われる。
また、暗黙的なデータ型変換は、データ型の変換が意味を持つ場合にのみ成功する。
明示的なデータ型変換と主な変換関数
ユーザーが明示的に変換関数を使用して実行するデータ型の変換を明示的なデータ型変換と呼ぶ。
明示的なデータ型変換のほうが、効率的に処理を実行できる、実行環境に依存しないSQL文を記述できる、データ型を変換する箇所が分かりやすくなるなどの理由から使用が推奨されている。
明示的なデータ型変換の関数をまとめると以下のようになる。
なお、日付値を数値に変換する関数や数値を日付値に変換する関数は存在しない。
日付値ーTO_CHAR関数→文字値ーTO_NUMBER関数→数値
日付値←TO_DATE関数ー文字値 ←TO_CHAR関数ー数値
（DATE型） （CHAR型、VARCHAR2型） （NUMBER型）
TO_CHAR関数（日付値→文字値）
TO_CHAR関数は引数に日付を指定すると、指定された日付書式を使用して日付値を文字値に変換して戻す。
また、NLSパラメータを指定することで、日付書式の言語環境を設定することもできる。
主な日付書式の要素
| 要素 | 説明 |
|---|---|
| SCCまたはCC | 世紀、Sを指定すると紀元前の日付の先頭に「-」が付く |
| SYYYYまたはYYYY | 年、Sを指定すると紀元前の日付の先頭に「-」が付く |
| YYYまたはYY、Y | 年の下３桁または２桁、１桁 |
| IYYYまたはIYY、IY、I | ISO基準に基づく４桁、３桁、２桁または１桁の年 |
| SYEARまたはYEAR | スペル表記による年 |
| RR | 年の下２桁、YYとは「世紀」の扱い方は異なる |
| Q | 年の四半期 |
| MM | 月、２桁の値 |
| MONTH | 空白が埋め込まれた９文字の長さの月の名前 |
| MON | 月の名前、３文字の省略形 |
| RM | ローマ数字で表した月 |
| WWまたはW | 年または月における週 |
| DDDまたはDD、D | 年または月、週における日 |
| DAY | 空白が埋め込まれた９文字の長さの曜日 |
| DY | 曜日、３文字の省略形 |
| IW | ISO基準に基づく、年における週 |
| AMまたはPM | AMまたはPMを示す要素 |
| A.M.またはP.M. | ピリオドを使用したA.M.またはP.M.を示す要素 |
| HHまたはHH12、HH24 | 時間、時間（１～１２）または時間（０～２３） |
| MI | 分（０～５９） |
| SS | 秒（０～５９） |
| SSSSS | 午前０時からの経過時間（０～８６３９９） |
| 「/」、「.」、「,」などの半角記号 | 半角記号はそのまま結果に表示される |
| "of the" | 二重引用符で囲まれた文字列はそのまま結果に表示される |
| FM | 埋め込みモードの有効・無効を切り替える 埋め込みモードを有効にすると数値の先行０や文字値の前後に含まれるスペースが取り除かれて表示される |
TO_CHAR関数の引数に**NLSパラメータ（nls_date_languages='言語'）**を指定することで、関数内で使用する言語環境を任意のものに変更することができる。
日付書式「DD」のような、数値で表示される日付書式の後ろに**「TH」や「SP」などの接尾語を指定すると、これらの表示形式を順序表記やスペル表記**に変更することができる。
数値で表示される日付書式に使用できる接尾辞
| 接尾辞 | 説明 |
|---|---|
| TH | 順序表記（例：DDTH→４TH） |
| SP | スペル表記（例：DDSP→FOUR） |
|
SPTHまたはTHSP （スペル表記した順序として表示） |
順序表記+スペル表記（例：DDSPTH→FOURTH） |
日付書式の大文字・小文字は区別されるので、「DDth」を「ddth」に変更すると、実行結果も「25th」や「02nd」のように小文字になる。
日付書式にFM要素を指定して埋め込みモード（デフォルトで有効）を無効にすると数値の先行０や文字値の前後に含まれるスペースが取り除かれて表示される。
なお、FM要素を指定すると、FM要素以降の全ての日付書式に対する埋め込みモードが無効になる。
埋め込みモードの有効・無効は、FM要素が指定されるたびに切り替わるので、「ddth」の先行０は取り除きたいが、「Month」の後続のスペースは残したいなどの場合は、Monthの前に再度FM要素を指定して（fmMonthとして）、埋め込みモードを有効にする。
TO_CHAR関数（数値→文字値）
引数に数値を指定すると、指定された数値書式を使用して数値を文字値に変換して戻す。
また、NLSパラメータを指定することで、小数点文字や桁区切り、各国通貨記号、国際通貨記号を指定することもできる。
なお、指定した桁数が実際のデータの桁数よりも少ない場合など、値を適切に変換できない場合は「#」が表示され、数値書式の一の位に０を指定すると先行０も表示されるが、９を指定すると先行０は表示されない。
主な数値書式の要素
| 要素 | 説明 | 例 | 結果 |
|---|---|---|---|
| 9 | 数値の位置（９の数で表示桁数が決まる） | 99999 | 1234 |
| 0 | 先行０を表示 | 099999 | 001234 |
| $ | ドル記号の表示 | $999999 | $1234 |
| L | ローカル通貨記号の表示（日本語環境では「¥」） | L999999 | ¥1234 |
| D | 指定された位置に小数点を表示 | 999D999 | 1.234 |
| . | 指定された位置に小数点を表示 | 999.999 | 1.234 |
| G | 指定された位置に桁区切りを表示 | 999G999 | 1,234 |
| , | 指定された位置に桁区切りを表示 | 999,999 | 1,234 |
| MI | 右にマイナス記号を表示（負の値の場合） | 999999MI | 1234- |
| PR | 負の値を<>で囲む | 999999PR | <1234> |
| EEEE | 科学表記法で表示 | 99.999EEEE | 1.234E+03 |
| V | １０のn乗を乗算する（nはVの後ろの９の数） | 9999V99 | 123400 |
| S | +または-を表示 | S999999 | +1234または-1234 |
TO_DATE関数
引数に指定された文字値を日付値に変換して戻す関数で、日付書式やNLSパラメータはTO_CHAR関数と同じ。
この関数は主に、デフォルトの日付書式と異なる形式の文字列を日付値に変換する場合に使用する。
なお、時刻が指定されなかった場合は午前０時０分０秒として処理され、日にちが指定されていない場合は１日、月が指定されていない場合は現在の月、年が指定されていない場合は現在の年として処理される。
暗黙的なデータ型変換は、以下のような点が原因で失敗することがあるので、変換後のデータ型として想定されるデータが複数ある時は、変換関数を使用して明示的なデータ型変換を行う必要がある。
- 日付値は「日付値-日付値」だけでなく「日付値-数値」でも操作できる。
- Oracleサーバーは算術式の中に文字列を見つけると、数値に変換しようとする。
日付書式のYY要素とRR要素はいずれも、年の下２桁を表す要素だが、受けれた値の世紀の扱い方が異なる。
YY:受け入れた値を、常に現在の世紀として扱う。
RR:受け入れた値を、現在の年に近い世紀として扱う。
TO_DATE関数で文字値を日付値に変換する際、日付書式要素と日付文字列内の対応する文字が一致しない場合、Oracleサーバーは次の表に示す通り元の書式要素の代わりに、別の書式要素の適用を試行する。
| 元の書式要素 | 代わりに試行する書式要素 |
|---|---|
| MM | MONおよびMONTH |
| MON | MONTH |
| MONTH | MON |
| YY | YYYY |
| RR | RRRR |
TO_NUMBER関数
引数に指定された文字値を数値に変換して戻す関数で、数値書式はTO_CHAR関数と同じ。
汎用関数と条件式
主な汎用関数とその使い方
汎用関数は、引数として受け取ったデータに対してNULL値に関する操作を行う関数で、主要なものは以下がある。
NVL関数は、引数の式１に指定された値がNULL値以外の場合は式１を、NULL値の場合は式２を戻す関数で、戻り値のデータ型は式１のデータ型と同じになる（必要に応じて暗黙的なデータ型変換が行われ、変換できない場合はエラーとなる）。
NVL2関数は、引数の式１に指定された値がNULL値以外の場合は式２を、NULL値の場合は式３を戻す関数で、戻り値のデータ型は式２のデータ型と同じになる（必要に応じて暗黙的なデータ型変換が行われ、変換できない場合はエラーとなる）。
NULLIF関数は、引数に指定された２つの値を比較して、等しい場合はNULL値を戻し、等しくない場合は式１を戻す関数。
なお、式１にはリテラルのNULL値以外を指定する必要がある。
COALESCE関数は、引数に指定された式リストを先頭からチェックし、最初に見つかったNULL値以外の値を戻す関数（すべての式がNULL値の場合、NULL値を戻す）。
なお、式リストに指定するデータは、すべて同じデータ型にする必要がある（数値、文字、日付をまたがる暗黙的なデータ変換は行われない。）
条件式とデコード関数
CASE式を使用すると、SQL文の中にIF-THEN-ELSEロジックを実装できる。
具体的には、式の値と条件式の値を条件式１から順番に比較し、最初に一致した条件式に対応した戻り値を戻す。
また、どの条件式とも一致しなかった場合はデフォルトの戻り値を戻し、一致する条件がなくELSE句が省略されている場合はNULL値を戻す。
CASE式の中でリスト化された条件を順番に評価し、最初にTRUEを戻す条件に対応したデータを戻すCASE式のことを検索CASE式と呼ぶ。
DECODE関数は、CASE式と同様にIF-THEN-ELSEロジックを実装する、Oracleデータベース固有の関数で、処理手順や戻り値の値はCASE式と同じ。
なお、一致する条件がなく、デフォルトの戻り値が省略されている場合はNULL値を戻す。