ストアドプロシージャに記述する処理はSQL文ですから、SELECTやDELETEなどのSQL命令の他に関数も利用できます。関数を使った年齢計算の例を紹介しておきましょう。
- 時間・期間の計算処理 -
このコラムの第22回「SQLと関数～文字列関数と日付関数」のところで、時間や期間の計算をSQLで行えるので便利だというまとめを行い、最後にこう結びました。
| 日付関数は、日付/時刻型のフィールドの値を元に、作業期間や勤務時間、年齢などの情報を計算する際に役立ちます。これは、アプリケーションのレベルで各言語の命令を用いて計算することも可能ですが、結果セットとしてSQLのレベルでそれらを取得できれば、グリッドやテキストボックスへ表示する際にアプリケーションのソースコードを記述する必要がなくなります。 |
アプリケーションで多用される時間や期間の計算処理は、ストアドプロシージャにしておくと便利です。但し、当然のことですが、計算の元になるデータがテーブルあるいはビューから導き出されるデータセットの中に存在していなければなりません。
計算の元になるデータがアプリケーションの処理内で（SQLとは別に）取得される場合は、アプリケーションの中で計算する必要があります。
では、テーブル「得意先_mr」の「生年月日」フィールドの値を元に、顧客の現在の年齢を計算する処理を作ってみましょう。現在の満年齢は
現在の日付 - 生年月日
の結果から「年」の値だけを取り出すことで得られます。もちろん、上の減算だけで「何歳と何箇月何日」（さらに何時間何分何秒まで）取得できますが、通常の処理では「何歳（年）」だけで十分です。
- 現在の満年齢を求める -
Transact-SQLには日付/時刻型の２つの値の差――時間・期間を計算する関数DATEDIFFがあります。さらにDATEDIFF関数は第１引数で取得したい時間・期間の書式を指定できるため、上の減算式はDATEDIFF関数を使って
DATEDIFF("yy", 生年月日, 現在の日付)
と記述できます。現在の日付はGETDATE関数で取得できます。従って、顧客の現在の満年齢を求めるストアドプロシージャ“GetAge”は、以下のように記述できます(ex01.sql)。
Create Procedure GetAge
@CstmId int ----------------------------------- (1)
As
DECLARE @Bday datetime ------------------------ (2)
SELECT @Bday = 生年月日 FROM 得意先_mr -------- (3)
WHERE お客様ID = @CstmId ---------------------- (4)
Return (DATEDIFF("yy", @Bday, GETDATE())) ----- (5)
- Returnで関数の戻り値を返す -
ソースコードを説明しておきましょう。
(1)引数に「得意先ID」を取る
引数はint型の“@CstmId”です。(4)のWHERE句による条件式で、この値を使ってレコードを絞り込みます。
(2)変数を宣言
生年月日を保持する変数“@Bday”を宣言します。
(3)変数に生年月日を代入
SELECT命令を使い、(2)で宣言した変数“@Bday”にテーブル「得意先_mr」の「生年月日」フィールドの値を代入します。
(4)レコードを絞り込む
WHERE句の条件に(1)で宣言した引数（得意先ID）を指定し、レコードを絞り込みます。
(5)満年齢を計算
DATEDIFF関数は、戻り値の書式・期間の開始・期間の終了――の３つの引数を取ります。第１引数の"yy"は年を２桁で取り出す指定です。第２引数に生年月日、第３引数に現在の日付を取得するGETDATE関数を指定します。
Return命令は整数値を返すので、その中で上述のDATEDIFF関数を実行してその結果を返すようにしています（datetime型の値が整数型にキャストされます）。
- ストアドプロシージャの実行 -
このストアドプロシージャをクエリアナライザで実行するには、前回説明したように引数を先に宣言してからEXECUTE命令を使います。「得意先ID＝34」のレコードを対象とするなら、以下のようになります(ex02.sql)。
DECLARE @Age int
EXECUTE @Age = GetAge 34
SELECT @Age '年齢'
アンダーラインの箇所を書き換えれば、他の得意先（顧客）の年齢を取得できます。
このように、ストアドプロシージャ内ではSQLの関数を使って複雑な計算を行い、その結果を得ることができます。ここではReturn命令で整数値を返す処理を紹介しましたが、前回説明したようにOUTPUTで戻り値用の変数を宣言すれば、様々な型の値を返すことができます。
 |
|
|
