公開[篠原光太郎,@IT]前回の「条件分岐のあるストアドプロシージャ」に引き続き、ストアドプロシージャの作成に挑戦します。今回は、前回のIF文による条件分岐から、WHILE文により繰り返し実行するSQL文を使用したストアドプロシージャを取り上げます。例題を実行しながら、SQLの確認をしていきましょう。2000を題材に解説しています誕生日の月ごとに従業員名を表示するストアドプロシージャ今回は、従業員テーブル(Employees)に保存されている誕生日(BirthDate)を基に、誕生日月ごとの従業員リストを作成するストアドプロシージャを作成します。クエリアナライザから下記のSQLを実行し、SelectBirthdayByMonthというストアドプロシージャを作成してみましょう。【例4】(リストをクリックすると、別ウィンドウで表示します)今回の例題はSQL文が随分と長くなりましたので、行頭にラインナンバーを入れました。クエリアナライザからSQL文を入力する場合は行番号が必要ありませんので、注意してくださいね。ストアドプロシージャを実行してみましょう。SelectBirthdayByMonthストアドプロシージャは引数を取りませんので、EXECでそのまま実行します。SelectBirthdayByMonthストアドプロシージャの実行結果(画面をクリックすると拡大表示します)SelectBirthdayByMonthストアドプロシージャを実行すると、BirthMonth列には1〜12月が順に表示され、それぞれの月に誕生日がある従業員の姓名(FirstNameとLastName)が表示されます。)の中には、その従業員の誕生日の日付が表示されます。同じ誕生月の従業員が複数人いる場合は、横に並んで表示されます。これは、SELECT文だけではなかなか作成するのが難しい表の形式です。SelectBirthdayByMonthストアドプロシージャの解説では、SelectBirthdayByMonthストアドプロシージャの定義を見ていきましょう。PROCEDURE句でストアドプロシージャの名称を指定しています。例1ではSelectBirthdayByMonthが名称で、引数はありません。2行目は、前回解説したBEGIN句で、ストアドプロシージャの定義本体の開始位置を指定します。対応するEND句は、51行目の最後のENDです。SelectBirthdayByMonthストアドプロシージャの本体は、大きく分けて5つの部分で構成されています。終了処理ストアドプロシージャの終了前処理を実行しますでは、順に見ていきましょう。変数とカーソルの宣言4行目は、「DECLARE」という新出のSQL文が登場しています。これは、ローカル変数の宣言と、この後説明する「カーソル」を宣言する際に使用するSQL文です。4行目では、ストアドプロシージャ内で使用するローカル変数の宣言を行っています。書式は以下の通りです。(変数名)(データ型)例1では、@EmployeeName、@BirthDate、@Counterという名称の3つの変数を宣言しています。DELCLAREで宣言する変数はローカル変数ですので、ストアドプロシージャの実行が終わった段階で自動的に使用できなくなります。次に、8〜10行目のDECLARE文では、カーソルを宣言しています。カーソルとは、SELECT文の実行結果を1行ずつ取り出すことができる便利な仕組みです。通常のSELECT文では結果を表の形で返しますので、Transact-SQLのようなプログラミング言語で1行ずつ何らかの処理をしたい場合には、カーソルを使用します。カーソルの宣言は、以下の書式で行います。(カーソルを使用する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文が登場しています。ServerにおけるSQLの実行環境を一時的に変更する際に使用するSQL文です。18行目は、SQL実行環境変更の例です。OFFは、SELECT文を実行したときにクエリアナライザのメッセージタブに表示される「(X件処理されました)」というメッセージを出さないようにSQL実行環境を変更します。実行環境の変更結果はストアドプロシージャの中でのみ有効ですので、実行が終了するとストアドプロシージャが実行される前の状態に自動的に戻ります。ストアドプロシージャでは、最終結果ではなく途中の処理で使用するSELECT文をたくさん使用します。Serverからクライアントに送信するためネットワークに無駄な負荷を掛けます。OFFによるメッセージ抑制が効果的です。次に、20行目から26行目で、先ほど作成した#Calendar一時テーブルを初期化します。ここでの初期化の目的は、1月から12月までの空のデータを作成することです。20行目で、次に説明するWHILE文で使用するカウンタである@Counterローカル変数に、初期値である「1」をセットしています。これは、SET文の「ローカル変数への値の代入」の例です。ローカル変数に値を代入する場合は、以下の書式を使用します。(代入する値)さて、21行目から26行目は、「WHILE」という新出のSQL文です。WHILE文はほかのプログラミング言語と同様、処理の繰り返しを行います。書式は以下のとおりです。13」としていますので、@Counterが12以下の場合に繰り返しループします。@Counter変数はループの中の25行目で1ずつカウントアップされるようにしましたので、結果として、@Counterが1から12まで、12回繰り返し実行されます。NEXT」と同じループになります。ループの中で実行する処理は、#CalendarテーブルへのINSERT処理です。#CalendarのBirthMonth列に、カウンタである@Counterローカル変数の値をセットしています。Employee列には「’’」(文字長0の文字)を初期値としてセットしています。以上で、メイン処理を実行するための準備が終わりました。メイン処理メイン処理では、宣言したEmployeeBirthdayカーソルから1行ずつ読み込み、読み込んだ従業員の誕生日の月をキーに#Calendar一時テーブルを検索して、その行のEmployees列に従業員名を追加していきます。29行目は、「OPEN」という新出のSQL文です。OPEN文は、カーソルを使用できるように準備します。実際には、カーソルに定義したSELECT文を実行して、その結果リストの第0行目に「現在行」をセットします。「現在行」は、カーソルが管理している読み出した行の現在位置です。OPENしたカーソルは、利用が終了したらCLOSEします。48行目が該当します。OPEN文とCLOSE文の書式は以下のとおりです。(カーソル名)31行目と32行目では、「FETCH」文を使用して、カーソルから1行を読み込んでいます。FETCH文の書式は、以下のとおりです。(結果を保存するローカル変数)FETCHタイプは、「どの行を読み込むか」の指定をします。今回の例では「NEXT」と指定して、「現在行」の次の行を読み込むように指定しています。このほかに、最初の行を読み込む「FIRST」、最後の行を読み込む「LAST」などのオプションが指定可能ですが、NEXT以外のFETCHタイプを使用する場合には、カーソルを宣言する際に「SCROLL」オプションを指定する必要があります。NEXTを実行すると1行目が読み込まれます。NEXTを実行した際は、2行目が読み込まれます。NEXTで最初の行から順に読み込んでいくと、いつかは最後の行を超えて読み込みを実行することになります。最後の行を超えたときは、もちろんデータは読み込まれず、その代わりに「@@FETCH_STATUS」というグローバルシステム変数が「-2」にセットされます。グローバルシステム変数は、SQLの実行結果ステータスなどを保存するための変数で、宣言をすることなくシステムが自動的に作成する変数です。@@FETCH_STATUSのほかに、SELECT文の結果行数を保存している「@@ROWCOUNT」などがあります。今回の例では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関数は、値のデータ型を変換する関数です。書式は以下のとおりです。)この例では、DAY関数が返した日付の数値を、文字型(「NVARCHAR(2)」)に変換して、ほかの文字列との結合ができるようにしています。内容は先ほど31〜32行目に記述したものと同じです。メインのループ処理が終了すると、#Calendarには結果の情報が保存されています。45行目では、SelectBirthdayByMonthストアドプロシージャの実行結果を返すために、#Calendarから全行全項目をSELECT文で抽出しています。使用したカーソルのCLOSE処理とメモリからの開放処理(DEALLOCATE)を実施しています。これで、すべての処理は終わりました。今回は、WHILE文を使用したストアドプロシージャの作成について解説しました。メールマガジンをご購読ください。申し込みページへITmediaはアイティメディア株式会社の登録商標です。