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

» 2002年09月12日 00時00分 公開
[篠原光太郎@IT]

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

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

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


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

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

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

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

【例2】

EXEC SelectBirthdayByMonth
画面1 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実践講座」バックナンバー

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

AI for エンジニアリング
「サプライチェーン攻撃」対策
1P情シスのための脆弱性管理/対策の現実解
OSSのサプライチェーン管理、取るべきアクションとは
Microsoft & Windows最前線2024
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。