13. ストアドプロシージャ (SQL 非標準)

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

ストアドプロシージャは、データベースに対する一連の処理を一つのプログラムにまとめ (PROCEDURE)、データベース管理システム (RDBMS) に保存 (STORE) したものです。複雑な SQL 文の呼び出しを、論理的に一つの処理単位にまとめて、簡単にその名前で呼び出せるようになっています。一つのプロシージャには、複数の SQL 文が含まれていたり、繰り返しや条件分岐などの制御構造をもつこともあります。また、引数をとって処理をしたり、処理結果を返すこともできます。

ストアドプロシージャを利用することにより、ユーザは必要に応じてその名前と引数を指定して呼び出すだけでよく、データベースに対する複雑な処理は RDBMS に任せてしまうことができます。つまり、ストアドプロシージャを利用することにより、次のようなメリットがあるといえます。

1. RDBMS に一つずつ SQL 文を発行する必要がなくなる
2. ネットワーク上のトラフィックを削減できる
3. あらかじめ処理内容が RDBMS に登録され、構文解析や機械語への変換が済んでいるため、処理時間が軽減される

ストアドプロシージャは SQL で記述されますが、SQL92 ではストアドプロシージャについて明確に定義していません。その結果、それぞれの RDBMS 製品ではストアドプロシージャを記述するための SQL を独自の拡張 SQL として定義しています。従って、ストアドプロシージャについて、RDBMS 間で互換性は失われることになりました。ここでは、ORACLE と PostgreSQL について説明することとします。ORACLE では PL/SQL という拡張 SQL を用いてストアドプロシージャを記述します。一方、PostgreSQL ではユーザ定義関数を利用することでストアドプロシージャに近い機能を実現します。

PostgreSQL で利用されるユーザ定義関数について簡単に説明しておきます。ユーザ定義関数に使用される関数には次の 4つの関数があります。

1. SQL 関数
2. PL/Tcl 関数
3. PL/pgSQL 関数
4. C 関数

SQL 関数は、PostgreSQL で実装されている SQL のみで表現される関数です。SQL だけで実現できる簡単な処理に向いています。PL/Tcl 関数は、Tcl 言語を利用した関数で、PL/pgSQL がある今となってはあまり利用されていないのが現状です。PL/pgSQL 関数は、PL/pgSQL 言語で記述される関数で、SQL 関数よりもより複雑な動作を定義することができます。C 関数は C 言語で記述する関数で、この 4つの関数の中で最も柔軟な動作を記述することができます。ただ、コンパイルが必要なため動作環境に依存したり、データベースにアクセスする際に、SPI というインタフェースを介さなければならないといったデメリットもあります。

13.2. ストアドプロシージャの基本構文

ここではストアドプロシージャを作成、実行、削除するための構文について説明します。

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

ストアドプロシージャを作成するには、次の基本構文を使用します。

  CREATE [ OR REPLACE ] PROCEDURE ストアドプロシージャ名
         [ (引数名 データ型),... ]
  IS     [ 変数名 データ型,... ]
  BEGIN
         処理内容
  END ;

  CREATE FUNCTION 関数名 ( [ 引数のデータ型,... ] )
  RETURNS [ SETOF ] 返り値のデータ型
  AS
  '
  [ラベル]
  [ DECLARE 変数名 データ型 ... ]
  BEGIN
         処理内容
  END ;
  '
  LANGUAGE 'sql' ;

ORACLE から説明します。定義するストアドプロシージャ名を指定し、続けて引数名とそのデータ型を記述します。IS キーワードに続けて、ストアドプロシージャの中で使う変数名とそのデータ型を指定します。そして、BEGIN と END の間にストアドプロシージャの処理内容を記述します。OR REPLACE 句を指定すると、指定したストアドプロシージャ名が既に存在する場合に上書きできることを示します。返り値については何も記述する必要はありません。

使用する言語が SQL の場合ですが、PostgreSQL の CREATE FUNCTION 文では、関数名を指定した後、引数名とそのデータ型を指定します。引数をとらない関数の場合は、( ) の中は空にしておきます。RETURNS キーワードの後には、返り値のデータ型を指定します。ここで、SETOF 句を指定すると複数行を出力することができます。AS キーワードの後ろで、使用する変数名とそのデータ型を DECLARE キーワードを使用して指定します。そして、BEGIN と END の間に処理内容を記述します。一番最後には、LANGUAGE キーワードに続けて、使用する言語名を指定します。SQL, PL/Tcl, PL/pgSQL または C のいずれかを指定します。それぞれのキーワードは、「sql」「pltcl」「plpgsql」「c」です。

変数の宣言部分と処理内容の部分を合わせて、一つのブロックとしたとき、そのブロックにラベルを指定したい場合は、変数の宣言の前にラベルを指定します。使用する言語が、PL/SQL または PL/Tcl の場合は、それぞれの言語で処理内容を「' '」内に記述します。また、使用する言語名が C 言語の場合は、変数の宣言や処理内容をこの構文内に記述するのではなく、別のソースに記述します。この構文では、「' '」の中に処理内容を記述したファイルをコンパイルしてできたもののパスを記述します。

13.2.2. ストアドプロシージャの実行と削除

前節で定義されたストアドプロシージャ (PostgreSQL では関数) を実行するための基本構文と、削除するための基本構文について説明します。

ストアドプロシージャ (PostgreSQL では関数) を実行するための基本構文は次の通りです。PostgreSQL の関数については、基本構文というより基本的な使用方法について示しています。

  BEGIN
       [ EXECUTE ] ストアドプロシージャ名 [(引数,...)]
  END ;

  SELECT 関数名([引数,...]) ;

ORACLE では、BEGIN と END のキーワードの間にストアドプロシージャ名を記述することで、実行することができます。引数をとるストアドプロシージャの場合は、引数を ( ) の中に記述します。また、EXECUTE は書いても書かなくても効果は同じです。

PostgreSQL では、集約関数などと同じように、SELECT 文の出力として使用したり、WHERE 句の条件の中で使用したりします。引数をとる関数の場合は、引数を ( ) の中に記述します。引数がない場合は、関数名 ( ) としておきます。

ストアドプロシージャを削除する場合の基本構文は次の通りです。

  DROP PROCEDURE プロシージャ名 ;

  DROP FUNCTION 関数名([引数のデータ型]) ;

ORACLE の場合は、DROP PROCEDURE 文でプロシージャを削除します。PostgreSQL の場合は、DROP FUNCTION 文で関数を削除します。