「SQL」タグアーカイブ

SQL:単語の先頭1文字目のみ大文字へ変更する方法

SQLで単語の先頭1文字目のみ小文字から大文字へ変更したい場合は、INITCAP関数を使用することで可能となります。引数に変換したい文字を指定すればOKです。単語の区切りはスペースかアルファベット以外の文字(ハイフン「-」、カンマ「,」、アンダースコア「-」など)を区切り文字として見做して変換されます。

DBMS毎の使用可否

  • 一般的な以下のDBMSではINITCAP関数は使用可能です。

サンプルテーブル

  • 「GOODS」テーブル

INITCAP関数の使用例

SQL(クエリー)

実行結果

  • 以下のように区切り文字の単語別に先頭文字が大文字に変換されているのが確認出来ます。

 

SQL:指定した日付(該当月)の月末日を取得する方法

SQLで指定した日付項目(該当年月)の月末日を取得するには「LAST_DAY」関数を使用します。うるう年でも正確な月の最終日を取得出来ます。

DBMS毎の使用可否

  • SQL Serverでは「EOMONTH」関数で最終日付を取得出来ます。
    関数\DBMSMySQLPostgreSQLSQL ServerOracle
    LAST_DAY
    EOMONTH

日付(該当月)の最終日を取得する例

サンプルテーブル「BIRTHDAY」

SQL(クエリー)

  • BIRTHDAYテーブルの項目「BIRTHDAY」の最終日を取得する例となります。

実行結果

SQL:特定の日付時点で有効な住所情報を取得する方法

顧客の有効住所などを管理しているテーブルからある特定の日付時点で有効な住所を取得する方法をメモしておきます。

特定の日付時点で有効な住所情報を取得するサンプル

サンプルテーブル

  • 「CLIENT_ADDRESS」テーブル
    以下の様に顧客ID(CLIENT_ID)毎に有効な住所を管理するテーブルです。最新の住所のEND_DATEはnullとして管理してます。

クエリー(SQL)

  • ‘2022-03-31’時点で有効な住所情報を取得する例です。

実行結果

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. 再度プロセス数とセッション数を確認すると上限が変更されているのが確認出来ます。セッション数は明示的に変更しなくてもプロセス数の変更に比例して上限が増えます。

DBeaver:テーブルに登録済のレコードをinsert文形式に変換して取得する方法

データベースのクライアントソフトのDBeaverには便利な機能がいくつもありますが、その内の機能の一つにテーブルに登録済のレコードをinsert文形式に変換して取得出来る機能があります。

開発などでIT環境などに登録済のデータをlocal環境へデータコピーしたい場合などに使える便利な機能の一つです。SQL形式(insert文)以外にもCSVやHTML、XML形式などにも変換出来るので様々な用途に応用可能となります。

DBeaverで登録済のレコードをinsert文形式に変換してい取得する方法

  1. コピーしたいテーブルのデータを表示してinsert文へ変換したいレコードを選択します。
  2. 右クリックして「高度なコピー」⇒「Copy as SQL」を選択するとinsert文形式でのコピーは完了です。
  3. 以下の様にコピーした内容を貼り付けてみるとinsert文形式へ変換されているのが確認出来ます。

 

SQL:NULLの判定方法

SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。

使用例

サンプルテーブル

「GOODS」

NULLのデータを抽出する例

クエリー(SQL)

実行結果

NULL以外のデータを抽出する例

クエリー(SQL)

実行結果

SQL:SELECT時にCASE文で条件指定する方法

SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。

使用例

サンプルテーブル

「GOODS」

クエリー(SQL)

  • 以下の例ではCASE文でGOODS_CODEが特定の値と一致した場合、NAME列に文字列を付与してTEST列として出力しています。

出力結果

Oracle:年、月、日、時、分、秒以下を切り捨てて取得する方法

OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。

使用例

サンプルテーブル「BIRTHDAY」

クエリー(SQL)

  • BIRTHDAYテーブルのUPDATE_DATEに対してTRUNC関数を使用した例となります。第2引数へformatを指定することで指定した単位で切り捨てた値を取得することが出来ます。
  • formatの種類 
    format説明
    YYYY年まで取得して月日時分秒を切り捨て
    MM年月まで取得して日時分秒を切り捨て
    DD年月日まで取得して時分秒を切り捨て
    HH年月日時まで取得して分秒を切り捨て
    MI年月日時分まで取得して秒を切り捨て
     

出力結果