ストアドプロシージャの作成自体は非常に簡単です。また、作成したストアドプロシージャを削除するのも簡単です。
- 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命令を使って終了コードを返すのですが、特に指定しない場合は自動的に０が返されます。
- 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”のテーブルを表示するストアドプロシージャとして、以下の２つを作ってみましょう。
・マスターテーブル名を表示する処理
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”となっていますが、プロシージャはそれぞれ独立しているため相互の影響はありません。
 |
|
|
