先の例では、IfまたはElseで条件に基づいて実行する処理が１行だけでした。実行したい処理が複数行ある場合、それらを１つのブロックに括る必要があります。
- 年次処理を手直しする -
前回紹介した年次処理“YearlyTransfer”を手直しします。内容は、以下のように「累積売上_dmy」の内容を新規テーブル「累積売上_old」にコピーし、「累積売上_dmy」を削除する――というものでした。
Create Procedure YearlyTransfer
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
DELETE 累積売上_dmy
通常はこれで特に問題はありませんが、万一何らかのエラーが発生してもユーザーにはそれを知るすべがありません。特に、テーブルの操作処理を２段階に実行しているため、先のINTO命令によるコピー処理が失敗した場合、続くDELETE命令の処理もエラーとなってしまいます。
そこで、それぞれの処理の後でエラーが発生したかどうかを調べ、エラーだったら処理を中止してその旨をユーザーに知らせるようにしてみます。
- エラー時に処理を切り替える -
SQL Serverでは、データベース操作でエラーが発生した場合、@@ERROR関数の戻り値でエラーコード（エラー番号）が返されます。正常終了した場合は0が返ります。
@@ERRORは名前もその役割も一見するとシステム変数のようですが、エラーコードを返すシステム関数です。戻り値はint型で、返すエラーコードは直前のTransact-SQLのステートメント（命令文）の結果を反映します。
２つの処理に対してエラー時の対応を考慮すれば、単純に以下のような形が考えられます。
Create Procedure YearlyTransfer2
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
If @@ERROR <> 0 ---- エラーが発生すればエラーコードを返す
Return @@ERROR
Else --------------- そうでなければ次の処理を実行する
DELETE 累積売上_dmy
If @@ERROR <> 0 -- エラーが発生すればエラーコードを返す
Return @@ERROR
- メッセージを表示させる -
しかし、これでは少し不親切なので、クエリアナライザのメッセージ・ペインにメッセージも表示させてみましょう。メッセージの表示にはPrint命令を使い、文字列を''で囲んで指定します。
Create Procedure YearlyTransfer2
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
If @@ERROR <> 0 ---------- 最初の処理がエラーだった場合
Begin
Print 'コピー操作でエラーが発生しました。'
Return @@ERROR
End
Else --------------------- 最初の処理が正常終了した場合
Begin
DELETE 累積売上_dmy -- ２番目の処理を実行
If @@ERROR <> 0 ------ ２番目の処理がエラーだった場合
Begin
Print '削除操作でエラーが発生しました。'
Return @@ERROR
End
End
Else ------------------- ２番目の処理も正常終了した場合
Begin
Print '処理は完了しました。'
Return 0
End
- Begin～Endによる処理ブロック -
上の例では、最初のSELECT INTO命令を実行した後、“If @@ERROR <> 0”としてエラーコードが0かどうかを調べ、0でなければPrint命令で「コピー操作でエラーが発生しました。」と表示し、エラーコードを返します。
最初の処理が正常に実行されればElse以降の処理が実行されます。まずDELETE命令でテーブルの削除処理が実行され、その結果がエラーならPrint命令で「削除操作でエラーが発生しました。」と表示し、エラーコードを返します。
ここでもエラーがなければ、最後にPrint命令で「処理は完了しました。」と表示して0を返します。
この処理では、複数の命令行をBegin～Endで括っています。IfやElseに続く処理が１行だけの場合はそのまま記述して構いませんが、IfやElseに続いて複数の行を実行したい場合は、それらをBegin～Endで括らなければなりません。
つまり、以下のような書式になります。
If <条件>
Begin
<処理1>
End
Else
Begin
<処理2>
End
- Begin～Endの入れ子構造 -
上の例のように、Begin～Endで括った処理ブロックは入れ子にすることもできます。
Begin ------------------- (a)
DELETE 累積売上_dmy
If @@ERROR <> 0
Begin ----------------- (b)
Print '削除操作でエラーが発生しました。'
Return @@ERROR
End ------------------- (b')
End --------------------- (a')
(a)のBeginと(a')のEndが対応して外側のブロックを作り、その中で(b)のBeginと(b')のEndが対応して内側のブロックを作っています。
- 実行前の準備 -
では、プロシージャを実行してみましょう。その前に準備が必要です。前回、この元になった“YearlyTransfer”を実行しているため、テーブル「累積売上_old」が存在しているはずです。SELECT INTO命令は新規にテーブルを作成するため、今作った““YearlyTransfer2”をこのまま実行すると、「データベースにオブジェクト名 '累積売上_old' が既に存在します。」というメッセージが表示されます。
また、テーブル「累積売上_dmy」も内容を削除されてレコードが１件もない状態にされているはずです。そこで、まず「累積売上_old」と「累積売上_dmy」を削除し、オリジナルのデータを保存している「累積売上_fx」の内容を「累積売上_dmy」にコピーしておかなければなりません。
以下の命令(ex07.sql)を実行しておきましょう。
DROP TABLE 累積売上_old
DROP TABLE 累積売上_dmy
SELECT * INTO 累積売上_dmy FROM 累積売上_fx
- プロシージャの実行 -
続いて、エラー番号を表示させるために以下のような命令を実行します(ex08.sql)。
DECLARE @Result int
EXECUTE @Result = YearlyTransfer2
SELECT @Result 'エラー番号'
処理が正常終了すれば、グリッド・ペインにはエラー番号として0が表示されます。メッセージ・ペインには(xx 件処理されました)に続いて「処理は完了しました。」と表示されます。
- If～Elseの入れ子構造 -
Transact-SQLで提供される言語構造では、条件判断と分岐にはIf～Elseという形式しか使えません。一般的なプログラミング言語のようにIf～Else If～Elseといった形で複雑な分岐構造を作ることはできないので、そのような段階的な分岐ではIf～Elseのネスト(入れ子）を用いることになります。
先の“YearlyTransfer2”を「もしエラーが発生すれば……」ではなく「もしエラーがなければ……」という形に書き換え、If～Elseの入れ子構造を作ってみましょう(ex09.sql)。
Create Procedure YearlyTransfer3
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
If @@ERROR = 0 ----------- コピーが正常終了した場合★
Begin
DELETE 累積売上_dmy ---- 削除処理を実行
If @@ERROR = 0 --------- 削除も正常終了したら
Begin ------------------ 処理終了
Print '処理は完了しました。'
Return 0
End
Else ------------------- 削除がエラーの場合
Begin ------------------ メッセージを表示して終了
Print '削除操作でエラーが発生しました。'
Return @@ERROR
End
End
Else --------------------- コピーがエラーの場合★
Begin -------------------- メッセージを表示して終了
Print 'コピー操作でエラーが発生しました。'
Return @@ERROR
End
結果は“YearlyTransfer2”と同じなのですが、SELECT命令によるコピー処理が正常終了した場合（最初の★マーク）とエラーだった場合（後の★マーク）とが離れすぎているため、人間がソースを読む際に混乱する可能性があります。
これがVisual BasicやC++などの言語だったら、処理ブロックの切れ目を判断しやすいため読み取りは困難ではないでしょう。Transact-SQLでは、ぱっと見ただけではIf～ElseやBegin～Endの対応が分かりにくいため、このように対応するIfとElseを離す構造はあまりお奨めできません。参考までに紹介したものと捉えてください。
SQLを関数を利用し、条件判断と分岐の構造を取り入れれば、複雑な処理を記述できるようになります。が、ストアドプロシージャはサーバー側で実行されるため、あまり複雑で大規模な処理や局所的な機能を実現する処理を作るのは避けた方が賢明です。
ストアドプロシージャでは、あくまで大局的な共通処理を作るよう心がけておきましょう。
 サンプルファイル (LZH形式
1.44KB)
 |
