連載:Visual Studio 2005でいってみようDBプログラミング

第8回 Let's Master ストアド・プロシージャ!(前編)

山田 祥寛(http://www.wings.msn.to/
2006/09/30
Page1 Page2 Page3 Page4

 今回は、ASP.NETアプリケーションからストアド・プロシージャを実行する方法について紹介します。

 ストアド・プロシージャ(Stored Procedure)とは、その名のとおり、データベース・サーバ上に保存(Stored)された一連の手続き(Procedure)のことです。ストアド・プロシージャは、データベース上で解析/コンパイル済みの状態で用意されていますので、実行時のオーバーヘッドを軽減することができます。

 また、複雑なデータベース操作を実行する場合にも、データベース・サーバ内で処理を完結できるので、アプリケーション・サーバとデータベース・サーバとの通信量を軽減できるというメリットもあります。クライアントからテーブルを直接に操作させるのではなく、常にストアド・プロシージャからのみアクセスさせるようにすることで、予期せぬ操作を防ぐという、セキュリティ上の効果も考えられるでしょう。

 ストアド・プロシージャを記述するには、データベースで定められた専用のプログラミング言語を使用します。プログラミング言語は使用しているデータベース・サーバによってそれぞれ異なりますが、OracleではPL/SQLという言語仕様を利用するのが一般的ですし、SQL ServerではTransact-SQLを使用します。Transact-SQLは、ANSI/ISOで定められたSQL-92標準の機能を、SQL Server向けに拡張したものです。

今回作成するサンプル・プログラムのダウンロード(vs2005db_08.zip)

ストアド・プロシージャ基本のキ

 それでは最初に、ストアド・プロシージャを使用する基本的な方法を見ていくことにしましょう。ここでは、以下のようにプルダウン・メニューで選択された条件に合致する書籍情報のみを表示するサンプル・アプリケーションを作成します。


図1 サンプル・アプリケーションの実行画面
プルダウン・メニューで指定された出版社に属する書籍のみを表示する。

 なお、本サンプルを動作させるには、あらかじめデータベース上に以下のようなBook/Publishテーブルを作成し、適当なデータを入力しておく必要があります。

列名 データ型 概要
isbn VARCHAR(30) ISBNコード(主キー)
title VARCHAR(100) 書名
price INT 価格
publish_id INT 出版社ID
publishDate DATETIME 配本日
表1 Bookテーブルのフィールド・レイアウト

列名 データ型 概要
publish_id INT 出版社ID(主キー)
name VARCHAR(50) 出版社名
address VARCHAR(100) 出版社所在地
表2 Publishテーブルのフィールド・レイアウト

[1]ストアド・プロシージャを定義する

 ストアド・プロシージャを作成するには、サーバ・エクスプローラから[データ接続]−[MyDB.mdf]−[ストアドプロシージャ]を右クリックし、表示されたコンテキスト・メニューから[新しいストアドプロシージャの追加]を選択します。


図2 追加されたストアド・プロシージャ
サーバ・エクスプローラから[データ接続]−[MyDB.mdf]−[ストアドプロシージャ]を右クリックし、コンテキスト・メニューから[新しいストアドプロシージャの追加]を選択すると、ストアド・プロシージャの骨格が作成される。

 コード・エディタに、上の画面のようにストアド・プロシージャの骨格が作成されますので、これをリスト1のように修正します。

CREATE PROCEDURE SelectProc
  @publish_id INT
AS
  SELECT * FROM
    Book AS b
  INNER JOIN
    Publish AS p
  ON
    b.publish_id=p.publish_id
  WHERE
    b.publish_id=@publish_id
リスト1 ストアド・プロシージャの定義

 ストアド・プロシージャを定義するのは、CREATE PROCEDURE命令の役割です*1。CREATE PROCEDURE命令の一般的な構文は以下のとおりです。

CREATE PROCEDURE プロシージャ名
  [@パラメータ名 データ型 [=デフォルト値]
  [,...]]
AS
  任意のTransact-SQL命令
CREATE PROCEDURE命令の構文

*1 既存のストアド・プロシージャに変更を加える場合には、CREATE PROCEDURE命令の代わりに、ALTER PROCEDURE命令を使用します。PROCEDUREキーワードは「PROC」と省略することも可能です。

 つまり上記のリスト1では、「Book/Publishテーブルを内部結合し、入力パラメータ@publishをキーにデータ抽出する」ストアド・プロシージャを「SelectProc」という名前で定義していることになります。この例ではAS句の配下にSELECT命令を1つ記述しているだけですが、(もちろん)複数のSQL命令を記述することも可能です。

 保存すると、サーバ・エクスプローラの[ストアドプロシージャ]配下にSelectProcストアド・プロシージャが追加されたことが確認できるはずです。SelectProcストアド・プロシージャをテスト実行するには、「SelectProc」を右クリックし、表示されたコンテキスト・メニューから[実行]を選択します。

 図3のように[ストアドプロシージャの実行]ダイアログが表示されますので、ここではパラメータ「@publish_id」にあらかじめPublishテーブルに登録された値(例えば「1」)を指定し、[OK]ボタンをクリックします。


図3 [ストアドプロシージャの実行]ダイアログ
サーバ・エクスプローラの[ストアドプロシージャ]配下に作成された「SelectProc」を右クリックし、コンテキスト・メニューから[実行]を選択すると表示される。パラメータ「@publish_id」に値を指定し[OK]ボタンをクリックすれば実行することができる。

 以下のように、[出力]ウィンドウに、条件に合致した値が表示されれば成功です。


図4 [出力]ウィンドウ(ストアド・プロシージャの実行結果)
ここではpublish_id列の値が「1」のデータが表示されている。

[コラム]SQLブロックのデザイン

 ストアド・プロシージャを定義する際に、SQLブロックを手書きしても(もちろん)構いませんが、クエリビルダを利用することでGUIベースでSQL命令を編集できますので、より便利です。

 クエリビルダを起動するには、コード・エディタ上の編集したいSQLブロックを右クリックし、コンテキスト・メニューから[SQLブロックのデザイン]を選択してください。コード・エディタ上では、編集可能なSQLブロックの単位は、青枠で示されています。なお、クエリビルダに関する詳細は、第5回でも紹介していますので、併せて参照することをお勧めします。



SQLブロック(青枠部分)を右クリックし[SQLブロックのデザイン]を実行


図5 クエリビルダによるSQL命令の編集
ストアド・プロシージャ内のSQLブロックは、クエリビルダを利用することでGUIベースで編集できる。

 ちなみに、コード・エディタではストアド・プロシージャの一部分のみを実行することも可能です。実行したい部分を選択状態にしたうえで右クリック、表示されたコンテキスト・メニューから[選択範囲の実行]を指定してください。ストアド・プロシージャが意図した動作をしない場合、断片的なブロック単位で実行することで、問題を特定しやすくなります。


 INDEX
  Visual Studio 2005でいってみようDBプログラミング
  第8回 Let's Master ストアド・プロシージャ!(前編)
  1.ストアド・プロシージャ基本のキ(1)
    2.ストアド・プロシージャ基本のキ(2)
    3.ストアドに条件分岐を追加する/ストアドから出力パラメータを取得する
    4.ストアド・プロシージャから戻り値を取得する
 
インデックス・ページヘ  「Visual Studio 2005でいってみようDBプログラミング」


Insider.NET フォーラム 新着記事
  • 第2回 簡潔なコーディングのために (2017/7/26)
     ラムダ式で記述できるメンバの増加、throw式、out変数、タプルなど、C# 7には以前よりもコードを簡潔に記述できるような機能が導入されている
  • 第1回 Visual Studio Codeデバッグの基礎知識 (2017/7/21)
     Node.jsプログラムをデバッグしながら、Visual Studio Codeに統合されているデバッグ機能の基本の「キ」をマスターしよう
  • 第1回 明瞭なコーディングのために (2017/7/19)
     C# 7で追加された新機能の中から、「数値リテラル構文の改善」と「ローカル関数」を紹介する。これらは分かりやすいコードを記述するのに使える
  • Presentation Translator (2017/7/18)
     Presentation TranslatorはPowerPoint用のアドイン。プレゼンテーション時の字幕の付加や、多言語での質疑応答、スライドの翻訳を行える
@ITメールマガジン 新着情報やスタッフのコラムがメールで届きます(無料)
- PR -

注目のテーマ

Insider.NET 記事ランキング

本日 月間
ソリューションFLASH