ストアドプロシージャを使う

さて、今度はストアドプロシージャを使ってみよう。

●とりあえず一発。

パラメータなし、変数なし、返り値なし、一コマンド実行するだけ、という超シンプル版で動作確認してみよう。

1> CREATE PROCEDURE PriceDown AS
2>     Update BookTable SET Price = Price/2
3> GO

ストアドプロシージャができた。はたして動くのか？

1> EXEC PriceDown
2> GO
(4 件処理されました)
1> SELECT * from BookTable
2> GO
 BookID      Title                AuthID      Price
 ----------- -------------------- ----------- -----------
           1 本その１                     102         250
           2 本その２                     101          15
           3 本その３                     103         500
           4 Book No.Four                 101        5000

動いた。各本の価格も半額になっている。 SELECTの出力は例によってずれるので整形してある。

●引数を持たせてみよう

引数をもつストアドプロシージャを作ってみる。
各本の金額を第一引数でかけて新しい金額にする。
％指定にして100にすると金額が変わらないことにしよう。
デフォルトは100だ。

1> CREATE PROCEDURE PriceChange (@NewRate INTEGER = 100 ) AS
2>     Update BookTable SET Price = Price*@NewRate / 100
3> GO
1> EXEC PriceChange 200
2> GO
(4 件処理されました)
1> SELECT * from BookTable
2> GO
 BookID      Title                AuthID      Price
 ----------- -------------------- ----------- -----------
           1 本その１                     102         500
           2 本その２                     101          30
           3 本その３                     103        1000
           4 Book No.Four                 101       10000
(4 件処理されました)

倍になって、もとの価格に戻った。
値を指定しないと価格は変わらないはず。

1> EXEC PriceChange
2> GO
(4 件処理されました)
1> SELECT * FROM BookTable
2> GO
 BookID      Title                AuthID      Price
 ----------- -------------------- ----------- -----------
           1 本その１                     102         500
           2 本その２                     101          30
           3 本その３                     103        1000
           4 Book No.Four                 101       10000
(4 件処理されました)

おーけー。

●値を返す。

値を返すストアドプロシージャを書いてみよう。
T-SQLでは値を返す方法は2通りある。
単一のINTEGERならRETURN文でプロシージャの返り値として戻せる。
また、複数の値やINTEGER以外の値を返すために引数にOUTPUT指定もできる。

まずはRETURN文を使ってみよう。
指定したBOOKIDの著者の年齢を返す。
いつまでも１行プロシージャじゃつまらないので、 BookIDがなかったら-1歳を返すくらいのことはしてみよう。

1> CREATE PROCEDURE GetAuthorAge (@BookID INTEGER ) AS
2>    DECLARE @AuthorAge INTEGER
3>    SET @AuthorAge = -1
4>    IF ( SELECT COUNT(*) FROM BookTable WHERE BookID=@BookID ) > 0
5>        SELECT @AuthorAge = AuthorTable.Age From AuthorTable, BookTable
6>               WHERE BookTable.BookID = @BookID
7>    RETURN @AuthorAge
8> GO

実行するだ。

1> DECLARE @Result INTEGER
2> EXECUTE @Result = GetAuthorAge 3
3> PRINT @Result
4> GO
10
1>

BookID=3の本の著者は、三郎さんで１０歳なのであっている。
次に存在しないBookIDを与えてみよう。

1> DECLARE @Result INTEGER
2> EXECUTE @Result = GetAuthorAge 10
3> PRINT @Result
4> GO
-1

おーけー。

では次にOUTPUTで値を返すのを試してみよう。
全著者の年齢の平均値を返す。

1> CREATE PROCEDURE GetAuthorAve (@AuthorAve FLOAT OUTPUT) AS
2>    DECLARE @AuthorCount INTEGER
3>    SET @AuthorAve = 0
4>    SELECT @AuthorCount = COUNT(*) FROM AuthorTable
5>    IF @AuthorCount > 0
6>        SELECT @AuthorAve=SUM(Age)*1.0/@AuthorCount FROM AuthorTable
7> GO

実行してみる。

1> DECLARE @Result FLOAT
2> EXECUTE GetAuthorAve @Result OUTPUT
3> PRINT @Result
4> GO
53.3333
1>

3人の年齢を足すと160歳だから、３人で割ると53と1/3才。
あってる。よし。

●カーソルを使ってみる。

プロシージャの中でカーソル変数を使ってループさせてみよう。
行ごとに処理するときの基本形。
ここでは、さっき作った PriceChange プロシージャと同じものを作る。
もちろん、一行でかけるわけだが、実際には、新しい値の計算に他のプロシージャを使うなど一行ごとに処理しなければならない場合もある。

プロシージャはこんな感じだ。

CREATE PROCEDURE PriceChange2 (@NewRate INTEGER = 100 ) AS

	DECLARE @cur CURSOR

	SET @cur = CURSOR FOR
	    	SELECT Price
			FROM BookTable
			FOR UPDATE OF Price

	-- データフェッチ用変数の宣言
	DECLARE @old_price int

	OPEN @cur
  
	-- FETCHでデータが終わるまで次々とレコードを取り出す。

	FETCH NEXT FROM @cur Into @old_price
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- 現在のカーソルのデータを更新する
		UPDATE BookTable SET Price = @old_price*@NewRate/100
			WHERE CURRENT OF @cur

		FETCH NEXT FROM @cur Into @old_price
	END
		
	CLOSE @cur
	DEALLOCATE @cur	
GO

結果は、PriceChangeの時の同じなので省略。
カーソル変数を宣言して、SETで割り当ててからOPENする。
FETCHでレコードを取り出し、@@FETCH_STATUSを見ながらループさせるのが基本のようだ。
最後にCLOSEして、DEALLOCATEする。

ここでちょっと疑問。
FETCHで値は取り出さずにUPDATEで値をセットするだけのフィールドは、SELECT文にリストする必要はあるのだろうか。
というわけで、BookIDをそのまま値段にしてしまうプロシージャを作ってみよう。

CREATE PROCEDURE ChangePriceToBookID  AS

	DECLARE @cur CURSOR

	SET @cur = CURSOR FOR
	    	SELECT BookID
			FROM BookTable
			FOR UPDATE OF Price

	-- データフェッチ用変数の宣言
	DECLARE @tmp_BookID int

	OPEN @cur
  
	-- FETCHでデータが終わるまで次々とレコードを取り出す。

	FETCH NEXT FROM @cur Into @tmp_BookID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- 現在のカーソルのデータを更新する
		UPDATE BookTable SET Price = @tmp_BookID
			WHERE CURRENT OF @cur

		FETCH NEXT FROM @cur INTO @tmp_BookID
	END
		
	CLOSE @cur
	DEALLOCATE @cur	
GO

こいつを実行してどうなるか見てみよう。

1> EXEC ChangePriceToBookID
2> GO
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
1> SELECT * FROM BookTable
2> GO
 BookID      Title                AuthID      Price
 ----------- -------------------- ----------- -----------
           1 本その１                     102           1
           2 本その２                     101           2
           3 本その３                     103           3
           4 Book No.Four                 101           4
(4 件処理されました)

大丈夫みたい。

Priceの値が腐ってしまったので元のデータをセットしておこう。

1> UPDATE BookTable SET Price=500   WHERE BookID=1
2> UPDATE BookTable SET Price=30    WHERE BookID=2
3> UPDATE BookTable SET Price=1000  WHERE BookID=3
4> UPDATE BookTable SET Price=10000 WHERE BookID=4
5> GO
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)
(1 件処理されました)

というわけでこの項おわり。

MSDE 実験ページへ戻る。