Server複数のスキーマを用意している環境で、あるスキーマのテーブルを別のスキーマから参照するビューを作成して使用することがあります。今回は、別のスキーマのテーブルをテーブルのように参照するビューを自動生成するストアドプロシージャを作成します。ストアドプロシージャが使用するテーブルの準備SQLServerのスキーマとは別スキーマのテーブルを参照するビュー(VIEW)を自動作成するストアドプロシージャを作成する前に、スキーマについて簡単に記述しておきます。SQLServerにはスキーマ(Schema)というオブジェクトが用意されています。スキーマとは、テーブルなどのデータベース内のオブジェクトをグループ化するコンテナの名前(名前空間)です。スキーマには権限などが設定できるので、特定のオブジェクトの権限を制御することができます。スキーマを使用することで、ユーザーとは別の権限管理が可能になります。スキーマはオブジェクトをグループ化する名前空間なので、スキーマが違えば同じ名前のオブジェクトを作成することができます。(1つのスキーマ内には同じ名前のオブジェクトを作成できませんが、スキーマが違えば作成できます。別スキーマのテーブルを参照するビューは、スキーマが違うテーブルのデータをテーブルのように参照するビューです。スキーマ名.テーブル名で別スキーマのテーブルのデータをすべて抽出するビューです。abcというスキーマとdefというスキーマがあるとします。abcスキーマには、TEST_TABLEという名前のテーブルを作成します。nvarchar(20));このテーブルを別のスキーマであるdefスキーマから参照するには、以下のようなビューを作成します。abc.TEST_TABLE;上記のような別スキーマのテーブルを参照するビューを作成する必要がある場合、テーブルの数が多くなればなるほど同じようなSQLをたくさん記述しなければなりません。そこで、今回はそれを自動的に作成するためのストアドプロシージャを作成します。参照するビューを自動作成するストアドプロシージャスキーマとテーブルの作成ストアドプロシージャを作成する前に、サンプルとして使用するスキーマとテーブルを作成しておきます。今回はスキーマに「a」「b」「c」の3つを用意します。c;GO作成するスキーマのうち、「a」を基準となるスキーマにします。他のスキーマ(bスキーマとcスキーマ)から参照するaスキーマのテーブルとして「TABLE1」「TABLE2」「TABLE3」を作成します。参照するビューを自動作成するストアドプロシージャ」では1「テーブルを参照するビューの基準となるテーブルを保持するためのテーブル」と2「ビューを作成する先のスキーマを保持するためのテーブル」を事前に作成しておいて、それを参照して処理を行います。1「テーブルを参照するビューの基準となるテーブルを保持するためのテーブル」には、スキーマ名とテーブル名の列を用意します。スキーマ名とテーブル名のサイズ(桁数)は、環境に合わせて変更してください。このテーブルに参照元となるテーブルの情報を登録します。REFERENCE_TABLEテーブルには、上記の3つのテーブルの情報を追加しておきます。'TABLE3');2「ビューを作成する先のスキーマを保持するためのテーブル」には、スキーマ名の列を用意します。nvarchar(50));このテーブルにビューを作成するスキーマを登録します。SCHEMA_TO_CREATE_VIEW(SCHEMA_NAME)VALUES('b'),('c');上記のREFERENCE_TABLEテーブルとSCHEMA_TO_CREATE_VIEWテーブルへのデータのインサートは、1回の実行で複数のレコードを挿入する形式のSQLで記述しています。1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法の詳細については、以下の記事を参照してください。1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)するSQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。...johobase.comストアドプロシージャから参照するテーブルの準備ができたら、テーブルを参照するビューを作成するストアドプロシージャのSQLを記述していきます。ストアドプロシージャでは、以下の処理を行います。プロシージャ内で使用する変数を定義します。変数には、「参照するテーブルのスキーマ名」「参照するテーブル名」「作成するビューのスキーマ名」を用意します。テーブルカーソルの変数を用意し、SELECT文を記述します。SELECT文ではREFERENCE_TABLEとビューを作成するスキーマを結合して、「参照するテーブルのスキーマ名」「参照するテーブル名」「作成するビューのスキーマ名」データを取得します。テーブルカーソルを開きます。フェッチでカーソルにレコードを読み込んでいきます。SELECT文で取得する結果のすべてのレコードをループで処理します。VIEW文)を生成します。VIEW)するSQLも生成します。生成したSQLを実行します。すべてのビューが作成できたらテーブルカーソルを閉じて破棄します。上記の処理を行うストアドプロシージャのSQLを記載します。プロシージャ名は「CREATE_TABLE_REFERENCE_VIEW」で作成します。ストアドプロシージャが存在する場合は一旦削除する。ストアドプロシージャを作成する。テーブルカーソルで仕様する変数を定義する。テーブルカーソルの定義とSQLの設定。テーブルカーソルを開く。作成するビューの名前。参照するテーブルの名前。念のためエラー処理を行っておく。エラーメッセージを出力する。以外のオブジェクトとして存在しています。作成されませんでした。テーブル参照ビューを一旦削除するSQL。テーブル参照ビューを作成するSQL。SQLを実行してビューを作成する。作成完了メッセージを出力する。テーブルカーソルを閉じる。テーブルカーソルを破棄する。ストアドプロシージャが存在する場合は一旦削除する。ストアドプロシージャを作成する。テーブルカーソルで仕様する変数を定義する。テーブルカーソルの定義とSQLの設定。テーブルカーソルを開く。作成するビューの名前。参照するテーブルの名前。念のためエラー処理を行っておく。エラーメッセージを出力する。以外のオブジェクトとして存在しています。作成されませんでした。テーブル参照ビューを一旦削除するSQL。テーブル参照ビューを作成するSQL。SQLを実行してビューを作成する。作成完了メッセージを出力する。テーブルカーソルを閉じる。テーブルカーソルを破棄する。TABLE_CURSOR;GO作成したストアドプロシージャは、以下のように実行します。CREATE_TABLE_REFERENCE_VIEW;ストアドプロシージャを実行すると、ビューが作成されることが確認できます。MySQL]テーブルにレコードとして保存されているデータを取得する際に横に展開して1行にしたい場合があります。STDDEV_POP)学校の成績関連のシステムの開発をしていると、時どき偏差値(へんさち)を求めることがあります。[SQLServer]前回の記事では、データベースのテーブルからデータを取得する際に使用するSELECTステートメントを紹介しました。SQ...記事を読むSQLServerでboolean型(True/Falseの真偽値)を扱うbit型SQLServerで真偽値を扱うためのデータ型について紹介します。Serverのデータベースを管理するための...記事を読むマスターテーブルのレコードごとの集計値を取得するSQLテーブルに登録されている金額などの数値データを、あるマスターテーブルのキー(コードやID)ごとに集計します。Server]SQLServerでSQLを記述していると、金額などの数値データを金額表記(12,345)にしたい時があります。