「データベース」カテゴリーアーカイブ

Oracle:COALESCE関数の使い方

oracleの独自関数のCOALESCE(コアレス)関数の用途についてメモしておきます。

COALESCE関数とは

COALESCE関数は引数のリストから最初のNULL以外の値を返却するOracle独自関数となります。

NVL2関数との違いは必ずしも引数へ指定するデータ型を全て合わせる必要はないことです。

ただデータの一貫性を保ちたい場合は極力データ型は合わせた方が望ましいです。

サンプルテーブル

  • サンプルテーブル「CLIENT_ADDRESS」

COALESCE関数の使用例

  • SQL(クエリー)例

    上記例ではSTART_DATEがNULLであればEND_DATEの値を、END_DATEもNULLであればsysdateを返却します。

  • 実行結果

Oracle:NVL関数とNVL2関数の違い

oracleの独自関数としてNVL関数やNVL2関数があります。

知ってると結構便利な関数なので、この2つの関数の違いについて整理しておきます。

NVL関数とは

NVL関数は第1引数がNULLなら第2引数の値(代替値)を返します。

もし第1引数の結果がNULLでなければ、そのまま第1引数の値を返します。

注意点として第1引数と第2引数へは同じデータ型を指定する必要があります。

NVL関数の使用例

  • サンプルテーブル「CLIENT_ADDRESS」
  • SQL(クエリー)例
    以下の例ではEND_DATEがNULLでない場合はEND_DATEの値を、NULLの場合はsysdateを返却します。
  • 実行結果

NVL2関数とは

NVL2関数は第1引数がNULLの場合に、第2引数の値を返却し、第1引数がNULLの場合は第3引数の値を返却します。

注意点として第1引数、第2引数、第3引数に指定する値は全て同じデータ型を指定する必要があります。

NVL2関数の使用例

  • サンプルテーブル「CLIENT_ADDRESS」
  • SQL(クエリー)例
    以下の例ではEND_DATEがNULLでない場合は第2引数のSTART_DATEの値を返却し、END_DATEがNULLの場合は第3引数のsysdateを返却します。
  • 実行結果

INSERT文を指定回数分ループして実行する方法

性能試験などであるテーブルに大量データの作成が必要になった場合にINSERT文をループで処理できれば便利!という事で、SQLとロジックを組み合わせたストアドプロシージャでのサンプルプログラムとなります。

ストアドプロシージャ

上記の例では、loop_limitに指定した件数分「GOODS」テーブルへレコードを追加するサンプルコードです。

上記の例では、loop_limitを10としていますが、必要に応じて任意の数値に変更できます。

DBeaverで上記のストアドプロシージャを実行する場合は「SQLスクリプトを実行する」を選択することで正常に実行されます。

実行結果

 

Oracle:事前構成済の表領域

Oracleではデータベースを作成すると自動的に以下の表領域が作成されます。

事前構成済の表領域

 

表領域説明
SYSTEMOracleサーバーがデータベースを管理するために使用する表領域
SYSAUXSYSTEM表領域の補助表領域
TEMPデータベースのデフォルトの一時表領域
UNDOTBS1UNDO表領域
USERS永続表領域
SYSやSYSTEMユーザー以外のユーザー用のデフォルト表領域
EXAMPLEデータベース作成時に「サンプル・スキーマの作成」を指定すると作成される表領域
 

Oracle:データベースの起動段階と状態について

Oracleデータベースを起動すると内部的に以下の段階でデータベースが使用可能な状態となります。

データベースの起動段階と状態

 

段階ステータス状態
1SHUTDOWNデータベースが停止している状態
2NOMOUNTインスタンスが起動している状態
※制御ファイル、データファイル、REDOログファイルはクローズ
3MOUNTインスタンスが起動し、制御ファイルがオープンしている状態
※データファイル、REDOログファイルはクローズ
4OPENインスタンスが起動し、制御ファイル、データファイル、REDOログ・ファイルもオープンしている状態
※一般ユーザーが使用可能な状態
 

Oracle:制約の種類

Oracleデータベースでは以下の制約を設定することが出来ます。

Oracle DB:制約の種類

制約説明
NOT NULL制約NULL値の設定を許可しない
UNIQE制約
(一意キー制約)
一意の値のみ許可します。複数レコード登録時、重複する値を許可しません。NULL値は許可します。
CHECK制約指定した条件の値のみ許可します。
PRIMARY KEY制約
(主キー制約)
一意の値のみ許可し、重複する値は許可しません。NULL値も許可しません。
FOREIGN KEY制約
(外部キー制約)
指定した親テーブルに存在する値のみ許可します。
 

制約のポイント

  • 制約はCREATE TABLE または ALTER TABLEで設定出来ます。
  • 制約設定の構文には列制約構文と表制約構文の2種類あります。
  • NOT NULL制約は列制約構文でのみ設定出来ます。

制約の設定例

  • 下記例のNOT NULL制約の設定箇所が列制約構文、UNIQUE制約の設定箇所が表制約構文となります。

 

Oracle:プロセス構造の仕組み

Oracle DBのプロセス構造は大きく「ユーザープロセス」、「サーバープロセス」、「バックグラウンドプロセス」があります。

ユーザープロセス

  • クライアントからSQLを発行すると発生するプロセスです。

サーバープロセス

  • クライアントが発行したSQLを処理するするのがメインのプロセスです。

バックグラウンドプロセス

  • サーバープロセスがSQL管理に専念させるため、他のプロセスを監視するためのプロセスです。Oracleインスタンスを起動することで自動で起動します。
  • 主なバッググランドプロセスの種類
    種類説明
    DBWn
    (データベースライター)
    データベースバッファキャッシュ内の変更されたデータをデータファイルへ書き込む
    LGWR
    (ログライター)
    REDOログバッファにあるREDOログをREDOログファイルへ書き込む
    CKPT
    (チェックポイント)
    チェックポイント情報を制御ファイルとデータファイルヘッダーへ書き込む
    SMON
    (システムモニター)
    インスタンスのリカバリを実行する
    PMON
    (プロセスモニター)
    ユーザープロセス障害からリカバリを実施する
    ARCn
    (アーカイバー)
    REDOログファイルのコピー(アーカーブファイル)を作成する
    REDO
    (リカバラ)
    分散トランザクションに関する障害を自動的に解決する。
    MMON
    (マネージメントモニター)
    メモリー内の統計情報をスナップショットとして定期的にデーターベースへ格納する。
    MMAN
    (メモリーマネージャ)
    メモリーを必要な場所に動的に割り当てる

 

SQL:任意の順序でソート(並べ替え)する方法

SQLでは特定の項目の昇順、降順ではなく任意の順序で並べ替えて取得することも出来ます。

任意の順序でソートするにはORDER BY句でCASE文を指定する事で取得することが出来ます。

任意の順でソートする例

サンプルテーブル

「BIRTHDAY」テーブル

クエリー(SQL)

出力結果

SQL:xx日後、xxヶ月後、xx年後などの日付を取得する方法

SQLでsysdateなどの日付型へ加算、減算してxx日後、xxヶ月後、xx年後を求める方法をメモしておきます。

xx秒後、xx秒前を求める方法

クエリー(SQL)例

  • システム日付の30秒後と30秒前を取得する例です。

実行結果

 

xx分後、xx分前を求める方法

クエリー(SQL)例

  • システム日付の5分後と5分前を取得する例です。

実行結果

 

xx時間後、xx時間前を求める方法

クエリー(SQL)例

  • システム日付の2時間後と2時間前を取得する例です。

実行結果

 

xx日後、xx日前を求める方法

クエリー(SQL)例

  • システム日付の1日後と1日前を取得する例です。

実行結果

 

xxヶ月後、xxヶ月前を求める方法

クエリー(SQL)例

  • システム日付の1ヶ月後と1ヶ月前を取得する例です。ADD_MONTHS関数は1/31の1ヶ月後は2/28となるように上手く月末日を調整してくれます。

実行結果

 

xx年後、xx年前を求める方法

クエリー(SQL)例

  • システム日付の1年後と1年前を取得する例です。ADD_MONTHS関数に12の倍数を指定することで年単位での加減算が可能となります。

実行結果

Oracle:最大プロセス数や最大セッション数を変更する方法

Oracleでのセッションやプロセス数には上限があり、それを超えてしまうとデータベースにアクセス出来ずに予期せぬエラー(ORA-12519)が発生するなどの不具合が発生してしまいます。

Oracleで最大プロセス数や最大セッション数は初期化パラメータを管理しているSPFILEに定義されています。変更前にSPFILEのバックアップを取得しておくのをオススメします。

SPFILEの配置場所

Oracle DB 18cの場合の例です。

  • 配置位置:[ORACLE_HOME]/database/SPFILE[ORACLE_SID].ORA
    (例)C:\ORACLE\WINDOWS.X64_180000_db_home\database\SPFILEORCL.ORA

最大プロセス数、最大セッション数の変更方法

  1. 最初に「V$SYSTEM_PARAMETER」から現在の最大プロセス数と最大セッション数の設定値を確認します。「V$SYSTEM_PARAMETER」はインスタンスに現在有効になっている初期化パラメータ情報を示します。

  2. 次にALETER文でプロセス数の上限を変更します。
  3. SPFILEの変更はOracleを再起動しないと適用されないため、Oracle DBを再起動します。
    詳細は「Oracle Database(Oracleサーバ)の再起動(停止・起動)手順」参照
  4. 再度プロセス数とセッション数を確認すると上限が変更されているのが確認出来ます。セッション数は明示的に変更しなくてもプロセス数の変更に比例して上限が増えます。