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スクリプトを実行する」を選択することで正常に実行されます。
Oracleではデータベースを作成すると自動的に以下の表領域が作成されます。
表領域 説明
SYSTEM Oracleサーバーがデータベースを管理するために使用する表領域
SYSAUX SYSTEM表領域の補助表領域
TEMP データベースのデフォルトの一時表領域
UNDOTBS1 UNDO表領域
USERS 永続表領域
SYSやSYSTEMユーザー以外のユーザー用のデフォルト表領域
EXAMPLE データベース作成時に「サンプル・スキーマの作成」を指定すると作成される表領域
Oracleデータベースを起動すると内部的に以下の段階でデータベースが使用可能な状態となります。
段階 ステータス 状態
1 SHUTDOWN データベースが停止している状態
2 NOMOUNT インスタンスが起動している状態
※制御ファイル、データファイル、REDOログファイルはクローズ
3 MOUNT インスタンスが起動し、制御ファイルがオープンしている状態
※データファイル、REDOログファイルはクローズ
4 OPEN インスタンスが起動し、制御ファイル、データファイル、REDOログ・ファイルもオープンしている状態
※一般ユーザーが使用可能な状態
Oracleデータベースでは以下の制約を設定することが出来ます。
制約 説明
NOT NULL制約 NULL値の設定を許可しない
UNIQE制約
（一意キー制約）一意の値のみ許可します。複数レコード登録時、重複する値を許可しません。NULL値は許可します。
CHECK制約 指定した条件の値のみ許可します。
PRIMARY KEY制約
（主キー制約）一意の値のみ許可し、重複する値は許可しません。NULL値も許可しません。
FOREIGN KEY制約
（外部キー制約）指定した親テーブルに存在する値のみ許可します。
|
1 2 3 4 5 6 7 |
CREATE TABLE "USER1"."BIRTHDAY" ( "NAME_NO" NUMBER, "NAME" VARCHAR2(50) CONSTRAINT "BIRTHDAY_NAME_NN" NOT NULL ENABLE, "BIRTHDAY" DATE, "UPDATE_DATE" TIMESTAMP (6), CONSTRAINT "BIRTHDAY_NAME_NO_U" UNIQUE ("NAME_NO") ); |
顧客の有効住所などを管理しているテーブルからある特定の日付時点で有効な住所を取得する方法をメモしておきます。
|
1 2 3 4 5 |
SELECT ca.* FROM CLIENT_ADDRESS ca WHERE TRUNC(ca.START_DATE) <= '2022-03-31' AND ('2022-03-31' <= TRUNC(ca.END_DATE) OR ca.END_DATE IS NULL); |
Oracle DBのプロセス構造は大きく「ユーザープロセス」、「サーバープロセス」、「バックグラウンドプロセス」があります。
| 種類 | 説明 |
|---|---|
| DBWn （データベースライター） | データベースバッファキャッシュ内の変更されたデータをデータファイルへ書き込む |
| LGWR （ログライター） | REDOログバッファにあるREDOログをREDOログファイルへ書き込む |
| CKPT （チェックポイント） | チェックポイント情報を制御ファイルとデータファイルヘッダーへ書き込む |
| SMON （システムモニター） | インスタンスのリカバリを実行する |
| PMON （プロセスモニター） | ユーザープロセス障害からリカバリを実施する |
| ARCn （アーカイバー） | REDOログファイルのコピー（アーカーブファイル）を作成する |
| REDO （リカバラ） | 分散トランザクションに関する障害を自動的に解決する。 |
| MMON （マネージメントモニター） | メモリー内の統計情報をスナップショットとして定期的にデーターベースへ格納する。 |
| MMAN （メモリーマネージャ） | メモリーを必要な場所に動的に割り当てる |
SQLでは特定の項目の昇順、降順ではなく任意の順序で並べ替えて取得することも出来ます。
任意の順序でソートするにはORDER BY句でCASE文を指定する事で取得することが出来ます。
「BIRTHDAY」テーブル
|
1 2 3 4 5 6 |
FROM BIRTHDAY b ORDER BY CASE b.NAME WHEN '佐藤 花子' THEN 1 WHEN '石川 一' THEN 2 WHEN '山田 太郎' THEN 3 END; |
SQLでsysdateなどの日付型へ加算、減算してxx日後、xxヶ月後、xx年後を求める方法をメモしておきます。
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 30/86400 "本日の日付 + 30秒", sysdate - 30/86400 "本日の日付 - 30秒" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 5/1440 "本日の日付 + 5分", sysdate - 5/1440 "本日の日付 - 5分" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 2/24 "本日の日付 + 2時間", sysdate - 2/24 "本日の日付 - 2時間" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(sysdate + 1,'YYYY-MM-DD') "本日の日付 + 1日", TO_CHAR(sysdate - 1,'YYYY-MM-DD') "本日の日付 - 1日" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 1),'YYYY-MM-DD') "本日の日付 + 1ヶ月", TO_CHAR(ADD_MONTHS(sysdate, -1),'YYYY-MM-DD') "本日の日付 - 1ヶ月" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 12),'YYYY-MM-DD') "本日の日付 + 1年", TO_CHAR(ADD_MONTHS(sysdate, -12),'YYYY-MM-DD') "本日の日付 - 1年" FROM dual; |
本記事では Oracle を前提に日付演算を紹介しましたが、他の主要データベースでも「xx日後／xxヶ月後／xx年後」を取得する方法が存在します。移植性や比較の参考として以下に整理します。
| DBMS | 日数加算の例 | 月・年加算の例 |
|---|---|---|
| Oracle | SYSDATE + 3 | ADD_MONTHS(SYSDATE, 1)（1ヶ月後） / ADD_MONTHS(SYSDATE, 12)（1年後） |
| MySQL | DATE_ADD(NOW(), INTERVAL 3 DAY) | DATE_ADD(NOW(), INTERVAL 1 MONTH) / INTERVAL 1 YEAR |
| SQL Server | DATEADD(day, 3, GETDATE()) | DATEADD(month, 1, GETDATE()) |
| PostgreSQL | CURRENT_DATE + INTERVAL '3 day' | + INTERVAL '1 month' / '1 year' |
Oracle の ADD_MONTHS('2024-01-31', 1) → 2024-02-29（存在しない日付は月末に補正）
MySQLやPostgreSQL でも同様に月末補正される場合があります
契約更新日などで「きっちり同日を基準にしたい」場合は仕様確認が必要です
支払期限：請求日＋30日
契約更新：契約開始日から6ヶ月後／1年後
リマインダー：イベント前7日／前1時間
登録日を基準としたステップメール通知
ADD_MONTHS(SYSDATE, -1) → 1ヶ月前
SYSDATE - 7 → 7日前
📌 上記を踏まえると、「日付加算はDBごとに関数が異なる」「月末や閏年の補正挙動を理解しておく」という点を意識しておくと、より安全なSQL設計につながります。
