1.6.11 ストアドプロシジャの利用

この項では,ストアドプロシジャの利用方法について説明します。

なお,メインフレーム系のDBMSではストアドプロシジャを利用したアクセスはできません。

また,繰り返し列はストアドプロシジャでは利用できません。

<この項の構成>
(1) ストアドプロシジャの概要
(2) ストアドプロシジャの実行と結果の受け取り

(1) ストアドプロシジャの概要

ストアドプロシジャとは,SQLでデータベースアクセス処理を記述し,この処理をCREATE PROCEDUREでデータベースに登録したものです。

ストアドプロシジャでは,実行に必要な値を引数としてプロシジャに渡したり(IN),プロシジャから実行結果を戻したり(OUT),値をプロシジャに渡してその実行結果を戻したり(INOUT)できます。

ストアドプロシジャは次のような形式で作成します。これは,引数を使って値をプロシジャに渡すための例で,Sample1という名称のプロシジャを作成しています。f1=入力値の条件でレコードをtable1から検索し,そのレコードのf2フィールドの値を受け取ります。

CREATE PROCEDURE Sample1 (IN @f1 INT,OUT @f2 char(20))
BEGIN
       SELECT f2 into @f2 FROM table1  where f1=@f1;
END

なお,SQL Anywhere, Adaptive Server Anywhereの場合は,実行結果を引数としてだけでなく,ResultSetとして返すこともできます。

ストアドプロシジャ内で使用できるSQL文や文法はDBMSによって異なるので,プロシジャの作成やコンパイル方法,及びDBMSへの登録方法は,使用するDBMSのドキュメントを参照してください。

(2) ストアドプロシジャの実行と結果の受け取り

ストアドプロシジャはSQLのCALL文を使って呼び出せますが,詳細版クラスにはストアドプロシジャを扱うためのDBCallableStatementクラスがあります。このクラスには,実行するストアドプロシジャの準備,実行,引数の設定,更に,ストアドプロシジャの実行結果を取得するためのメソッドがあります。

(a) ストアドプロシジャの実行

アプリケーションでは,実行するストアドプロシジャ名をSetProcedureメソッドを使ってDBMSに通知します。引数を持つ場合,プロシジャ名の後に引数の数だけ"?"を指定し,SetParamメソッドで引数の値を設定します。次に,Executeメソッドでプロシジャを実行します。

引数を持つプロシジャの実行を図1-7に示します。

図1-7 引数を持つプロシジャの実行

[図データ]

(b) 戻り値の受取り

戻り値を引数で受け取る場合,DBCallableStatementクラスGetParamメソッドを呼び出して取得します。

(c) 戻り値として複数のレコードを取得する場合

下記DBMSの場合,ストアドプロシジャでのSELECT文の実行結果が複数のレコードでもアプリケーションで受け取ることができます。

この場合,ストアドプロシジャではResultで戻り値の型を定義し,アプリケーションでは,DBResultSetクラスのResultSetとして受取り,GetFieldメソッドで参照します。

CREATE PROCEDURE sample ()
RESULT ("Value" INT,"Shop" CHAR(30))
BEGIN
       SELECT  CAST( sum( sales_order_items.quantity * product.unit_price)
                      AS INTEGER ) AS value
               shop_name,
       FROM customer
               INNER JOIN sales_order
               INNER JOIN sales_order_items
               INNER JOIN product
       GROUP BY shop_name
       ORDER BY value desc;
END

この例では,value及びshop_nameの検索結果をResultSetとして扱うために,RESULT句でValue及びShopフィールドとそのデータ型を新しく定義しています。

また,SELECT文中に演算処理がある場合は,その結果を考慮してストアドプロシジャの作成時に,結果に合ったデータ型をRESULT句で用意しておきます。