連載

2002年9月12日

ストアドプロシージャによる繰り返し処理：SQL実践講座（22）

[篠原光太郎，＠IT] PC用表示関連情報

LINE

Hatena

　前回の「条件分岐のあるストアドプロシージャ」に引き続き、ストアドプロシージャの作成に挑戦します。今回は、前回のIF文による条件分岐から、WHILE文により繰り返し実行するSQL文を使用したストアドプロシージャを取り上げます。

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

今回登場するストアドプロシージャのコマンド群は、SQL Server 2000を題材に解説しています

誕生日の月ごとに従業員名を表示するストアドプロシージャ

　今回は、従業員テーブル（Employees）に保存されている誕生日（BirthDate）を基に、誕生日月ごとの従業員リストを作成するストアドプロシージャを作成します。クエリアナライザから下記のSQLを実行し、SelectBirthdayByMonthというストアドプロシージャを作成してみましょう。

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

　今回の例題はSQL文が随分と長くなりましたので、行頭にラインナンバーを入れました。もちろん、クエリアナライザからSQL文を入力する場合は行番号が必要ありませんので、注意してくださいね。では早速、ストアドプロシージャを実行してみましょう。SelectBirthdayByMonthストアドプロシージャは引数を取りませんので、EXECでそのまま実行します。

【例2】

EXEC SelectBirthdayByMonth

画面1　SelectBirthdayByMonthストアドプロシージャの実行結果（画面をクリックすると拡大表示します）

　SelectBirthdayByMonthストアドプロシージャを実行すると、BirthMonth列には1～12月が順に表示され、それぞれの月に誕生日がある従業員の姓名（FirstNameとLastName）が表示されます。従業員の姓名の後ろの（　）の中には、その従業員の誕生日の日付が表示されます。同じ誕生月の従業員が複数人いる場合は、横に並んで表示されます。これは、SELECT文だけではなかなか作成するのが難しい表の形式です。

SelectBirthdayByMonthストアドプロシージャの解説

　では、SelectBirthdayByMonthストアドプロシージャの定義を見ていきましょう。まず、1行目ではCREATE PROCEDURE句でストアドプロシージャの名称を指定しています。例1ではSelectBirthdayByMonthが名称で、引数はありません。2行目は、前回解説したBEGIN句で、ストアドプロシージャの定義本体の開始位置を指定します。対応するEND句は、51行目の最後のENDです。

　SelectBirthdayByMonthストアドプロシージャの本体は、大きく分けて5つの部分で構成されています。

(1) 変数とカーソルの宣言
ストアドプロシージャ内で使用するローカル変数とカーソルを宣言します

(2) 一時テーブルの作成
ストアドプロシージャ内で使用する一時テーブルを作成します

(3) 初期化処理
変数や一時テーブルの初期化を実行します

(4) メイン処理
ストアドプロシージャのメイン処理を実行します

(5) 終了処理
ストアドプロシージャの終了前処理を実行します

　では、順に見ていきましょう。

変数とカーソルの宣言

　4行目は、「DECLARE」という新出のSQL文が登場しています。これは、ローカル変数の宣言と、この後説明する「カーソル」を宣言する際に使用するSQL文です。4行目では、ストアドプロシージャ内で使用するローカル変数の宣言を行っています。書式は以下の通りです。

DECLARE （変数名）（データ型）

　例1では、@EmployeeName、@BirthDate、@Counterという名称の3つの変数を宣言しています。

DELCLAREで宣言する変数はローカル変数ですので、ストアドプロシージャの実行が終わった段階で自動的に使用できなくなります。

　次に、8～10行目のDECLARE文では、カーソルを宣言しています。カーソルとは、SELECT文の実行結果を1行ずつ取り出すことができる便利な仕組みです。通常のSELECT文では結果を表の形で返しますので、Transact-SQLのようなプログラミング言語で1行ずつ何らかの処理をしたい場合には、カーソルを使用します。カーソルの宣言は、以下の書式で行います。

DECLARE （カーソル名） CURSOR FOR （カーソルを使用するSELECT文）

　例1では、EmployeeBirthdayというカーソルを宣言しています。EmployeeBirthdayカーソルは、Employeesテーブルから従業員名（FirstName＋LastName）と誕生日（BirthDate）を1行ずつ取り出せるようにSELECT文を指定しています。

　ここで宣言したEmployeeBirthdayカーソルは、この後、FETCHというSQL文で1行ずつ取り出す処理を実行します。

一時テーブルの作成

　誕生月と従業員名の結果リストを保存するための一時テーブルを、13行目?15行目で作成します。通常のテーブルはCREATE文で作成すると、データベース上に保存が可能な形で作成されます。ここで宣言する一時テーブルは、データベース上ではなく、データベースサーバのメモリに仮想的なテーブルを作成します。一時テーブルはストアドプロシージャの実行が終了するとメモリ上から自動的に削除されますので、先に宣言をしたローカル変数のテーブル版と考えることができます。一部制約はあるものの、ストアドプロシージャ内では通常のテーブルと同じようにSELECT文でデータを抽出したり、INSERTやUPDATEでデータの登録を行ったりすることができます。

　一時テーブルの宣言は、通常のCREATE文と同じですが、テーブル名の頭に「#」を付けることで、作成するテーブルが一時テーブルであることを指定します。

　SelectBirthdayByMonthストアドプロシージャでは、「#Calendar」という一時テーブルを作成します。#Calendarテーブルに処理をしたデータを順次保存していき、最後に#Calendar一時テーブルの内容をSelectBirthdayByMonthストアドプロシージャの結果として返します。

初期化処理

　18行目では、「SET」という新出のSQL文が登場しています。SET文は、ローカル変数への値の代入と、SQL ServerにおけるSQLの実行環境を一時的に変更する際に使用するSQL文です。

　18行目は、SQL実行環境変更の例です。SET NOCOUNT OFFは、SELECT文を実行したときにクエリアナライザのメッセージタブに表示される「(X

件処理されました)」というメッセージを出さないようにSQL実行環境を変更します。実行環境の変更結果はストアドプロシージャの中でのみ有効ですので、実行が終了するとストアドプロシージャが実行される前の状態に自動的に戻ります。

　ストアドプロシージャでは、最終結果ではなく途中の処理で使用するSELECT文をたくさん使用します。SELECT文を実行するたびに「(X 件処理されました)」というメッセージを表示すると、PRINT文で明示的に表示する重要なメッセージなどを見つけにくくなるばかりか、メッセージをSQL Serverからクライアントに送信するためネットワークに無駄な負荷を掛けます。このため、ストアドプロシージャではSET NOCOUNT OFFによるメッセージ抑制が効果的です。

　次に、20行目から26行目で、先ほど作成した#Calendar一時テーブルを初期化します。ここでの初期化の目的は、1月から12月までの空のデータを作成することです。

　まず20行目で、次に説明するWHILE文で使用するカウンタである@Counterローカル変数に、初期値である「1」をセットしています。これは、SET文の「ローカル変数への値の代入」の例です。ローカル変数に値を代入する場合は、以下の書式を使用します。

SET （変数名）＝（代入する値）

　さて、21行目から26行目は、「WHILE」という新出のSQL文です。WHILE文はほかのプログラミング言語と同様、処理の繰り返しを行います。書式は以下のとおりです。

WHILE （条件式）（実行するSQL文かSQLブロック）

　21行目では、条件式を「@Counter < 13」としていますので、@Counterが12以下の場合に繰り返しループします。@Counter変数はループの中の25行目で1ずつカウントアップされるようにしましたので、結果として、@Counterが1から12まで、12回繰り返し実行されます。BASIC言語の「FOR Counter = 1 to 12　～　NEXT」と同じループになります。

　ループの中で実行する処理は、#CalendarテーブルへのINSERT処理です。#CalendarのBirthMonth列に、カウンタである@Counterローカル変数の値をセットしています。Employee列には「’’」（文字長0の文字）を初期値としてセットしています。

　以上で、メイン処理を実行するための準備が終わりました。

メイン処理

　メイン処理では、宣言したEmployeeBirthdayカーソルから1行ずつ読み込み、読み込んだ従業員の誕生日の月をキーに#Calendar一時テーブルを検索して、その行のEmployees列に従業員名を追加していきます。

　まず、29行目は、「OPEN」という新出のSQL文です。OPEN文は、カーソルを使用できるように準備します。実際には、カーソルに定義したSELECT文を実行して、その結果リストの第0行目に「現在行」をセットします。「現在行」は、カーソルが管理している読み出した行の現在位置です。

　OPENしたカーソルは、利用が終了したらCLOSEします。48行目が該当します。OPEN文とCLOSE文の書式は以下のとおりです。

OPEN （カーソル名）

CLOSE （カーソル名）

　31行目と32行目では、「FETCH」文を使用して、カーソルから1行を読み込んでいます。FETCH文の書式は、以下のとおりです。

FETCH （FETCHタイプ）（カーソル名） INTO （結果を保存するローカル変数）

　FETCHタイプは、「どの行を読み込むか」の指定をします。今回の例では「NEXT」と指定して、「現在行」の次の行を読み込むように指定しています。このほかに、最初の行を読み込む「FIRST」、最後の行を読み込む「LAST」などのオプションが指定可能ですが、NEXT以外のFETCHタイプを使用する場合には、カーソルを宣言する際に「SCROLL」オプションを指定する必要があります。

　「現在行」はOPEN文を実行した直後は「0行目」にセットされますので、最初にFETCH NEXTを実行すると1行目が読み込まれます。そして、「現在行」は読み込んだ行に自動的にセットし直されますので、次にFETCH NEXTを実行した際は、2行目が読み込まれます。

　このようにFETCH NEXTで最初の行から順に読み込んでいくと、いつかは最後の行を超えて読み込みを実行することになります。最後の行を超えたときは、もちろんデータは読み込まれず、その代わりに「@@FETCH_STATUS」というグローバルシステム変数が「-2」にセットされます。グローバルシステム変数は、SQLの実行結果ステータスなどを保存するための変数で、宣言をすることなくシステムが自動的に作成する変数です。@@FETCH_STATUSのほかに、SELECT文の結果行数を保存している「@@ROWCOUNT」などがあります。@@FETCH_STATUS は、今回の例ではWHILE文でループ終了条件に使用します。

　32行目は、FETCHの結果をローカル変数に格納するため、INTO句を使用しています。カーソルに指定したSELECT文の結果列の数だけ、ローカル変数を指定する必要があります。ここでは、先に宣言した@EmployeeNameと@BirthDateを指定しています。

　34行目から43行目が、まさにSelectBirthdayByMonthストアドプロシージャの要のループ部分です。先ほど説明したとおり、34行目で@@FETCH_STATUSが0の間（FETCHが正常終了して値が読み込めた場合のステータス）、ループをするよう指定しています。

　36行目から39行目で、読み込んだ@BirthDateを基に#Calendar一時テーブルを検索し、該当する行のEmployees列に従業員名を追加しています。このUPDATE文の中で、新出の関数を3つ使用しています。MONTH関数とDAY関数は、それぞれDATETIME型の値から「月」に該当する数値と「日付」に該当する数値を取り出します。

　次に、CAST関数は、値のデータ型を変換する関数です。書式は以下のとおりです。

CAST（（変換する元の値） AS （変換後のデータ型））

　この例では、DAY関数が返した日付の数値を、文字型（「NVARCHAR(2)」）に変換して、ほかの文字列との結合ができるようにしています。

　さて、41～42行目は、次の行の処理に向けてのFETCH NEXTです。内容は先ほど31～32行目に記述したものと同じです。

　メインのループ処理が終了すると、#Calendarには結果の情報が保存されています。45行目では、SelectBirthdayByMonthストアドプロシージャの実行結果を返すために、#Calendarから全行全項目をSELECT文で抽出しています。

終了処理

　使用したカーソルのCLOSE処理とメモリからの開放処理（DEALLOCATE）を実施しています。これで、すべての処理は終わりました。

次回の予定

今回は、WHILE文を使用したストアドプロシージャの作成について解説しました。次回は、値や表を戻り値として返すストアド・ファンクションを紹介する予定です

「次回」へ

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