Oracle Bronze10g SQL基礎I講座(Ⅳ) - system818 @ ウィキ

第4章 単一行関数について

4.1 単一行関数とは

･値を数値処理したり、文字列を変換したり、値を別のデータ型に変換したりすることができます。
(1行の入力から1つの結果が戻る)

---

4.2 数値関数

(表)数値関数一覧

関数名	機能	書式	説明
CEIL	n以上の最も小さい整数を戻します	CEIL(n) n:計算対象の値
FLOOR	n以下の最も大きい整数を戻します	FLOOR(n) n:計算対象の値	nの値が正ならば切捨て。負ならば切り上げである。
MOD	mをnで割った余りを戻します	MOD(m,n) m:計算対象の値 n:割る値	nが0の時には、mを戻す。mが0の時には、0を戻す。
ROUND	数値を指定桁で丸めます	ROUND(n[,i]) n:計算対象の値、i:四捨五入する桁数を指定	四捨五入する桁数が正数のとき小数点以下の桁数。負数の場合は正数の桁数になる。
TRUNC	数値を指定桁で切り捨てます	TRUNC(n[,i]) n:計算対象の値、i:切り捨てる桁数を指定	四捨五入する桁数が正数のとき小数点以下の桁数。負数の場合は正数の桁数になる。

※CEIL関数…正の数に限っては、切り上げです。負の数の場合は、切り上げにはならない。

---

4.3 文字関数

INICAP関数

機能	書式	説明
文字の各単語の先頭に大文字に、2文字以後を小文字に変換して戻します	INITCAP(s) s:文字列	単語の区切りは、空白、記号等です。

INSTR関数

機能	文字列から指定の文字位置を戻す
書式	s1:検索対象の文字 s2:検索する文字 p:s1の検索を開始する文字位置(負数の場合、文字の最後からの逆検索) o:一致した回数 p,oまたはoは、省略可能
説明	･s1のpの位置からs2を検索して、o番目に一致した文字列を戻します。 ･検索文字と一致しない場合には、0が戻る。

(例･･･INSTR関数)
INSTR('ABCdefGHIABC', 'ABC', -1, 2) ⇒ 1

     ↑              ↑
2回目検出      1回目の検出

※上記は、ABCの数が2回目の検出の際の'ABC'を指している。
※仮にINSTR('ABCdefGHIABC', 'ABC', 1, 2)の場合は1回目の検出のところになるので、
解答は"10番目"になる。

SUBSTR関数

機能	文字を指定位置から指定文字分に切り出した文字を戻します
書式	SUBSTR(s,p[,I]) s:対象の文字列 p:切り出す文字位置(負数の場合、文字の終わりからの指定になる) I:切り出す文字列(省略可、省略すると文字の最後までを切り出す)
説明	sの文字列のp番目から長さIだけの文字を切り出します

LENGTH関数

機能	書式	説明
文字の長さを戻す	LENGTH(c) c:文字列	cの文字の長さを戻し、文字がNULLの場合はNULLを戻します。

■文字関数での全角文字の扱い
･文字関数は、デフォルトで全角文字と半角文字を同じ1文字として扱う。
･LPAD、LTRIM、RPAD、RTRIM、TRIM関数には、バイト単位で扱う関数ではない。

LENGTHB関数

機能	書式	説明
文字のバイト数を戻す	LENGTHB(c) c:文字列	cの文字のバイト長を戻し、全角の1文字は2バイトです。文字がNULLの場合は、NULLを戻す。

LOWER関数

機能	書式	説明
文字を小文字に変換	LOWER(c) c:文字列	cのすべての文字を小文字に変換

UPPER関数

機能	書式	説明
文字を大文字に変換	UPPER(c) c:文字列	cのすべての文字を大文字に変換

LPAD関数

機能	書式	説明
文字の左側に文字を埋めて戻す	LPAD(c1,n[,c2]) c1:文字列 n:変換して戻す文字列 c2:埋める文字	c1の左側に、c1の文字列がnになるまで、c2の文字を埋めます

LTRIM関数

機能	書式	説明
文字の左側から指定文字を削除	LTRIM(c[,s]) c:文字列 s:削除する文字	cの左側の連続したsの文字を削除。cにs以外の文字を見つけたら処理終了

TRIM関数

機能	書式	説明
文字の指定方向から指定文字を削除	TRIM([[LEADING┃TRAILING ┃ BOTH] s FROM ] c c:文字列 s:削除する文字	cの左側の連続したsの文字を削除。sを省略すると空白が使用される。

---

4.4 日付関数

ADD_MONTH関数

機能	書式	説明
日付に月数を加算した日付を戻す	ADD_MONTHS(d,n) d:日付 n:日付に加算する月数(マイナスの値を指定することも可能)	日付dにnの月数を加算した日付を戻す

LAST_DAY関数

機能	書式	説明
指定月の月末の日を戻す	LAST_DAY(d) d:日付	日付dの月末の日付を戻す

MONTHS_BETWEEN関数

機能	書式	説明
2つの日付の月間数を戻す	MONTHS_BETWEEN(d1,d2) d1:日付,d2:日付	日付d1-d2の月数を戻す

NEXT_DAY関数

機能	日付以降の最新の指定曜日の日付を戻す
書式	NEXT_DAY(d,c)
説明	日付dから後の指定曜日の日付を戻します。 現在の日付が指定曜日の時は、次の週の指定曜日の説明が戻されます。 曜日は、'木'、'木曜日'、5(曜日の番号)等で指定することができます。

SYSDATE関数

機能	書式	説明
現在の日付に戻す	SYSDATE	日付は、サーバの日付情報で、日付には年月日以外にも時分秒も含まれます。

※日付の演算として、
･日付 ＋ 日付
･日付 ＊ 日付
･日付 / 日付
･日付 / 数値　等の演算はできない。

ROUND関数(日付)

機能	書式	説明
指定した単位で日付を戻す	ROUND(d,[f]) d:日付,f:日付書式モデル	日付書式モデルとして、年、月、日、時、分、秒等の単位を指定できる。

※YEARの切り上げ時期
･年を表す書式は、'YEAR'だけでなく、'YYYY','YYY','YY','Y'を指定できる。

※MONTHの切り上げ時期
･月を表す書式は、'MONTH'だけでなく'MM','MON'を指定できます。

---

4.5 変換関数

■日付型データと数値型データを直接変換する関数はない。

(例)日付型データから数値型データへ変換してから数値型データに変換します。
日付型データ→文字型データ→数値型データ

(例)数値型データを、文字型データに変換してから日付型データに変換します。
数値型データ→文字型データ→日付型データ

TO_CHAR関数(日付)

機能	日付dを日付書式モデルに従い文字列データに変換
書式	TO_CHAR(日付データ,日付書式)
説明	日付データを日付書式の形式の文字列に変換する

TO_DATE関数(数値)

機能	文字型データcを日付書式モデルに従い日付型データに変換
書式	TO_DATE(文字列,　日付書式)
説明	日付書式の形式で指定した、文字列をDATE型に変換する

TO_NUMBER関数

機能	文字型データcを数値書式モデルに従い数値型データに変換します。
書式	TO_NUMBER（ 数値文字列, [ 書式 ] ）
説明	TO_NUMBERは引数で指定した文字列を、指定した書式に沿って、数値に変換します。

---

4.6 NULLを扱う関数

COALESCE関数

機能	書式	説明
リストの最初のNULLでない値を戻す	COALESCE(a,b,c,....)	関数内のa,b,c,...で、最初のNULLでない値を戻す。

NULLIF関数

機能	書式
aとbが等しい時、NULL値を戻す	NULLIF(a,b) a:判定対象の値 b:判定対象の値

NVL関数

機能	書式
aがNULLのとき、bの値を戻します。aがNULLでないとき、aの値を戻します。	NVL(a,b) a:判定対象の値 b:判定対象の値

NVL2関数

機能	書式
aがNULLでない時、bの値を戻します。aがNULLのとき、cを戻します。	NVL2(a,b,c) a:判定対象の値 b:aの値がNULL以外のとき、返す値 c:aの値がNULLの時返す値

---

4.7 その他の関数

DECODE関数

機能	対象と一致する値に対応するデータを戻す
書式	DECODE(列名 | 式,条件1,値1,...{デフォルト値})
説明	式と条件が一致する最初の値が戻される。どれにも一致しなった場合はELSE句に指定したデフォルト値が返される。

※CASE句とDECODE句との違い
CASE式では、WHEN句でEXISTS、比較条件、IN、BETWEEN、LIKEなどの演算子が使用可能だが、DECODE関数では使用不可能。
DECODE関数では、DECODEは NULL = NULL の評価は True となるが、CASE式ではNULLとなるCASE式でNULLを評価する場合は、WHEN句でIS NULLを使う。

GREATEST関数

機能	書式	説明
リスト内の最大値を戻す	GREATEST(e,[...] e,[...]:判定対象の値)	判定対象は、数値、文字列値、日付値等です。

---

4.8 書式モデル

こちらの章については
書式モデルを参考にしてください。