ストアドプロシージャはサーバー側に保存されて実行されるため、ネットワーク上の複数のクライアントが共通して利用できます。そのため、データベースの運用に関わる全体的な処理に向いています。
- 年次処理をストアドプロシージャに -
ここでは、販売管理システムの年次処理（１年間の取りまとめ処理）の１つをストアドプロシージャにしてみます。
処理の内容は、次のようなものです。
１年間（昨年度）の売上実績情報を記録した
テーブル「累積売上_fx」の内容を
保存用の別テーブルに転記し、
テーブル「累積売上_fx」の内容を
新年度に備えてすべて削除する。
《注意》
テスト用テーブルの準備
テーブル「累積売上_fx」には既にいくつかのレコードが記録されているため、ここでは同じ内容を保存したテスト用のテーブル「累積売上_dmy」を作って処理を試すことにします。
・テーブル「累積売上_dmy」が存在しない場合
既存テーブルの内容をそのまま保持した新しいテーブルを作るには、SELECT INTO文を使います。
クエリアナライザで以下のSQLを実行してください。
SELECT * INTO 累積売上_dmy FROM 累積売上_fx
今回のサンプルに含まれているクエリファイル「累積売上_dmy新規作成.sql」に、上記SQLと同じ内容が記述してあるので、クエリアナライザでそれを開いて実行しても構いません。
・テーブル「累積売上_dmy」が存在する場合
もし、データベースにテーブル「累積売上_dmy」が存在している場合は、上記のSQLを実行するとエラーになります。その場合、以下のSQLを実行して一旦「累積売上_dmy」を削除してから、新規に「累積売上_dmy」を作成しなければなりません。
「累積売上_dmy」を削除するには、以下のSQLを実行します。
DROP TABLE 累積売上_dmy
今回のサンプルに含まれているクエリファイル「累積売上_dmy削除・作成.sql」には、「累積売上_dmy」の削除と新規作成のためのSQL（上記の２つのSQL）が記述してあるので、クエリアナライザでそれを開いて実行しても構いません。 |
- ２段階の処理 -
では、ストアドプロシージャを作ってみましょう。
上記の処理を実行するためには、次の手順を採る必要があります（テスト用のテーブル「累積売上_dmy」を使うという前提で説明します）。
1.テーブル「累積売上_dmy」の内容を新規テーブル「累積売上_old」に転記する
SELECT * INTO 累積売上_old FROM 累積売上_dmy
2.テーブル「累積売上_dmy」の内容をすべて削除する
DELETE 累積売上_dmy
テーブル「累積売上_dmy」は、新年度に備えて内容（記録されているレコード群）を削除するだけであって、テーブルそのものは削除しません。
- ストアドプロシージャの追加 -
上記２つのSQLを実行するストアドプロシージャは、以下のコードを実行すれば出来上がります。
Create Procedure YearlyTransfer
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
DELETE 累積売上_dmy
クエリアナライザで上記のソースを実行すれば、データベースにストアドプロシージャ“YearlyTransfer”が追加されます。なお、前回も説明したようにCreate Procedure命令を実行する前に、対象データベースに必ず“db1001ya”を選択しておいてください。
以下のように、Create Procedure命令の前に“Use db1001ya”の１行を挿入して、対象データベースを明示しても構いません。
Use db1001ya
Create Procedure YearlyTransfer
As
SELECT * INTO 累積売上_old FROM 累積売上_dmy
DELETE 累積売上_dmy
- ストアドプロシージャの実行 -
実行したら、クエリアナライザの右ペインに表示されているオブジェクトブラウザで「db1001ya」のツリーを展開し、「ストアドプロシージャ」を選択して[F5]キーを押すか、右クリックしてコンテキストメニューから「最新情報に更新」を選択してみましょう※1。
その後、「ストアドプロシージャ」左側の[+]をクリックしてツリーを展開すれば、リストに“YearlyTransfer”が追加されています。
ストアドプロシージャ“YearlyTransfer”を実行してみましょう。EXECUTE（または“EXEC”と略）を使います。
EXECUTE YearlyTransfer
実行すると、
(xx 件処理されました)
というメッセージが２回表示されます※2。
これは、最初のSQL文
SELECT * INTO 累積売上_old FROM 累積売上_dmy
でテーブル「累積売上_dmy」のレコードを「累積売上_old」に転記した処理のメッセージに続けて、次のSQL文
DELETE 累積売上_dmy
でテーブル「累積売上_dmy」のレコードを削除した処理のメッセージが表示されるためです。
※1 オブジェクトブラウザが表示されていない場合は、メニューから「ツール」→「オブジェクトブラウザ」→「表示／非表示」を選択します
※2 “xx”の箇所にはテーブル「累積売上_dmy」に記録されているレコード数が表示されます。サンプル・データベースには29件のレコードが記録されていますが、必ずしも同じ値である必要はありません
- 処理結果の確認 -
結果を確認してみましょう。クエリアナライザ右ペインの「オブジェクトブラウザ」で「db1001ya」の「ユーザーテーブル」を選択して[F5]キーを押し、最新の情報に更新します（先ほど「ストアドプロシージャ」の項目で行った操作と同じです）。
その後、「ユーザーテーブル」左側の[+]をクリックしてツリーを展開すると、「累計売上_old」というテーブルが追加されています。
これを右クリックしてコンテキストメニューの一番上にある「開く」を選択すれば、これまで「累積売上_dmy」に記録されていた内容（販売記録）が表示されます。
同じリストから「累積売上_dmy」を開いてみましょう。こちらは中身がすべて削除されています。
 |
|
|
