データベース千夜一夜第27回

ストアドプロシージャ(1)~ストアドプロシージャの基礎 長谷川裕行
有限会社 手國堂

ストアドプロシージャの作成と削除

ストアドプロシージャの作成自体は非常に簡単です。また、作成したストアドプロシージャを削除するのも簡単です。


- Create Procedure命令 -

ストアドプロシージャは“Create Procedure”命令によって作成します。作成されたストアドプロシージャは、EXECUTE命令に続けてその名前を示すことで実行できます。これを、関数と同じように「呼び出す」と表現します。

但し、これまでに紹介したSQL関数のようにSQL文の中に埋め込むのではなく、ストアドプロシージャは単独のSQLとして呼び出し、一定の処理を実行して終了します。

Create ProcedureはSQL Serverに備わっている拡張されたSQL──Transact SQLの命令で、以下のような書式で用います。

  Create Procedure <名前>
  As
    <処理>
“Create Procedure”命令は“Create Proc”と略しても構いません。

<名前>で示すストアドプロシージャ名は、データベース内で一意でなければなりません。SQLの命令など既に存在する識別名を使うとエラーになります。

Asに続けて<処理>の箇所にSQL文を記述します。ここに記述するのは、これまでに紹介してきたSELECT、DELETE、INSERT、UPDATEといった命令を使ってデータベースを操作する処理です。

本来は、処理の最後にreturn命令を使って終了コードを返すのですが、特に指定しない場合は自動的に0が返されます。


- Drop Procedure命令 -

作成したストアドプロシージャを削除するには、“Drop Procedure”命令を使います。書式は以下の通りです。

  Drop Procedure <名前>
先に作成した“GetDbOutline”を削除するなら、

  Drop Procedure GetDbOutline
とします※2

作成したストアドプロシージャを書き換える場合も、まずDrop Procedureで既存のストアドプロシージャを削除した後、Create Procedure命令を使って同じ名前で処理内容の異なるストアドプロシージャを登録します。

削除せずに既存の名前でストアドプロシージャを登録しようとすると、「データベースにオブジェクト名 'xxxxxxxx' が既に存在します」というメッセージが表示され、登録はできません。

なお、Drop命令はDrop Tableとすればテーブルを、Drop Viewとすればビューを削除するなど、データベースを構成する様々なオブジェクトに対して使えます。間違って他のオブジェクトを削除しないように注意してください。

※2 これを実行するとせっかく作ったストアドプロシージャが削除されるので、実行しないようにしましょう


- 説明用のストアドプロシージャ -

先に示した例では、サンプル・データベース(db1001ya)の概要を示すだけでした。あってもなくてもいいような無意味な処理に思えますが、たくさんのデータベースを抱えている場合、名前だけでは「それが何をするためのデータベースなのか?」判別できないこともあります。

そのような場合のために、データベースの概要を取得できる処理としてGetDbOutlineプロシージャを用意しておくのです。他にも、データベース内の主要なテーブルを表示する処理を作っておけば、Enterprise Managerを起動することなく、クエリアナライザや開発環境で作った簡単なアプリケーションで、データベースの役割やその中のテーブルを知ることができます。


- ストアドプロシージャの作成と実行 -

データベース“Db1001ya”のテーブルを表示するストアドプロシージャとして、以下の2つを作ってみましょう。

・マスターテーブル名を表示する処理

  Create Procedure GetMasterTables
  As
    SELECT '基本情報, 商品_mr, 在庫_mr, 得意先_mr, 仕入先_mr'
    As Result
・取引テーブル名を表示する処理

  Create Procedure GetSlipTables
  As
    SELECT '売上ヘッダ, 売上明細, 仕入ヘッダ, 仕入明細, 累計売上_fx, 累計仕入_fx'
    As Result
それぞれ、クエリアナライザでストアドプロシージャを作成した上で、実行して結果を確認してみてください。実行するには、以下のSQLを用います。

  EXECUTE GetMasterTables
  EXECUTE GetSlipTables
なお、返されるフィールド名はどれも“Result”となっていますが、プロシージャはそれぞれ独立しているため相互の影響はありません。





トップページ
ストアドプロシージャの基本
ストアドプロシージャの作成と削除
Create Procedure命令
Drop Procedure命令
説明用のストアドプロシージャ
ストアドプロシージャの作成と実行
ストアドプロシージャ向きの処理
ストアドプロシージャ作成ウィザード
あとがき
Copyright © GrapeCity inc. All rights reserved.