今回は、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ベースで編集できる。 |
ちなみに、コード・エディタではストアド・プロシージャの一部分のみを実行することも可能です。実行したい部分を選択状態にしたうえで右クリック、表示されたコンテキスト・メニューから［選択範囲の実行］を指定してください。ストアド・プロシージャが意図した動作をしない場合、断片的なブロック単位で実行することで、問題を特定しやすくなります。 |
Insider.NET 記事ランキング
本日
月間
