ストアドプロシージャについて

ストアドプロシージャについて

ストアドプロシージャとは、SQLによる一連の命令を処理の流れに沿ってまとめ、プログラムとして記述したものとなります。

1.ストアドプロシージャとは何か？

ストアドプロシージャとは、データベースに対する一連の処理をSQLで記述し、サーバーに格納しておくものです。サーバにストア(格納)しておくプロシージャ(一連の処理)という意味でストアドプロシージャと呼ばれます。
ストアドプロシージャを記述する言語はSQLですが、SQL-92規格では、ストアドプロシージャの仕様を定義していません。ですので主要なRDBMS毎に独自に定義しています。OracleではPL/SQL、SQL ServerではTransact-SQLという拡張SQLが実装されています。このように製品間で記述方法が異なる為ストアドプロシージャには製品間の互換性はありません。

2.ストアドプロシージャの作成・実行・削除

①.ストアドプロシージャの作成

まず、ストアドプロシージャの作成時の構文を説明します。

【Oracle】
CREATE [or REPLACE] PROCEDURE <ストアドプロシージャ名>
[(<引数1>、<データ型>、<引数2>、<データ型>、・・・)] IS
[(<変数1>、<データ型>、<変数2>、<データ型>、・・・)] BEGIN
<具体的な処理内容>;
END;

【SQL Server】
CREATE [or REPLACE] PROCEDURE <ストアドプロシージャ名>
[(<@引数1>、<データ型>、<@引数2>、<データ型>、・・・)] AS
[DECRARE <@変数1>、<データ型>、<@変数2>、<データ型>、・・・] BEGIN
<具体的な処理内容>;
RETURN

OracleもSQL Serverもストアドプロシージャ名を指定し、続いて引数を定義します。引数は呼び出し元から渡されるデータを指し、ここで引数名とデータ型を指定します。SQL Serverでは最初に’＠’をつけます。

②.ストアドプロシージャの登録

ストアドプロシージャのサーバへの登録はCREATE PROCEDURE文をSQLとして実行することにより行います。今回は、SQL Serverの無償版で試してみます。

対象のテーブル：支払テーブル

登録するストアドプロシージャ：dbo.支払抽出

登録は、このCREATE PROCEDURE文をSQLとして実行することで行います。
実行すると以下のようにプログラミングの中のストアドプロシージャの中に登録されます。

③.ストアドプロシージャの実行

実行は、実行対象のストアドプロシージャを選択し右クリックから、ストアドプロシージャの実行を選択することで行います。

実行が終わるとSQLクエリパネルに表示されます。
今回登録したストアドプロシージャはSELECT文を2つ実行するSQLですので、その結果が2つ表示されます。

3.ストアドプロシージャのプログラミング

ストアドプロシージャは、引数を使ったり、制御文を入れることでより複雑な処理を実行することができます。

①.引数の使用方法

引数を使用するストアドプロシージャはCREATE PROCEDURE文の直後に引数の定義を行います。
例として以下のストアドプロシージャを実行してみます。

まず、引数として”@CODE”を定義します。そして、2つ目のSELECT文のWHERE句の抽出条件で利用します。この引数の値は、このストアドプロシージャの実行時にユーザにより指定します。実行は前項の２．の②と同様です。まずストアドプロシージャの実行を選択します。

次に引数の指定画面が出現します。ここでは”3”を入力します。

2つ目のSELECT文の結果は引数で指定された”3”がWHERE句の条件である支払コードと結びつき、結果として該当する1行が出力されます。

②.制御文の使用方法

次に制御文を使ったストアドプロシージャを見てみます。今回は例としてIF文を使ったストアドプロシージャを実行してみます。
次のストアドプロシージャで解説します。

このストアドプロシージャでは、引数の値によって実行するSQLが変わってきます。
引数（@CODE）の値によって以下のSQLを実行します。

・引数 = 1：SELECT * FROM 支払
・引数 ≠ 1：SELECT * FROM 支払 Where 支払コード >= 10

それでは実際に動かしてみた結果を見てみましょう。

一回目：引数(@CODE) = 1

二回目：引数(@CODE) = 2

一回目は、@CODEの値が”１”なので、全件抽出のSELECT文が実行されます。二度目は、@CODEの値が”２”なので、IDが10以上のレコードが抽出されるSELECT文が実行されます。