ストアドプロシージャの作成：SQL実践講座（20）

» 2002年08月17日 00時00分公開

　今回から数回にわたり、ストアドプロシージャの作成に挑戦します。ストアドプロシージャの利用については、前回の「システム・ストアドプロシージャを用いたロールの詳細設定」の中で取り上げました。今度は、Transact-SQLを使用して、ユーザー定義のストアドプロシージャを作成します。

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

SELECTを実行するストアドプロシージャ

　最初に、非常に簡単なストアドプロシージャを作成してみましょう。作成するのは、Customerテーブルから「顧客ID（CustomerID）」「会社名（CompanyName）」「担当者名（ContactName）」「電話番号（Phone）」を抽出するストアドプロシージャです。クエリアナライザから下記のSQLを実行してみましょう。

【例1】

CREATE PROCEDURE SelectContact AS
  SELECT CustomerID, CompanyName, ContactName, Phone
  FROM Customers

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

　例1のSQL文を実行すると、「SelectContact」という名称の新しいストアドプロシージャが作成されます。確認のために、SelectContactストアドプロシージャを実行してみましょう。ストアドプロシージャの実行を行うSQL文は、EXECでしたね。

【例2】

EXEC SelectContact

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

　例2の実行結果を見ると分かるとおり、作成したストアドプロシージャは、

SELECT CustomerID, CompanyName, ContactName, Phone 
FROM Customers

というSELECT文を実行したのと同じ結果を返します。例1のCREATE文のAS句以降に記述されたSELECT文と同じですね。

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

　例1は、ストアドプロシージャの非常に単純な例です。テーブル作成やビュー作成の際に説明したのと同様、ストアドプロシージャの作成にはCREATE文を利用します。テーブル作成の際は、「CREATE TABLE テーブル名」、また、ビューの作成の際は、「CREATE VIEW ビュー名」としましたね。ストアドプロシージャの場合は、例1のように「CREATE PROCEDURE ストアドプロシージャ名」とします。PROCEDUREというキーワードが、ストアドプロシージャ作成の指示です。ストアドプロシージャ名は、ここでは、SelectContactでしたね。AS句以降には、定義するSQL文を記述します。

　CREATE文で作成したストアドプロシージャは、ビューと同様にDBサーバ上に格納されます。一度定義したストアドプロシージャは、権限の設定を行えば、ほかのユーザーから呼び出すことも可能になります。

　AS句に定義するSQL文は、例1のようにSELECT文をはじめ、ほとんどすべてのSQL文が使用できます。また、1つのSQL文だけではなく、複数のSQL文の指定が可能です。SQL文の実行は、基本的には定義した順番に行われますが、ほかの言語と同じように実行順を制御したり、繰り返させたりすることも可能となっています。これらのSQL文の実行制御を行うのが「Transact-SQL」というプログラム言語です。

ストアドプロシージャを作成する目的

　例1のように「データベース上に名前を付けて保存しておいたSELECT文を呼び出す」という単純な機能においては、ストアドプロシージャはビューと大して変わらないですね。ストアドプロシージャが実力を発揮するのは、Transact-SQLによるプログラムが必要になるときでしょう。データベースにプログラムを保存しておく主な目的は、次のとおりです。

繰り返して使用されるデータベースに対する処理を共通化して実装することによって、データの矛盾を防ぎたい
単独のSELECT文では処理できない複雑な条件による問い合わせを、クライアント・プログラムではなくデータベース上で実行することで、クライアントとデータベース間の通信量（トラフィック量）を抑えてレスポンスタイムを短縮化したい

　1つ目の目的の例として、従業員の登録をする際、従業員テーブルにデータを追加するだけではなく、従業員の所属する部署と役職から、データベース・テーブルへのアクセス権を設定する処理が必要な場合を考えてみましょう。この場合、それぞれの処理を別のSQL文として発行し処理することも、もちろん可能です。しかし、それを人手に任せると、「従業員の登録のみ行って、アクセス権が設定されていない」というミスが発生する可能性があります。このようなミスを未然に予防するため、「従業員登録」というストアドプロシージャを作成すれば、そのストアドプロシージャにすべての処理を順に間違いなく実行させることが可能になります。また、データベースに対して変更が加わり、追加で設定しなくてはならない項目が増えたとしても、「従業員登録」ストアドプロシージャを変更するのみで、従業員登録ストアドプロシージャを呼び出すユーザーの操作や、従業員登録ストアドプロシージャを呼び出しているプログラムの変更はしなくてもよい可能性が高まります。

　2つ目の目的の例として、在庫を問い合わせて、引き当てが可能であれば引き当てを行い、在庫数が足りなければ製造元に発注をかける処理を考えてみましょう。この処理を行うためには、次の3つの問い合わせが必要になります。

(1) 在庫の問い合わせ（在庫テーブルのSELECT）

(2) 在庫の引き当て（在庫テーブルのUPDATE）

(3) 製品の発注（発注テーブルへのINSERT）

　在庫があった場合もなかった場合も、最低2回のSQL文を発行する必要がありますが、この一連の処理を「顧客注文処理」というストアドプロシージャでまとめて作成すれば、データベースへの問い合わせはストアドプロシージャを一度呼び出すだけで済みます。多量の顧客からの注文を処理するシステムでは、クライアントとデータベース間での通信量を大幅に削減することが可能になります。

　さて、Transact-SQLを利用したストアドプロシージャは次回以降に順に解説していきます。今回は、例1を基に、いくつかの変更をしてみましょう。

ストアドプロシージャの引数

　例1は、SELECT文を実行するのみの非常に単純な例でしたので、CustomerIDで検索ができるように変更してみましょう。新しいプロシージャを作成するのではなく、ALTER文で、例1の定義を置き換えてみます。

【例3】

ALTER PROCEDURE SelectContact @CustomerID nchar(5) AS
  SELECT CustomerID, CompanyName, ContactName, Phone
  FROM Customers
  WHERE CustomerID = @CustomerID

画面3　作成したストアドプロシージャの定義をALTER文で変更したところ（画面をクリックすると拡大表示します）

　例3を実行すると、SelectContactストアドプロシージャは、CustomerIDを引数に取るように変更されます。まずは、実際に呼び出しを行ってみましょう。

【例4】

EXEC SelectContact 'ALFKI'

画面4　画面3で変更したストアドプロシージャを実行したところ（画面をクリックすると拡大表示します）

　CustomerIDが「ALFKI」の顧客のみがリストされたのが分かりますね。解説に移る前に、もう1点確認しておきましょう。引数を指定しないでSelectContactを呼び出すと、下記のとおりエラーが出ます。

【例5】

EXEC SelectContact

画面5　画面3で変更したストアドプロシージャを引数なしで実行したところ。エラーとなったことが分かる（画面をクリックすると拡大表示します）

　例3では、例1の「CREATE」に変わって、「ALTER」を利用しました。ALTERを利用すると、権限の設定はそのままで、定義を変更することが可能です。また、構文はCREATEとほぼ同様ですので、この後のALTER文に対する解説は、CREATE文でも同様に利用できます。

　さて、例1からの変更点は、まず、プロシージャ名の後の「@CustomerID nchar(5)」の部分です。これは、プロシージャに対して引数の定義を行っています。引数に対しては、必ず「@」を変数名の前に付けます。引数名の後の「nchar(5)」は、引数のデータ型の指定です。引数のデータ型の種類は、データベースのカラムのデータ型と同じです。例3では、引き渡すCustomerIDの型がnchar(5)ですので、それに合わせてnchar(5)としました。

　引数で宣言した変数は、ローカル変数です。つまり、ストアドプロシージャの中でのみ参照することが可能となります。例3の@CustomerID引数は、SelectContactストアドプロシージャの中でのみ参照が可能であり、ほかのストアドプロシージャからの参照はできません。

　例1からの変更点の2つ目は、SELECT文のWHERE句です。「WHERE CustomerID = @CustomerID」として、CustomerID列を先ほど引数に指定した@CustomerID変数で検索をするよう指示しています。このように指定することによって、SelectContactストアドプロシージャの@CustomerID引数に指定した顧客IDのみを抽出することが可能となっています。

Enterprise Managerでのストアドプロシージャの確認

　では、Enterprise Managerで、作成したSelectContactストアドプロシージャを確認してみましょう。

例6：「作成したストアドプロシージャの確認」

(1) Enterprise Managerを起動する

(2) 左端に表示される「ツリー」で、以下を順に展開する

1.Microsoft SQL Servers
2. SQL Server グループ
3. サーバー名
4. データベース
5. 「Northwind」
6. ストアドプロシージャ

画面6　Enterprise Managerでストアドプロシージャの一覧を表示したところ（画面をクリックすると拡大表示します）

　表示される一覧の中に、例3で作成した「SelectContact」が含まれることが確認できますね（注：スクロールする必要があるかもしれません）。では、SelectContactストアドプロシージャをダブルクリックして、定義を表示させてみましょう。

画面7　Enterprise ManagerでSelectContactストアドプロシージャの定義を表示させたところ（画面をクリックすると拡大表示します）

　「テキスト」欄に、クエリアナライザから実行したストアドプロシージャのCREATE文が表示されていることが確認できます。SelectContactストアドプロシージャは、例3ではALTER文で変更しましたが、ここに表示されるのはALTER文ではなくCREATE文であり、変更された後のストアドプロシージャが新規に作成される場合の定義であることを確認しておきましょう。

　最後に「GO」というステートメントが追加されていますが、これはTransact-SQLの終了を意味するステートメントであり、通常は必要ありません。自動的に付加されますので、そのままにしておきましょう。

　さて、この画面でもストアドプロシージャの新規作成、および定義の変更は可能です。「テキスト」欄のSQL文を変更して、「適用」ボタンを押せば、ALTER文が生成されて定義が変更されます。また、「構文の確認」ボタンを押せば、定義を変更する前にTransact-SQLの構文のチェックが可能です。今後の例題では、クエリアナライザで定義をしていきますが、この画面を利用しても、もちろん構いません。