連載：Visual Studio 2005でいってみようDBプログラミング 第9回　Let's Master ストアド・プロシージャ！（後編） 山田 祥寛（http://www.wings.msn.to/） 2006/10/28

Page1 Page2 Page3 Page4

　前回は、アプリケーションからストアド・プロシージャを利用する基本的な方法について紹介しました。ストアド・プロシージャの基本的な記法から、入力／出力パラメータ、戻り値を受け渡しする基本的な操作をご理解いただけたことと思います。

　今回は引き続きストアド・プロシージャを使って、トランザクション処理を含む更新処理を行う方法や、SQL Server 2005の新機能SQL CLR統合によるTransact-SQLの置き換えなどについて解説を進めます。

今回作成するサンプル・プログラムのダウンロード（vs2005db_09.zip）

ストアド・プロシージャでトランザクションを利用する

　ここまでは、一度のデータベース操作で1つの処理を行う――比較的単純な処理を紹介してきたわけですが、一般的な業務アプリケーションではいくつかの変更処理をひとまとまりの処理として行いたいようなケースは多々発生します。

　データベースの世界においては、このような論理的な処理の単位を「トランザクション」と呼びます。複数の処理をトランザクションとして定義することで、トランザクション内の一部の処理が失敗した場合にはそのほかの処理をキャンセルし、データの整合性を維持することができます。

　ここでは、このトランザクション機能をストアド・プロシージャ上で実装する方法について紹介します。使用するのは、前回のサンプル・アプリケーションでも利用したBookテーブルです。ここでは、Bookテーブルに対してわざと主キー制約違反が発生するようなレコードを追加し、そのときにトランザクションがロールバック（キャンセル）されることを確認してみます。

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

　Visual Studio 2005（以下、VS 2005）のサーバ・エクスプローラから［データ接続］−［MyDB.mdf］−［ストアドプロシージャ］を右クリックし、表示されたコンテキスト・メニューから［新しいストアドプロシージャの追加］を選択します。ストアド・プロシージャの骨格が自動生成されますので、これをリスト1のように修正してください。

ALTER PROCEDURE InsertProc AS   BEGIN TRANSACTION      -- 新規データ（処理は成功するはず）     INSERT Book(isbn,title) VALUES('4-7981-0981-9','独習PHP')      -- 主キーがすでにテーブル上に存在（処理は失敗するはず）     INSERT Book(isbn,title) VALUES('4-7981-1206-2','サーバサイドAjax入門')   COMMIT TRANSACTION   RETURN 0

リスト1　ストアド・プロシージャInsertProcの定義

MyDB.mdf内のBookテーブルには、isbn列が「4-7981-1206-2」のレコードが存在するものとする。

　トランザクションを開始するのは「BEGIN TRANSACTION命令」の役割です。これによってトランザクションは有効化され、以降行われた変更は「COMMIT TRANSACTION命令」が呼び出されるまでは確定しません。あるいは「ROLLBACK TRANSACTION命令」が呼び出された場合や、致命的なエラーが発生した場合には、トランザクションが開始されてからそこまでに行われた変更をすべて無効にします。1つのトランザクションは、COMMIT／ROLLBACK TRANSACTION命令が呼び出された時点で終了します。

　ということで、まずは上のストアド・プロシージャInsertProcをサーバ・エクスプローラから実行してみましょう。

図1　ストアド・プロシージャInsertProcの実行結果

　出力ウィンドウに図1のような結果が表示され、確かに主キー制約違反によるエラーが発生していることが確認できます。

　ところがBookテーブルを確認するとどうでしょう。図2のように1件目のINSERT命令で追加されたレコードがコミットされてしまっていることが確認できます。

図2　ストアド・プロシージャInsertProcを実行した後のBookテーブル

　つまり、ここで押さえておく必要があるのは「主キー制約違反によるエラーが発生しても、SQL Serverは（デフォルトでは）自動的にロールバックはしない」という点です。

　制約違反などのステートメント・エラーが発生した場合、SQL Serverは「エラー原因となったステートメントのみをロールバック」します。制約違反エラーによってトランザクション全体をロールバックしたい場合には、アプリケーション開発者が明示的にエラー処理を記述する必要があるというわけです。

■TRY〜CATCH命令によるエラー処理

　そこでリスト1に明示的なエラー処理を加えたのが、次のリスト2です（追記部分は太字）。

ALTER PROCEDURE InsertProc AS    -- 例外が発生する可能性があるコード   BEGIN TRY      -- トランザクションの開始     BEGIN TRANSACTION        -- 新規データ（処理は成功するはず）       INSERT Book(isbn,title) VALUES('4-7981-0981-9','独習PHP')        -- 主キーがすでにテーブル上に存在（処理は失敗するはず）       INSERT Book(isbn,title) VALUES('4-7981-1206-2','サーバサイドAjax入門')      -- トランザクションをコミット（確定）     COMMIT TRANSACTION   END TRY    -- 例外処理   BEGIN CATCH     -- トランザクションをロールバック（キャンセル）     ROLLBACK TRANSACTION     RETURN ERROR_NUMBER()   END CATCH   RETURN 0

リスト2　ストアド・プロシージャInsertProcの定義（例外処理に対応）

　先ほど追加されてしまった1件目のデータを削除したうえで、もう一度、修正したストアド・プロシージャInsertProcを実行してみましょう。今度は正しく処理がロールバックされ、Bookテーブルにはデータが追加されて「いない」ことが確認できるはずです。

　ストアド・プロシージャ上で例外（エラー）処理を行うのは、「TRY〜CATCH命令」の役割です。TRY〜CATCH命令の一般的な構文は、以下のとおりです。

BEGIN TRY   エラーが発生する可能性があるコード END TRY BEGIN CATCH   エラー発生時に実行するコード END CATCH

TRY〜CATCH命令の構文

　TRY〜CATCH命令は、BEGIN TRY〜END TRYブロックでエラーが発生した場合に本来の処理を中断し、後続の処理をBEGIN CATCH〜END CATCHブロックに委ねます。

　つまり、リスト2の例でいうならば、2番目のINSERT命令でエラーが発生したタイミングで、処理はBEGIN CATCH〜END CATCHブロックに移り、トランザクションをロールバックするとともに、エラー番号を戻り値として返しているわけです。

　ERROR_NUMBER関数は、BEGIN TRY〜END TRYブロックで発生したエラーのエラー・コードを返します。エラー発生時には、この関数のほかにもERROR_LINE（エラー発生行）、ERROR_STATE（エラーの状態）、ERROR_MESSAGE（エラー・メッセージ）などの関数を利用することが可能です。

INDEX
	Visual Studio 2005でいってみようDBプログラミング
	第9回　Let's Master ストアド・プロシージャ！（後編）
	1．ストアド・プロシージャでトランザクションを利用
	2．SQL CLR機能によるストアド・プロシージャ
	3．新規SQL CLRストアド・プロシージャの追加
	4．アクセス許可レベルを設定／アセンブリの配置

　

「Visual Studio 2005でいってみようDBプログラミング」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）