トランザクションを用いて注文登録をするSQL実践講座(26)

» 2002年11月26日 00時00分 公開
[篠原光太郎@IT]

実践的なトランザクションの構成と実行

 今回は、トランザクションを利用したより実践的なプログラムに挑戦します。前回は、トランザクションを構成する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) ダイアグラムアイコンを右クリックし、「新規データベースダイアグラム」を選択する。

画面1 まず作成ウィザードを起動させる(画面をクリックすると拡大表示します) 画面1 まず作成ウィザードを起動させる(画面をクリックすると拡大表示します)

(4)データベースダイアグラム作成ウィザード」が起動するので、「次へ」ボタンをクリックする

(5)追加するテーブルの選択」画面が表示される。ここで、左側の「使用できるテーブル」リストボックスより、「Orders」テーブルを選択する

(6)関連するテーブルを自動的に追加」チェックボックスをクリックし、関連するテーブルのレベルを「10」にセットする

画面2 追加テーブルの選択(画面をクリックすると拡大表示します) 画面2 追加テーブルの選択(画面をクリックすると拡大表示します)

(7)追加」ボタンをクリックする。

(8)ダイアグラムに追加するテーブル」にテーブルがリストされたのを確認し、「次へ」ボタンをクリックする。

(9)データベースダイアグラム作成ウィザードの完了」画面が表示されるので、「完了」ボタンをクリックする。

画面3 データベースダイアグラム作成完了(画面をクリックすると拡大表示します) 画面3 データベースダイアグラム作成完了(画面をクリックすると拡大表示します)

(10) 自動的に関連図が作成され、表示される。テーブルの位置を見やすく調整すると、次の図のようになる。

画面4 テーブル間関連図(画面をクリックすると拡大表示します) 画面4 テーブル間関連図(画面をクリックすると拡大表示します)

(11) 左上のディスクアイコンをクリックし、名前を付けて保存する。

 ここで作成されたダイアグラムによって、テーブル間のリレーションを確認することができます。それぞれのボックスがテーブル、そしてテーブル間に引かれた線がリレーションを表しています。テーブルやリレーションを選択して右クリックするとコンテキストメニューが表示されますので、「プロパティ」を選択するとテーブルの定義やリレーションの定義(外部キーの定義)を参照することが可能です。

 では、このダイアグラムを参照しながら、トランザクションの例題を実行していきましょう。

注文の登録

 今回は、注文の登録を実施していきます。次のような注文を受注したとします。

顧客ID ALFKI 受注日 2002/11/11
顧客名 Alfreds Futterkiste 担当者(ID) Anne Dodsworth(9)
配送先 Mr. Yamada 納品予定日 2002/11/17
配送先住所 1-1-1Yurakucho,Chuo-ku,
Tokyo, Japan 111-0001
運送業者(ID) Federal Shipping(3)
送料 100    

明細

No. 商品ID 製品名 単価 数量 値引き率
1 10 Ikura 31 10 2% 303.8
2 13 Konbu 6 5 0% 30

 これは、顧客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
画面5 クエリアナライザに受注伝票を登録する(画面をクリックすると拡大表示します) 画面5 クエリアナライザに受注伝票を登録する(画面をクリックすると拡大表示します)

 実行結果のメッセージに「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
画面6 オーダーの登録を確認する(画面をクリックすると拡大表示します) 画面6 オーダーの登録を確認する(画面をクリックすると拡大表示します)

 Ordersテーブルにデータが1件、Order Detailsテーブルにデータが2件登録されているのが確認できたでしょうか。では、SQL文の解説に移る前に、もう1つ確認のステップを実行してみましょう。先ほどの例題1のSQLの75行目の「13」を「130」に変更してもう1度実行してみます。ProductIDが130の商品は登録されていないため、エラーが発生するはずです。

画面7 未登録商品を登録するとエラーが発生する(画面をクリックすると拡大表示します) 画面7 未登録商品を登録するとエラーが発生する(画面をクリックすると拡大表示します)

 エラーが発生して、「TRANSACTION ROLLBACKED」と表示されたはずです。例題2と同様に、テーブルにデータが登録されているか否かを確認してみましょう。

画面8 Order テーブルで未登録を確認(画面をクリックすると拡大表示します) 画面8 Order テーブルで未登録を確認(画面をクリックすると拡大表示します)

 OrdersテーブルにもOrder Detailsテーブルにもデータが登録されていないのが確認できたでしょうか。では、SQL文の解説に移りましょう。

テーブル間の関連の確認

 トランザクション処理の解説に移る前に、まず、今回の例題で関連するテーブルについて確認しておきましょう。データの登録において直接関連するテーブルは、OrdersとOrder Detailsテーブルです。この2つのテーブルにデータを登録することで、受注を登録できます。

 OrdersテーブルとOrder Detailsテーブルにデータを登録するためには、幾つかの関連するテーブルとのリレーションを設定する必要があります。リレーションを設定するためにはどうすればよいかといえば、外部キーとして設定されているカラムに、参照先のテーブルに存在する値を設定して登録すればいいですね。

 例題1で作成したダイアグラムを利用して外部キーを確認してみましょう。Ordersテーブルを右クリックして、プロパティを選択し、リレーションシップタブを表示します。

画面9 トランザクションを開始する(画面をクリックすると拡大表示します) 画面9 トランザクションを開始する(画面をクリックすると拡大表示します)

 このプロパティ画面の「選択したリレーションシップ」ドロップダウンリストを表示させると、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実践講座」バックナンバー

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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