ストアドプロシージャは、処理結果を戻り値として呼び出し元に返すこともできます。
- ２通りの返し型 -
ここまでに紹介したストアドプロシージャでは、SELECT命令の処理結果を結果グリッドに表形式で表示する形でした。この形式では、アプリケーションからストアドプロシージャを呼び出した場合、SELECT命令によるSQL文を実行した場合と同じように、各フィールドを指定して返された結果（ストアドプロシージャの処理結果）を受け取る必要があります。
もちろんそれでも処理に支障はありませんが、値を返すストアドプロシージャを作っておけば、アプリケーションでEXECUTE命令を発行した結果を簡単に受け取れます。ストアドプロシージャで値を返すには、以下の２つの方法があります。
(1)戻り値用の変数を用いる
(2)Return命令を使う
- 戻り値用の変数を用いる方法～書式 -
ストアドプロシージャの処理結果を、予め宣言しておいた戻り値格納用の変数に保存すれば、処理の終了時にその値がストアドプロシージャの戻り値として呼び出し元に返されます。
戻り値用の変数は、引数宣言の最後にOUTPUTを付けて宣言します。書式は以下のようになります。
Create Procedure <名前>
@<引数1> <データ型>, @<引数2> <データ型> ...
@<変数> <データ型> OUTPUT
~~~~~~~~~
As
<処理>
- 戻り値用の変数を用いる方法～商品名を返す処理 -
商品IDを引数にして商品名を取得する処理を、この方法で作ると以下のようになります。名前は“GetItemName2”としておきます(ex05.sql)。
Create Procedure GetItemName2
@ItemId int,
@ItemName varchar(30) OUTPUT ------------ 戻り値用の変数を宣言
As
SELECT @ItemName = 品名 FROM 商品_mr ---- SQL内で変数に値を代入
WHERE 商品ID=@ItemId
int型の@ItemIdは先の例と同じで「商品ID」を受け取る引数です。
続く“@ItemName”が戻り値を格納する変数で、データ型はvarchar(30)として30文字までのテキストを受け取れるようにしておきます。
この変数“@ItemName”に、SELECT命令の処理結果である「品名」フィールドの値を受け取らせるには、以下のようなSQLを記述します。
SELECT @ItemName = 品名 FROM 商品_mr
~~~~~~~~~~~~~~~~~~~~
WHERE ...
アンダーラインの箇所で、SELECT命令による抽出結果から「品名」フィールドの値を変数@ItemNameに代入しています。
SELECT <@変数名> = <フィールド名> FROM <テーブル名>
という使い方で、SELECT命令による抽出結果から特定のフィールドの値を変数に代入できます。ストアドプロシージャでは結構よく使う書き方です。
なお、
SELECT 商品ID, @ItemName = 品名, 販売単価 FROM 商品_mr
のように戻り値としたいフィールド以外のフィールドを指定することはできません（指定するとエラーになります）。
変数への値の代入方法
SELECT命令は、以下のような書式で変数に値を代入する場合にも用いられます。
SELECT <@変数名> = <値>
但し、SELECT命令による代入はSQL Server 7以前の使用で、2000以降はSET命令を使うことが推奨されています。書式はSELECTの場合と同じです。
SET <@変数名> = <値> |
- 戻り値用の変数を用いる方法～クエリアナライザで戻り値を受け取る -
このストアドプロシージャを先ほどの値を返さない“GetItemName1”と同じように実行すれば、アプリケーションのレベルでデータベース・エンジンから戻り値を得ることができます。
しかしクエリアナライザでは、戻り値もSQL文で受け取らなければなりません。そこで、以下のような形でストアドプロシージャの戻り値を取得します。
DECLARE @<変数> <データ型>
EXECUTE <ストアドプロシージャ> [<引数>...], @<変数> OUTPUT
SELECT @<変数> '<フィールド名>'
DECLAREはストアドプロシージャ内で用いる変数を宣言する命令で、変数名に続けてデータ型を指定します。
上記の“GetItemName2”を実行した結果を30文字のvarchar型変数@Nameに受け取り、それを「商品名」というフィールドの値として結果グリッドに表示させるには、以下のようなSQLを発行します。
DECLARE @Name varchar(30) ------------ 変数を宣言
EXECUTE GetItemName2 36221, @Name OUTPUT-- 戻り値を変数に受け取る
SELECT @Name '商品名' ------------ 変数の値を表示
- Return命令を使う方法～商品IDを返す処理 -
ストアドプロシージャで返す値が正数値の場合は、Return命令を使えます。但し、テキスト（文字列）や日付/時刻（datetime型）を返すことはできません。
一般にReturn命令は「処理の成功時に0、失敗時に-1」といった処理結果の正否をアプリケーションに伝えるために用います。が、Return命令はその引数に整数値を返す式を設定できるため、処理結果として整数型フィールドの値を与えることもできます。
テーブル「商品_mr」に対して商品名の一部を与え、該当するレコードから「商品ID」を返すストアドプロシージャを作ってみましょう。名前は“GetItemId1”とし、商品名の一部からレコードを抽出できるよう、Like演算子による曖昧検索を可能にします(ex07.sql)。
Create Procedure GetItemId1
@ItemName varchar(30) -------------- 引数だけを宣言
As
DECLARE @ItemId int ---------------- 変数を宣言
SELECT @ItemId = 商品ID FROM 商品_mr --- 変数に結果を代入
WHERE 品名 Like '%' + @ItemName + '%'
Return @ItemId ----------------------- 値を返す
DECLARE命令でint型の@ItemIdという変数を宣言し、続くSELECT命令の中で
SELECT @ItemId = 商品ID FROM 商品_mr ...
として、変数@ItemIdに「商品ID」フィールドの値を受け取っています。
Like演算子を使ったWHERE条件式
WHERE 品名 Like '%' + @ItemName + '%'
の意味は、既に説明したのでお分かりでしょう※3。ここで、引数の@ItemNameに保存されている文字列を%で挟み、曖昧検索の条件を設定しています。
※3 本コラムの第５回「条件指定によるレコードの絞り込み(２)～範囲を指定しての比較、文字列の比較」を参照してください
- Return命令を使う方法～ストアドプロシージャの実行 -
作成した“GetItemId1”を実行するには、以下のようなSQLを実行します。
DECLARE @Id int ------------------ 変数を宣言
EXECUTE @Id = GetItemId1 '化粧台' --- 戻り値を変数に受け取る
SELECT @Id '商品ID' -------------- 変数の値を表示
先ほどと同じようにDECLARE命令で結果を受け取る変数@Idを宣言し、今度は
EXECUTE @Id = GetItemId1 '化粧台'
のように
EXECUTE @<変数> = <ストアドプロシージャ名> <引数>
という書式で変数に戻り値を受け取ります。
先ほどの、戻り値格納用変数を用いるストアドプロシージャでは、以下のような書式で実行時には引数の後ろに変数を記述しました。
EXECUTE <ストアドプロシージャ名> <引数> @<変数> OUTPUT
両者の違いに注意してください。
なお、WHERE句による条件指定で抽出した結果複数のレコードが存在する場合、最初の１件だけが戻り値として返されます。
- Return命令を使う方法～SELECT文をまとめる -
Return命令は変数の他に数値を返す式を与えてその結果を戻り値とすることもできます。従って先のストアドプロシージャは、以下のようにも記述できます。名前は“GetItemId2”としておきます(ex09.sql)。
Create Procedure GetItemId2
@ItemName varchar(30)
As
Return (SELECT 商品ID FROM 商品_mr
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
WHERE 品名 Like '%' + @ItemName + '%')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
アンダーラインを引いたSELECT命令全体をReturnの引数とすることで、戻り値を保存するための変数が不要になりました。
ストアドプロシージャの実行方法は、先ほどの“GetItemId1”と同じです。ここでは、文字列「ティーポット」を含む商品を表示してみます。
DECLARE @Id int
EXECUTE @Id = GetItemId2 'ティーポット'
SELECT @Id '商品ID'
- Return命令を使う方法～複数レコードに注意 -
但し、抽出結果が複数のレコードとなる場合、Returnの引数にSELECT文を与える方法ではエラーが発生します。“GetItemId2”を、先の“GetItemId1”と同じように複数のレコードが合致する「化粧台」という文字列を引数にして実行してみましょう。
DECLARE @Id int
EXECUTE @Id = GetItemId2 '化粧台'
SELECT @Id '商品ID'
すると、以下のようなエラーメッセージが表示されます。
サブクエリは複数の値を返しました。サブクエリが
= 、!= 、<、<= 、>、>= のあとに続く場合や、
サブクエリが１つの式として使われる場合に
複数の値は許可されません。
ここで言う「サブクエリ」とはReturnの引数としたSELECT文です。そして、これを()で囲んでReturnの引数とした場合、エラーメッセージの最後にある「サブクエリが２つの式として使われる場合」に該当します。
先に紹介した“GetItemId1”の場合はSELECT文は独立したクエリ（問い合わせ分）であってサブクエリではないため、複数のレコードがデータセットとして返ってきても、変数には単純にその先頭のレコードだけが代入されます。
引数を受け取ったり値を返すようにすることで、ストアドプロシージャの利用価値は高まり、適用範囲も広がります。多くのクライアントから共同利用される処理をストアドプロシージャとすることで、アプリケーションの設計やソースの記述ミスによるエラーも軽減されるでしょう。
 サンプルファイル (LZH形式
1.41KB)
 |
|
|
