トランザクション処理をさらっとマスターしよう：さらっと覚えるSQL＆T-SQL入門（12）（3/3 ページ）

» 2008年03月24日 00時00分 公開
[石橋潤一，株式会社システムインテグレータ]
前のページへ 1|2|3 　　　　　　

トランザクションを組み込んだストアドプロシージャの作成

　ストアドプロシージャの作成にあたって利用するデータベースはいつもと同じ、SQL Server 2005のサンプルデータベースであるAdventureWorksを利用します。

　今回作成するストアドプロシージャは、「Employee」および「Contact」テーブルから氏名、役職を取り出し、データを新しいテーブルに挿入していく、という内容です。

テーブルの作成

　ストアドプロシージャ作成の前に、次の内容のテーブルをAdventureWorksに加えてください。

テーブル名：TranTest
列名 定義 備考
EmployeeID int 主キー
FirstName nvarchar(50)  
LastName nvarchar(50)  
Title nvarchar(50)  

ストアドプロシージャの作成、実行

　続いて、ストアドプロシージャの作成に入りましょう。

　作成するストアドプロシージャは、前回の記事で作成したサンプルを拡張した内容になっているため、前回のサンプルが残っている方は、その内容を拡張しても良いでしょう。

ALTER procedure sp_TranSample as

--変数リストの宣言
DECLARE @EmployeeID int
DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)
DECLARE @Title nvarchar(50)

--カーソルの宣言
DECLARE EmpCur cursor FOR
SELECT emp.EmployeeID,cnt.FirstName,cnt.LastName,emp.Title
 FROM HumanResources.Employee as emp INNER JOIN Person.Contact AS cnt
 ON emp.ContactID = cnt.ContactID

--カーソルを開く
OPEN EmpCur

--FETCH（行の取り出し）
FETCH NEXT 
 FROM EmpCur INTO @EmployeeID,
                  @FirstName,
                  @LastName,
                  @Title

BEGIN TRY
    BEGIN TRANSACTION        --トランザクションの開始
        --ループ
        WHILE (@@fetch_status = 0)
        BEGIN
            --変数リストからINSERT文の実行
            INSERT INTO TranTest
                 VALUES
                       (@EmployeeID
                       ,@FirstName
                       ,@LastName
                       ,@Title)
            --FETCH（行の取り出し）
            FETCH NEXT
             FROM EmpCur INTO @EmployeeID,
                              @FirstName,
                              @LastName,
                              @Title
        end
    COMMIT TRANSACTION       --トランザクションを確定
END TRY

--例外処理
BEGIN CATCH
    ROLLBACK TRANSACTION     --トランザクションを取り消し
    PRINT ERROR_MESSAGE()    --エラー内容を戻す
    PRINT 'ROLLBACK TRANSACTION'
END CATCH

--カーソルを閉じる
CLOSE EmpCur
DEALLOCATE EmpCur

RETURN

　作成したストアドプロシージャを実行すると、TranTestテーブルに対してデータの追加が行われます。

ロールバック処理の検証

　ここでは、実際にロールバックが正しく動作しているか確認するために、TranTestに対して手入力で次のデータを登録してから、プロシージャを実行してみましょう。手入力したデータによって、ストアドプロシージャ実行時にPRIMARY KEY違反が発生するはずです。

列名 値
EmployeeID 77
FirstName atmark
LastName IT
Title WebMaster

　上記データがTranTestに存在する状態でストアドプロシージャを実行すると、次のような結果となります。

(1 行処理されました)

〜〜（略）〜〜

(0 行処理されました)
制約 'PK_TranTest' の PRIMARY KEY 違反。オブジェクト 'dbo.TranTest' には
重複したキーを挿入できません。
ROLLBACK TRANSACTION

　PRIMARY KEY違反が発生し、ロールバックが行われています。実際にTranTestを確認してみると、1行もデータが追加されていないことが確認できます。

　TranTestからデータを消去したうえで再度ストアドプロシージャを実行すると、処理が正常に終了し、INSERT文によるデータ追加がすべてコミットされます。

まとめ

　以上のように、トランザクションを利用することによって、一連の処理を1つのまとまりとして扱い、データの整合性を保ちながらデータの更新処理を行うことができます。複数のDML文を発行するような場合は必須と言える技術であるため、その特性をしっかりと把握しておきましょう。

　とうとうこの連載も、これで最後のまとめになります。1年前から始まった本連載ですが、読者のみなさんの学習の助けになったでしょうか？

　SQLはとても奥が深く、それでいてとても使いやすい言語です。難しい概念や難解な構文もたくさんありますが、実際に手で書き、目で動きを追ってみると、理解できないものではありません。本連載をきっかけとして、みなさんがSQLを極める最初の一歩となれば幸いです。

　1年間ご愛読ありがとうございました！ それでは、また新しい記事でお会いしましょう！（連載完）

筆者プロフィール

石橋潤一
株式会社システムインテグレータ勤務。Web＋DBの業務アプリをメインに開発に携わる。＠IT連載記事「SQL Server 2005を使いこなそう」「SQL Server 2005 CTPレビュー」執筆のほか、著書に『DBマガジン別冊　SQL Server 2005徹底活用ガイド』（翔泳社刊／共著）、『ASP.NET+SQL Server　ゼロからはじめるWebアプリケーション』（ソフトバンクパブリッシング刊／共著）。


「さらっと覚えるSQL＆T-SQL入門」バックナンバー
前のページへ 1|2|3 　　　　　　

Copyright © ITmedia, Inc. All Rights Reserved.

＠IT

@ITについて

RSSについて

アイティメディアIDについて

メールマガジン登録

＠ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。