条件分岐のあるストアドプロシージャ：SQL実践講座（21）

[篠原光太郎，＠IT]

　前回の「ストアドプロシージャの作成」に引き続き、ストアドプロシージャの作成に挑戦します。今回は、前回の単純なSQL文によるストアドプロシージャに加えて、Transact-SQLのIF文による条件分岐を取り上げていきます。

　では早速、例題を実行しながら、SQLの確認をしていきましょう。

IF文を使用するストアドプロシージャの作成

　前回は、CustomerIDを引数に取るSelectContactというストアドプロシージャを作成しました。今回は、Regionを引数に取る、同様なストアドプロシージャを作成してみましょう。

　前回引数としたCustomerIDは主キーでしたので、すべての行のCustomerIDには値が保存されていましたが、今回引数とするRegionは、NOT NULLの制約が定義されていません。このため、NULL値の場合の検索も可能となるように、ストアドプロシージャを作成します。クエリアナライザから下記のSQLを実行し、SelectContactByRegionというストアドプロシージャを作成してみましょう。

【例1】

CREATE PROCEDURE SelectContactByRegion @Region nvarchar(15) AS
  IF @Region is NULL
    SELECT CustomerID, CompanyName, ContactName, Phone
    FROM Customers
    WHERE Region is NULL
  ELSE
    SELECT CustomerID, CompanyName, ContactName, Phone
    FROM Customers
    WHERE Region = @Region

画面1　CREATE文で「SelectContactByRegion」ストアドプロシージャを作成する（画面をクリックすると拡大表示します）

　例1のSQL文を実行すると、「SelectContactByRegion」という名称の新しいストアドプロシージャが作成されます。確認のために、SelectContactByRegionストアドプロシージャを実行してみましょう。引数には、WA（ワシントン州）を指定してみましょう。

【例2】

EXEC SelectContactByRegion WA

画面2　画面1で作成したストアドプロシージャを引数を指定して実行する（画面をクリックすると拡大表示します）

　引き続き、NULLを引数に指定した場合も正常に動作することを確認してみましょう。

【例3】

EXEC SelectContactByRegion NULL

画面3　画面1で作成したストアドプロシージャを、今度は引数にNULLを指定して実行する（画面をクリックすると拡大表示します）

　例2と例3の実行結果を見ると分かるとおり、作成したストアドプロシージャは値を指定した場合もNULLを指定した場合も、それぞれ正常にSELECTの実行結果を返します。では、例1のストアドプロシージャの定義を確認してみましょう。

条件分岐を行うIF文

　例1では、引数がNULLか否かを判別するために、IF文を使用しています。IF文はほかの言語のIF文と同様に、IF句の後に条件式を指定し、条件式がTRUEの場合に実行するSQL文と、FALSEの場合に実行するSQL文とを指定することができます。構文は次のとおりです。

IF （条件式）（条件式がTRUEの場合に実行するSQL文）

ELSE （条件式がFALSEの場合に実行するSQL文）

　最後に、END IFのようなIF文を閉じる句は必要ありません。また、ELSE以降は必須ではなくオプショナルです。条件式には、SELECT文のWHERE句に使用できる構文や関数は、すべて使用することができます。例1では、「IF @Region is NULL」として、引数の「@Region」が「NULL」か否かを検査しています。

　IF文がTRUEの場合、条件式の直後に指定したSQL文を実行します。例1では、WHERE句に「Region is Null」と指定したSQL文が実行されます。そして、IF文がFALSEの場合は、ELSE句の直後に指定したSQL文が実行されます。例1では、WHERE句に「Region = @Region」と指定したSQL文ですね。

SQL文を複数指定する

　次に、ストアドプロシージャの中に複数のSQL文を指定する方法を見ていきましょう。では、例4を実行して、例1のSelectContactByRegionを置き換えます。

【例4】（リストをクリックすると、別ウィンドウで表示します）

画面4　画面1で作成したストアドプロシージャを書き換える（画面をクリックすると拡大表示します）

　例4のSQL文を実行すると、「SelectContactByRegion」の定義が更新されます。確認のために、SelectContactByRegionストアドプロシージャを実行してみましょう。引数には、CA（カリフォルニア州）を指定してみます。

画面5　例4で作成したストアドプロシージャを実行する（画面をクリックすると拡大表示します）

　ここでさらに確認していただきたいのは、クエリアナライザの「メッセージ」タブです。「メッセージ」タブを表示すると、次のように表示されているはずです。

画面6　例4で作成したストアドプロシージャの実行結果を、「メッセージ」を開いて確認する（画面をクリックすると拡大表示します）

　SQL Server 2000より前のバージョンのクエリアナライザでは、「グリッド」「メッセージ」といったタブは表示されず、「結果」というタブに表示されます。SQL Server 2000でも必要になることがありますので、SQL Server 2000以前と同様な表示になるよう、オプションを変更してみましょう。

例5：「クエリアナライザの結果表示を変更する」

(1) クエリアナライザのメニューから、「ツール」→「オプション」を選択する

画面7　クエリアナライザのメニューから、「ツール」→「オプション」を選択する（画面をクリックすると全体を表示します）

(2) 「結果」タブを選択し、規定の結果ターゲットを「結果をテキストで表示」に設定する

画面8　ダイアログボックスの「結果」タブを開き、「規定の結果ターゲット」を「結果をテキストで表示」に変更する（画面をクリックすると拡大表示します）

(3) 「OK」ボタンを押して設定を反映させ、再度SQL文を実行する

　では、順に解説をしていきましょう。

BEGINとENDによるSQLブロックの構成

　例4では、「ALTER PROCEDURE 〜 AS」の直後が「BEGIN」で始まっています。これは、複数のSQL文を束ねる構文で、「END」と対で使用します。例4では、3組のBEGINとENDを使用していますので、それぞれ対応するBEGINとENDの対応が分かりやすいように、コメントに番号を記述しました。「--」（マイナス記号2つ）は、SQL92の標準のコメント識別記号で、「--」以降その行の最後までが、コメントと見なされます。

　AS句直後のBEGINの対のENDは、最後のENDです。コメントで(1)と記された個所に当たります。AS句には、ストアドプロシージャの本体として、これまでの例のように1つのSQL文か、もしくは、BEGINとENDでくくられたSQLブロックを指定することができます。例4では、BEGINとENDでくくられている個所には、実は1組のIF文しか存在しませんので、BEGIN?ENDは省略可能です。

　IF文の中で使用している(2)と(3)のBEGIN〜ENDには、例1で使用したSELECT文に加えて、「PRINT」文を記述しました。PRINT文は、BASIC言語のPRINT文と同様に、パラメータに指定した文字列を結果として出力します。どこに出力されるかというと、先ほどの例の結果で確認したとおり、「メッセージ」タブに、ほかのSQL文の実行結果とは別に表示されます。また、「結果をテキストで表示」モードにした場合は、「結果」タブに、ほかのSQL文の実行結果と同様に表示されることが確認できたと思います。

　例4のように、IF文の条件分岐が正常に行われているか否かを確認する場合や、変数などの値を確認するのに、PRINT文は便利ですね。

SELECTの結果による条件分岐

　では、もう少し複雑な条件分岐の指定をしてみましょう。次の例では、引数で指定されたRegionがデータベース上に存在するか否かをチェックし、存在しない場合はエラーメッセージを表示します。

【例5】（リストをクリックすると、別ウィンドウで表示します）

　例5を確認するために、SelectContactByRegionのパラメータに「CA」と「JP」をそれぞれ指定して実行してみましょう。「JP」を指定した場合の実行結果は、次の画面のようになるはずです。

画面9　例5の実行結果（画面をクリックすると拡大表示します）

　では、例5のSQL文を確認してみましょう。

　2つ目のIF文で、Regionが存在するか否かを確認するために、SELECT文でCustomersのCOUNTを求めています。このCOUNTの値が0よりも大きければ、指定したRegionのCustomersが存在することになります。これを、IF文の条件式として指定しています。この例で示したように、指定できるSQL文は、値をただ1つだけ返すものでなくてはなりません。

　2つ目のIF文は、「ELSEIF」という1つの句ではないことに気を付けてください。Transact-SQLには、ELSEIF句は存在しません。ただし、例5のように、IF文を入れ子にして指定することは可能ですので、ELSEIFと同等の処理を記述することは可能です。

次回の予定

今回は、IF文を使用したストアドプロシージャの作成について解説しました。次回は、変数の定義、繰り返し構文を利用したストアドプロシージャの作成について紹介する予定です

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう