OracleBronze ４章

SQL関数

Oracleにあらかじめ組み込まれているファンクションがあります。
例えば、小文字を大文字に変換するUPPERなどです。UPPERは列名指定で受け取った文字列を大文字に変換してくれるのですが、これは１件毎に処理し１件毎に結果を戻す単一行関数と呼ばれるものです。
対してavgの様に複数の結果を受け取って一つの値を返す、グループ関数というものもあるのですが、本章では単一行関数を扱います。

関数名( 引数1, 引数2…) ;

が基本形なのですが、これはSELECT句以外でもWHERE句、ORDER BY句でも使用できます。

文字列関数

UPPER／LOWER

UPPER( 文字列 )

文字列を全て大文字に変換する。
WHERE句でも使えます。その時は比較する文字列は大文字じゃないとダメですよ。
ちなみに、こんな感じで使います。

SELECT UPPER( yomi ) FROM EMPLOYEES ;

 

大文字にすることが出来れば、小文字にすることも出来ますよね。

LOWER( 文字列 )

文字列を全て小文字に変換する。

INITCAP

INITCAP( 文字列 )

先頭文字を大文字にします。

CONCAT

CONCAT( 文字列1, 文字列2 )

2つの文字列を結合して戻す。

SUBSTR

SUBSTR( 文字列, m ,n )

文字列のm番目の文字からn文字分の文字列を戻す。
ちょっと実例を見てみましょうか。

SELECT SUBSTR( 'SUGIMOTO', 5 ,4 ) …

だと、MOTOと表示されることになります。
ちなみに、nは省略可能で、その際はm文字目以降の全てが表示されます。

LENGTH

LENGTH( 文字列 )

文字列の文字数を返します。全角も記号も半角も1文字です。

INSTR

INSTR( 文字列1, 文字列2, m, n )

ちと複雑ですね。
文字列1のm文字目から文字列2を検索し、n回目に一致した文字列の位置を返します。
mとnは省略化で、その場合はどちらも1が入ります。

INSTR( 文字列1, 文字列2 )

そうなると、ちょっと簡単。
文字列1から文字列2を検索してヒットした文字列の位置を返す。

LPAD／RPAD

LPAD( 文字列, n, '埋め込み文字' )
RPAD( 文字列, n, '埋め込み文字' )

文字列がn文字になるように埋め込み文字を埋めて戻す。
Lは左に、Rは右に、埋め込みます。意味分かんないでしょうｗ

LPAD( yomi, 10, '*' )

******sato
：（以下略）
こうなる。って感じです。当たり前ですが、Rなら、
sato******

TRIM

TRIM( 文字列 )

文字列の前後にある削除文字（デフォルトは半角スペース）を削除し戻す。
これだけなら簡単なのですが、以下の様な使い方も出来ます。

TRIM( LEADING,  '削除文字', FROM 文字列 )
TRIM( TRAILING, '削除文字', FROM 文字列 )
TRIM( BOTH,     '削除文字', FROM 文字列 )

説明も面倒なので自分で調べましょう。

REPLACE

REPLACE( 文字列, 変更前文字列, 変更後文字列 )

変更前文字列を変更後文字列に置き換えて返す。要は、置換ですね。
変更後文字列は省略可能で、省略した場合は変更前文字列が削除されたものを返します。

数値関数

ここまでは文字列操作でしたが、ここからは数値を扱う関数をご紹介します。

ROUND

ROUND( 数値, n )

数値の小数点以下n桁に四捨五入して返す。
nは省略可で、整数値に四捨五入します。0が入っていると同じ扱いですね。

TRUNC

TRUNC( 数値, n )

数値の小数点以下n桁に切り捨てして返す。
nは省略可で、整数値になるよう切り捨てます。0が入っていると同じ扱いですね。
ROUNDとほぼ同じですね。

MOD

MOD( n, m )

nをmで割った余りを戻す関数です。
2章で四則演算が使えると説明があったと思いますが、余り算はありませんでしたよね。関数として実装されています。

日付関数

DBでは日付を扱うことが割と多いと思います。
関数の前にちょっと日付についてご説明いたします。

日付の基礎知識

日付値は、-年-月-日-時-分-秒で内部的な数値を持っている
デフォルトの書式（RR-MM-DD）がある。
格納形式は他の形にも出来るが別章参照。

日付の計算

加算や減算が出来ます。

日付 + 数値
日付 - 数値

日付として計算なので、+1すると一日後。つまり明日ですね。
-1は一日前、つまり昨日となります。

日付 + 数値/24
日付 - 数値/24

という記載になると、数値は時間として計算されます。

また日付値同士の減算が出来ます。この時の結果は、日数で表示されます。

日付 - 日付

ちなみに、加算は出来ませんので、ご注意を。

SYSDATE

現在の日付を返します。

SYSDATE

 

MONTHS_BETWEEN

引数１，引数２の間の月数を返します。

SYSDATE( 日付1, 日付2 )

 

ADD_MONTHS

引数に指定した日付のnヶ月後の日付を返す。

ADD_MONTHS( 日付, n )

最終日の扱いが独特なので気をつけましょう。
2/28の1ヶ月後は、3/31という考え方です。つまり末日扱いですね。

NEXT_DAY

引数に指定した日付の翌日以降に指定した曜日になる最初の日を返す。

NEXT_DAY( 日付, '曜日' )

 

LAST_DAY

引数に指定した日付を含む月の最終日の日付を返す。

LAST_DAY( 日付 )

 

ROUND

数値関数でもやったROUNDです。その引数に日付を受け入れるとどうなるかということです。

ROUND( 日付 [, '書式'］ )

書式というのは、
年(YEAR)/月(MONTH)/日(DD)のどれかだと思ってください。

年：6/30以前なら当年の1月1日午前0時、以降なら翌年の1月1日午前0時
月：15日以前なら当月の1日午前0時、以降なら翌月1日午前0時
日：正午以前なら当日の午前0時、以降なら翌日の午前0時

TRUNC

TRUNCも数値関数でやってますよね。切り捨てです。
ROUND同様、これも日付を受け付けることが出来ます。

TRUNC( 日付 ［, '書式'］ )

書式は、年(YEAR)/月(MONTH)/日(DD)と、ROUNDと同様です。

年：当年の1月1日午前0時
月：当月の1日午前0時
日：当日の午前0時

それでは以下をやってみましょう。
今回は色々と飛ばしました。関数については必要になった時に調べましょう。

以下のデータは、EMPLOYEESテーブルから取得してください。
・yomiを全て大文字に変換して表示
・yomiの先頭文字を大文字に変換して表示
・enameとyomiをくっつけて表示
・入社年月日の月だけ抽出して表示
・yomiの文字列をカウントして表示
・yomiからdaの文字を検索し何文字目にあるか表示
・入社してから今日までの日数を表示