実践的なトランザクションの構成と実行
今回は、トランザクションを利用したより実践的なプログラムに挑戦します。前回は、トランザクションを構成するSQL文である「BEGIN TRANSACTION」「COMMIT TRANSACTION」「ROLLBACK TRANSACTION」を用いて、トランザクションの考え方を解説しました。今回は、Northwindデータベースに対して、「注文を登録する」という処理を、トランザクションを利用して実行します。
まず、今回の例題の解説を容易にするために、Northwindデータベースに定義されているテーブル間の関連を図で表示してみましょう。ここでは、SQL Server Enterprise Managerの「ダイアグラム作成機能」を利用します。
では早速、次のステップに従って例題を実行してみましょう。
今回登場するトランザクションのコマンド群は、SQL Server 2000を題材に解説しています
ダイアグラム作成機能を利用する
Enterprise Managerでロック状況を確認する
(1) Enterprise Managerを起動する。
(2) 左端に表示される「ツリー」で、以下を順に展開する。
1. Microsoft SQL Servers
2. SQL Server グループ
3. サーバー名
4. データベース
5. Northwind
6. ダイアグラム
(3) ダイアグラムアイコンを右クリックし、「新規データベースダイアグラム」を選択する。
(4) 「データベースダイアグラム作成ウィザード」が起動するので、「次へ」ボタンをクリックする
(5) 「追加するテーブルの選択」画面が表示される。ここで、左側の「使用できるテーブル」リストボックスより、「Orders」テーブルを選択する
(6) 「関連するテーブルを自動的に追加」チェックボックスをクリックし、関連するテーブルのレベルを「10」にセットする
(7) 「追加」ボタンをクリックする。
(8) 「ダイアグラムに追加するテーブル」にテーブルがリストされたのを確認し、「次へ」ボタンをクリックする。
(9) 「データベースダイアグラム作成ウィザードの完了」画面が表示されるので、「完了」ボタンをクリックする。
(10) 自動的に関連図が作成され、表示される。テーブルの位置を見やすく調整すると、次の図のようになる。
(11) 左上のディスクアイコンをクリックし、名前を付けて保存する。
ここで作成されたダイアグラムによって、テーブル間のリレーションを確認することができます。それぞれのボックスがテーブル、そしてテーブル間に引かれた線がリレーションを表しています。テーブルやリレーションを選択して右クリックするとコンテキストメニューが表示されますので、「プロパティ」を選択するとテーブルの定義やリレーションの定義（外部キーの定義）を参照することが可能です。
では、このダイアグラムを参照しながら、トランザクションの例題を実行していきましょう。
注文の登録
今回は、注文の登録を実施していきます。次のような注文を受注したとします。
*** 一部省略されたコンテンツがあります。PC版でご覧ください。 ***
これは、顧客Alfreds Futterkiste社から従業員のAnne Dodsworthが11月11日に受注した受注伝票を示しています。受注した商品は2種15点で、11月17日までに納品する必要があります。この伝票を、データベースに登録してみましょう。
例題1は、トランザクションを利用して受注伝票を登録するSQL文です。まずはクエリアナライザから実行してみましょう。
【例題2】
1 DECLARE @OrderID INT 2 DECLARE @ERROR INT 3 4 WHILE (1>0) 5 BEGIN 6 BEGIN TRANSACTION 7 8 INSERT INTO Orders ( 9 CustomerID, 10 EmployeeID , 11 OrderDate, 12 RequiredDate, 13 ShipVia, 14 Freight, 15 ShipName, 16 ShipAddress, 17 ShipCity, 18 ShipRegion, 19 ShipPostalCode, 20 ShipCountry 21 ) VALUES ( 22 'ALFKI', 23 9, 24 '2002/11/11', 25 '2002/11/17', 26 3, 27 100, 28 'Mr. Yamada', 29 '1-1-1 Yurakucho', 30 'Chuo-ku', 31 'Tokyo', 32 '111-0001', 33 'Japan' ) 34 35 SET @ERROR = @@ERROR 36 SET @OrderID = @@IDENTITY 37 38 IF @ERROR > 0 39 BEGIN 40 ROLLBACK TRANSACTION 41 PRINT 'TRANSACTION ROLLBACKED' 42 BREAK 43 END 44 45 INSERT INTO "Order Details" ( 46 OrderID, 47 ProductID, 48 UnitPrice, 49 Quantity, 50 Discount 51 ) VALUES ( 52 @OrderID, 53 10, 54 31, 55 10, 56 0.02 ) 57 58 SET @ERROR = @@ERROR 59 60 IF @ERROR > 0 61 BEGIN 62 ROLLBACK TRANSACTION 63 PRINT 'TRANSACTION ROLLBACKED for OrderID=' + CONVERT(VARCHAR(10), @OrderID) 64 BREAK 65 END 66 67 INSERT INTO "Order Details" ( 68 OrderID, 69 ProductID, 70 UnitPrice, 71 Quantity, 72 Discount 73 ) VALUES ( 74 @OrderID, 75 13, 76 6, 77 5, 78 0 ) 79 80 SET @ERROR = @@ERROR 81 82 IF @ERROR > 0 83 BEGIN 84 ROLLBACK TRANSACTION 85 PRINT 'TRANSACTION ROLLBACKED for OrderID=' + CONVERT(VARCHAR(10), @OrderID) 86 BREAK 87 END 88 89 COMMIT TRANSACTION 90 PRINT 'TRANSACTION COMMITED for OrderID=' + CONVERT(VARCHAR(10), @OrderID) 91 BREAK 92 93 END
実行結果のメッセージに「TRANSACTION COMMITED for OrderID=xxxxx」と表示されれば、SQL文の実行は成功です。OrderIDは自動採番をしているため、皆さんの実行環境と数字は違うと思いますので、以下の例題2でも読み替えてくださいね。では、実際にオーダーが登録されたか、OrdersテーブルとOrder Detailsテーブルを参照してみましょう。
【例題3】
1 SELECT * FROM Orders WHERE OrderID = 18947 2 SELECT * FROM "Order Details" WHERE OrderID = 18947
Ordersテーブルにデータが1件、Order Detailsテーブルにデータが2件登録されているのが確認できたでしょうか。では、SQL文の解説に移る前に、もう1つ確認のステップを実行してみましょう。先ほどの例題1のSQLの75行目の「13」を「130」に変更してもう1度実行してみます。ProductIDが130の商品は登録されていないため、エラーが発生するはずです。
エラーが発生して、「TRANSACTION ROLLBACKED」と表示されたはずです。例題2と同様に、テーブルにデータが登録されているか否かを確認してみましょう。
OrdersテーブルにもOrder Detailsテーブルにもデータが登録されていないのが確認できたでしょうか。では、SQL文の解説に移りましょう。
テーブル間の関連の確認
トランザクション処理の解説に移る前に、まず、今回の例題で関連するテーブルについて確認しておきましょう。データの登録において直接関連するテーブルは、OrdersとOrder Detailsテーブルです。この2つのテーブルにデータを登録することで、受注を登録できます。
OrdersテーブルとOrder Detailsテーブルにデータを登録するためには、幾つかの関連するテーブルとのリレーションを設定する必要があります。リレーションを設定するためにはどうすればよいかといえば、外部キーとして設定されているカラムに、参照先のテーブルに存在する値を設定して登録すればいいですね。
例題1で作成したダイアグラムを利用して外部キーを確認してみましょう。Ordersテーブルを右クリックして、プロパティを選択し、リレーションシップタブを表示します。
このプロパティ画面の「選択したリレーションシップ」ドロップダウンリストを表示させると、3つの∞記号が付いた項目が表示されると思います。それぞれ、FK_Orders_CustomerはCustomersテーブルを参照する外部キー、FK_Orders_EmployeeはEmployeesテーブルを参照する外部キー、FK_Orders_ShippersはShippersテーブルを参照する外部キーですね。この画面下方の「INSERTとUPDATEに対するリレーションシップを適用する」チェックボックスが選択されている場合は、外部キーが参照先のテーブルに存在しない場合はエラーが発生し、データの登録や更新ができません。
例えば、Ordersテーブルにデータを挿入しようとした場合に、CustomerID列に指定した値がCustomersテーブルに存在しなかった場合は、データが登録されずにエラーになります。
このようにOrdersテーブルの設定を確認した結果、Ordersテーブルにデータを登録する際は、Customersテーブル、Employeesテーブル、Shippersテーブルに登録されているデータを指定してリレーションを設定する必要があることが分かります。
同様にしてOrder Detailsテーブルを確認すると、Ordersテーブル、Productsテーブルとのリレーションを設定する必要があることが分かると思います。
SQL文の解説
では、例題2のSQL文に移りましょう。
まず、大きな構造としては、1〜2行目がローカル変数の宣言、そして、4〜93行目で大きなWHILE文を構成しています。このWHILE文は繰り返し実行するために利用しているわけではありません。Transact-SQLではエラーハンドラを用いた記述ができないため、WHILE文を利用することで構成的に若干見やすさをよくしています。
このため、4行目のWHILE文の繰り返し条件は、「1>0」としてあり、BREAK文により強制的にWHILEループを抜けない限り、永遠に繰り返して実行することになってしまいます。このような用途でWHILE文を使用するときは、無限ループに陥らないように注意が必要です。
6行目で、トランザクションを開始しています。8〜33行目が、Ordersテーブルに対するINSERT文です。実際のプログラムでは、OrdersテーブルにINSERTする前に、CustomerID、EmployeeID、ShipperIDそれぞれの名称をキーにしてSELECT文で検索する必要があるかもしれませんが、ここでは省略して直接データを記述しています。
35行目では、@@ERRORシステム変数を@ERRORローカル変数に保存しています。@@ERRORシステム変数は、直前に実行したSQL文のエラー番号を保持しています。正常に実行が完了したときには、0がセットされます。何らかのSQL文を実行してしまうと途端にクリアされてしまうため、INSERT文の実行直後にSET文で@ERRORローカル変数に保存をしています。
36行目は、@@IDENTITYシステム変数を@OrderIDローカル変数に保存しています。@@IDENTITYシステム変数は、自動採番するためにIDENTITY指定のカラムに対して、実際に割り当てられた値の最新のものを保持しています。Ordersテーブルは、OrderID列が自動採番のためにIDENTITY指定されているため、Ordersテーブルに対してデータをINSERTした直後は、自動採番されたOrderIDが保存されます。このOrderIDは、この後Order Detailsを登録する際に必要となるため、@OrderIDローカル変数に保存しています。
38行〜43行目で、最初のINSERT文のエラー処理をしています。@ERRORが0以外であった場合をエラーとして判定しています。エラーが発生した場合は、40行目でトランザクションをロールバックし、41行目でメッセージを表示しています。42行目でその後のSQL文を実行しないようにするために、BREAK文でWHILE文を抜けています。
45〜65行目は、注文明細の1行目の登録です。基本的な構成は、Ordersテーブルの登録のときとまったく同じです。52行目でOrderID列への値の指定に、先ほど保存した@OrderIDローカル変数を利用しています。
67〜87行目は、注文明細の2行目の登録です。内容は、1行目とまったく同じです。
89〜91行目は、すべての処理が成功したときの終了処理です。89行目でトランザクションをコミットし、90行目でメッセージを表示し、91行目ではBREAK文でWHILE文を抜けています。これで、すべての処理が完了します。
エラーの場合の実行結果
画面7は、ProductIDを「130」とProductsテーブルに登録されていない番号へ変更したため、Order Detailsテーブルへデータを登録しようとした際に参照整合性制約エラーが発生します。このため、82行目で@ERRORが0以外となり、84行目でトランザクションがロールバックされます。
ここでロールバックされるのは、BEGIN TRANSACTION以降のすべての操作（INSERT、UPDATE、DELETE）となります。このため、これまでに実行されたOrdersテーブルへのデータ登録、Order Detailsテーブルへの1件目のデータ登録もキャンセルされ、完全に実行前の状態に戻されます。
このように、トランザクションを用いることで、「受注明細の1件目のみが登録された不完全な受注データ」が登録されることなく、データの整合性を保証することが可能となります。
次回の予定
今回は、トランザクションの実践的な例について解説しました。次回は、今回予定していたロックの詳細を紹介する予定です
- SQL Serverで「デッドロック」を回避する
- トランザクションの一貫性を保証するロック
- トランザクションを用いて注文登録をする
- トランザクションでデータの不整合を防ぐ
- テーブルで複数の処理を実行させるトリガー
- ユーザー定義関数を作成するストアドファンクション
- ストアドプロシージャによる繰り返し処理
- 条件分岐のあるストアドプロシージャ
- ストアドプロシージャの作成
- システム・ストアドプロシージャを用いたロールの詳細設定
- ロールを利用したグループ単位での権限設定
- SQL Serverのオブジェクトに権限を設定する
- Enterprise Managerによるビューの作成
- 作成したSELECT文をDBに登録する「ビュー」
- データの更新と主キーの重要性
- テーブル中のデータ識別に必要な主キーを定義する
- データの登録を行うINSERT文
- CREATE文をさらに使いこなそう
- CREATE文でテーブルを作成する
- SELECT文を統合する「UNION」
- サブクエリーの応用「相関サブクエリー」
- SELECT文の結果を抽出条件に使う
- テーブル結合のバリエーションを増やす
- テーブル結合の仕組みを理解する
- 異なるテーブル同士を結合する「JOIN」句
- 集計を行う「GROUP BY」句
- SELECT文で並べ替えを行うには？
- SQLの基礎 「SELECT」文を覚えよう
Copyright © ITmedia, Inc. All Rights Reserved.