連載
» 2008年03月24日 00時00分 公開

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

[石橋潤一,株式会社システムインテグレータ]
前のページへ 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アプリケーション』(ソフトバンクパブリッシング刊/共著)。


前のページへ 1|2|3       

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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