ブログ投稿用のカテゴリ
oracleの独自関数のCOALESCE（コアレス）関数の用途についてメモしておきます。
COALESCE関数は引数のリストから最初のNULL以外の値を返却するOracle独自関数となります。
NVL2関数との違いは必ずしも引数へ指定するデータ型を全て合わせる必要はないことです。
ただデータの一貫性を保ちたい場合は極力データ型は合わせた方が望ましいです。
|
1 |
SELECT COALESCE(START_DATE, END_DATE, sysdate) FROM CLIENT_ADDRESS; |
上記例ではSTART_DATEがNULLであればEND_DATEの値を、END_DATEもNULLであればsysdateを返却します。
oracleの独自関数としてNVL関数やNVL2関数があります。
知ってると結構便利な関数なので、この２つの関数の違いについて整理しておきます。
NVL関数は第１引数がNULLなら第２引数の値（代替値）を返します。
もし第１引数の結果がNULLでなければ、そのまま第１引数の値を返します。
注意点として第１引数と第２引数へは同じデータ型を指定する必要があります。
|
1 |
SELECT NVL(END_DATE, sysdate) FROM CLIENT_ADDRESS; |
NVL2関数は第１引数がNULLの場合に、第２引数の値を返却し、第１引数がNULLの場合は第３引数の値を返却します。
注意点として第１引数、第２引数、第３引数に指定する値は全て同じデータ型を指定する必要があります。
|
1 |
SELECT NVL2(END_DATE, START_DATE, sysdate) FROM CLIENT_ADDRESS; |
Oracle の NVL／NVL2 関数は便利な反面、以下のような点に留意して使うとより安全・効率的です。
データ型制約に注意する
NVL／NVL2 では、引数に与える値が「同じデータ型」でなければなりません（Oracle の仕様）という制約があります。
たとえば、日付型と文字列型を混在させて使おうとすると、意図しない型変換やエラーを招く可能性があります。
NULL の扱いが複雑な場合には COALESCE の利用も検討する
複数の候補値を順番に評価して最初に NULL でないものを返すような処理をしたい場合は、NVL／NVL2 よりも COALESCE のほうが可読性・拡張性の面で優れるケースがあります。
たとえば、複数の列を順番にチェックして最初の非 NULL 値を採りたいときなどには COALESCE のほうが直感的に記述できます。
パフォーマンス面の配慮
NULL チェック・代替値の置き換えという処理自体は軽い操作ですが、複雑な SQL や大規模データセットで組み合わされると、意図せぬオーバーヘッドになることがあります。
特にインデックス条件や結合条件の中で使う場合は、実行プランを確認して予期せぬフルスキャンなどになっていないか注意しましょう。
意図の可視化
関数を多用するクエリは読みづらくなりがちです。
「なぜこの列で NULL チェックをするのか」「代替値にはなぜこの値を選んだか」といった背景を、コメントやドキュメントとして残しておくと、後から見直すときに助けになります。
性能試験などであるテーブルに大量データの作成が必要になった場合にINSERT文をループで処理できれば便利！という事で、SQLとロジックを組み合わせたストアドプロシージャでのサンプルプログラムとなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE -- 変数の宣言 counter NUMBER := 1; loop_limit NUMBER := 10; goods_id NUMBER := 1; BEGIN WHILE counter <= loop_limit LOOP -- 実行するSQL INSERT INTO USER1.GOODS (GOODS_ID,NAME,GOODS_CODE,PRICE) VALUES (goods_id,'パソコン',101,5000); -- インクリメント counter := counter + 1; -- カウンタをインクリメント goods_id := goods_id + 1; -- GOODS_IDをインクリメント END LOOP; -- 正常終了すれば自動的にCOMMITされるため明示的なCOMMITは不要。途中で失敗した場合は全件ロールバックされる -- DBクライアントソフトが自動コミットしない場合は明示的なCOMMITが必要 END; |
上記の例では、loop_limitに指定した件数分「GOODS」テーブルへレコードを追加するサンプルコードです。
上記の例では、loop_limitを10としていますが、必要に応じて任意の数値に変更できます。
DBeaverで上記のストアドプロシージャを実行する場合は「SQLスクリプトを実行する」を選択することで正常に実行されます。
Excelで文字列として入力済の値をまとめて数値形式へ変換する方法です。
以下の様にA1～A3セルへは文字列として入力しており、A4セルで集計してますが文字列なので0表示されてしまっています。
このA1～A3セルの文字列を数値形式に変換してみます。
日頃からスパムメールの類には注意していたつもりでしたが、以下の様なメールが届いてフィッシングメールとは気付かずにリンクをクリックしてしまったので注意喚起として記載します。
メールの件名：「お知らせ: メールボックスのサイズがクォータ制限に達しました」
メール内容
ちゃんと見れば送信元のメールアドレスが疑わしいアドレスだったり、メールボックス制限に達してるのにメールが送られてきてたりと不審な点に気づくはずなんですが、このメールを見た瞬間はフィッシングメールと気付かず「メールクォータを変更する」をクリックしてしまい、メールアドレスが自動で入力しているPOPパスワードの入力を促す画面へ誘導されてしまいました。。
私の場合はPOPパスワードを忘れていてプロバイダへ連絡してみたら「そのようなメールは送っていないのでフィッシングメールなので削除して下さい」とご指摘頂いたので気付くことが出来ました。
皆様もご注意を^^;
Oracleではデータベースを作成すると自動的に以下の表領域が作成されます。
表領域 説明
SYSTEM Oracleサーバーがデータベースを管理するために使用する表領域
SYSAUX SYSTEM表領域の補助表領域
TEMP データベースのデフォルトの一時表領域
UNDOTBS1 UNDO表領域
USERS 永続表領域
SYSやSYSTEMユーザー以外のユーザー用のデフォルト表領域
EXAMPLE データベース作成時に「サンプル・スキーマの作成」を指定すると作成される表領域
SQLで単語の先頭1文字目のみ小文字から大文字へ変更したい場合は、INITCAP関数を使用することで可能となります。引数に変換したい文字を指定すればOKです。単語の区切りはスペースかアルファベット以外の文字（ハイフン「-」、カンマ「,」、アンダースコア「-」など）を区切り文字として見做して変換されます。
|
1 2 |
SELECT INITCAP(g.NAME) FROM GOODS g; |
Eclipseでコーディングしている際、スネークケースとキャメルケースの切り替えはショートカットキーを利用することで簡単に実施出来ます。ローカル変数などを定義する際に結構便利な機能となります。
Eclipseのバージョンによっては使用出来ない可能性もあるのでご注意下さい。
Oracleデータベースを起動すると内部的に以下の段階でデータベースが使用可能な状態となります。
段階 ステータス 状態
1 SHUTDOWN データベースが停止している状態
2 NOMOUNT インスタンスが起動している状態
※制御ファイル、データファイル、REDOログファイルはクローズ
3 MOUNT インスタンスが起動し、制御ファイルがオープンしている状態
※データファイル、REDOログファイルはクローズ
4 OPEN インスタンスが起動し、制御ファイル、データファイル、REDOログ・ファイルもオープンしている状態
※一般ユーザーが使用可能な状態
SQLで指定した日付項目（該当年月）の月末日を取得するには「LAST_DAY」関数を使用します。うるう年でも正確な月の最終日を取得出来ます。
| 関数＼DBMS | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|
| LAST_DAY | ○ | ○ | ✕ | ○ |
| EOMONTH | ✕ | ✕ | ○ | ✕ |
|
1 2 |
SELECT LAST_DAY(b.BIRTHDAY) FROM BIRTHDAY b |
