この記事は、シアトルコンサルティング株式会社 Advent Calendar 2021の16日目の記事です。
こんにちは、シアトルコンサルティングの村田です。
はじめに
データベース関連のちょっとした情報を集めました。
関数のちょっと便利な使い方や処理効率の話です。
1つ2つでも身になるネタがあれば幸いです。
1. in句とor句のパフォーマンスは同じ
・WHERE 列1 = 値1 OR 列1 = 値2 OR 列1 = 値3
・WHERE 列1 IN (値1,値2,値3)
上記は同じ結果を得られますが、IN句を使用した方が簡潔で見やすい指定といえます。
ただしIN句はOR演算子のセットとして評価されるためパフォーマンスは変わりません。
つまりIN句でまとめらるものはまとめましょうということです。
2. ワイルドカード「_」で文字数を指定
「 _ 」は任意の1文字を意味するワイルドカードです。
下記は、列1から5文字の値を取得します。（文字の全角・半角は区別されません）
・WHERE 列1 LIKE '_ _ _ _ _'
0文字以上の任意の文字列を意味する「%」との併用も可能です。
%は前方一致、後方一致、部分一致の三種類があります。
下記の場合、最後に「マン」が付く7文字以上のデータが取得されます。
「スーパーマン」と「アイアンマン」は取得されませんが「帰ってきたウルトラマン」は取得できます。
・WHERE 列1 LIKE '%_ _ _ _ _マン'
「%」や「 _ 」を文字列として扱うときにはエスケープ処理(escape)を記述します。
下記のように記述すると「¥」がエスケープ文字となり、次の文字の%を文字列として扱うことができます。
よって「アキラ100%」が取得できます。もし仮に「アキラ99%」がいた場合にも取得されます。
・WHERE 列1 LIKE 'アキラ%¥%' escape '¥'
※「100%」が全角だった場合取得できません。
3. 置換変数「＆」
SQL*Plusでは置換変数が使用できます。
下記のように「&」を記述するとプロンプト内で変数1部分の値を指定できます。
・WHERE 列1 = &変数1
毎回日付等を設定しなおす時とかに使えそうです。
開発よりも保守運用で使用する機会があるかもしれません。
4. UPPER関数で大小文字問わず検索することができる
UPPER関数は引数の文字列を全て大文字に変換します。
下記でSELECT結果は全て大文字で出力されます。
・SELECT UPPER(列1) FROM テーブル1;
またUPPER関数はWHERE句でも使用が可能です。
下記のように記述することで大文字、小文字を問わず取り出すことができます。
・SELECT 列1 FROM テーブル1 WHERE UPPER(列1) = 値1;
（全て小文字に変換するLOWER関数でも同様のことができます。）
5. SUBSTR関数でマイナス値を指定する
SUBSTR関数は引数の文字列を切り出す関数です。
キャラクタ単位でのカウントであるため、該当の文字についてシングルバイトでもマルチバイトでも1文字と判定されます。
バイト単位で判別したい場合はSUBSTRB関数を使用します。
下記は3文字目から3文字が指定され'パーマ'が返されます。
・SUBSTR('スーパーマン',3,3)
2つ目の引数に負の値にすると文字列の後ろから数えて何文字目と指定できます。
下記は'ーマン'となります。
・SUBSTR('スーパーマン',-3,3)
6. ROUND関数でマイナス値を指定する
ROUND関数は引数を四捨五入して返します。
下記は小数点以下第一位になるように四捨五入し、1234.6となります。
・ROUND(1234.567,1)
2つ目の引数に負の値を指定することもできます。
下記は十の位を四捨五入し、1200となります。
・ROUND(1234.567,-2)
7. ADD_MONTH関数において月の最終日は特別
ADD_MONTH関数は引数ヶ月後の日付を返します。
下記は'21-08-20'が返されます。
・ADD_MONTH('21-07-20',1)
ただし月の最終日は特別な値として扱われます。
引数に月の最終日を指定すると返される値も月の最終日となります。
下記で返される値は'21-08-28'ではなく、'21-08-31'となります。
・ADD_MONTH('21-02-28',6)
※うるう年についても自動で判別、計算されます。
2024年の場合は02/29が最終日として扱われます。
さいごに
Oracle Masterの学習中に面白いなーと思ったことまとめてみました。
実務の中で使える知識もあれば、あまり役に立たない雑学的な項目もあります。
in句とor句のパフォーマンスとか気にしません。普通にin句使います。
しかし引数に負の値を指定できる関数や、月の最終日の特別扱いは1年に1回くらい役に立つでしょう。
参考
(1) 西昭彦 飯室美紀 鈴木佐和 岡野友紀 矢島祐子 ORACLE MASTER Bronze Oracle Database 12c[12c SQL基礎]（試験番号：1Z0-061）完全詳解＋精選問題集 SBクリエイティブ株式会社 2014
コーポレートサイト
https://www.seattleconsulting.co.jp/
Wantedly
https://www.wantedly.com/companies/seattleconsulting