Wiki – ストアドプロシージャ

 

ストアドプロシージャとは

ストアドプロシージャ(stored procedure)とは、データベースに対する一連の処理手順を一つの手続きにまとめ、DBMS(データベース管理システム)に保存したものです。この「DBMSに保存した」というのが結構重要なポイントで、クエリのたびに長々としたSQLをDBMSに発行する必要がなく、ストアドプロシージャを呼び出す命令だけで一連の処理をこなすことが出来ます。

また、一般的なプログラムのサブルーチンや関数のように、クライアントから引数を渡したり、クライアントに処理結果を返したりすることもできます。

ストアドプロシージャは中間コードとして即実行できる形でデータベースサーバに保存されるため、クライアントから呼び出し命令を一回送信するだけで一連の処理を実行でき、構文解析や機械語変換処理も事前に行われているため、その都度SQLを発行するよりもオーバーヘッドが少なく、処理時間の軽減にもつながるのもメリットです。

なお、戻り値を返すストアドプロシージャのことをストアドファンクションと呼ぶことがあります。

 

 利点欠点

ストアドプロシージャを利用することは、多くの場合メリットとなりますが、場合によってはデメリットになることもあります。

以下はメリットの例です。

  • ひとつの要求で複数のSQL文を実行できる(ネットワークに対する負荷を軽減できる)
  • あらかじめ構文解析や内部中間コードへの変換をすませるため、処理時間が軽減される
  • データベーストリガと組み合わせることで、複雑なルールによるデータの参照整合性保持が可能になる。簡単に言えば、アプリケーション側にロジックを持たずとも、データベース内のデータのつじつまが合うようにすることができる。
  • JAVA等のホスト言語とSQL文が確実に分離されソースコードの見通しが良くなること、また、Webサイトなど運用中でもストアドプロシージャの入れ替えによる修正が可能なため保守性に優れる。

ストアドプロシージャを多用することによるデメリットには以下のようなものがあります。

  • データベース製品ごとに、記述する構文の規約がSQL/PSM規格との互換性が低いため、コード資産としての再利用性が悪い。
  • ビジネスロジックの一部として利用する場合、業務の仕様変更に際して、外部のアプリケーションとともにストアドプロシージャの定義を変更する必要がある。このとき、よけいな手間や変更ミスによる障害を発生させる可能性がある。

※上記の説明では、互換性の問題による再利用性の低さをデメリットとしていますが、互換性を考慮しなければ、機能としての再利用性は高まります。

 

基本構文

詳細はDBMSによって異なりますが、基本的にストアドプロシージャは「CREATE PROCEDURE ~」という構文を使い、ストアドファンクションは「CREATE FUNCTION ~」を使います。

 

トリガー

トリガーとは、データベースに対する何らかの操作を検知して呼び出されるストアドプロシージャのことです。ストアドプロシージャなのですが、クライアント側から明示的に呼び出すのと違って、なんらかの操作に連動して(フック)自動的に呼び出されるところが少々異なります。

 

まとめ

ストアドプロシージャとストアドファンクションとは、以下のようなもののことをいいます。

ストアドプロシージャ 一連の処理を記述したSQL版のサブルーチンのようなもの
ストアドファンクション プロシージャとの差異は戻り値を返すところで、それ以外はほとんど同義