ロジックの格納

この章では、将来使用するために SQL プロシージャを格納する方法とトリガーを作成する方法について説明します。ストアドビューの詳細については、第 16 章データの取得を参照してください。

以下の項目について説明します。

ストアドプロシージャを使用して、論理的に関連付けされたプログラミングステップを一般的なプロセスにグループ化し、次にそのプロセスを 1 つのステートメントで呼び出すことができます。また、パラメーターを渡すことによって、異なる値でこのプロセスを実行できます。

SQL ストアドプロシージャを呼び出すと、ホスト言語のプログラムと SQL エンジンとの間で内部的な通信を行うことなく、プロシージャ全体が実行されます。ストアドプロシージャを単独で呼び出したり、ほかのプロシージャまたはトリガーの本体の一部として呼び出すことができます。トリガーの詳細については、SQL トリガーを参照してください。

ストアドプロシージャ内の SQL 変数ステートメントを使用して、ステートメントからステートメントへ値を内部に格納することができます。これらのステートメントの詳細については、SQL 変数ステートメントを参照してください。

ストアドプロシージャ内の SQL 制御ステートメントを使用して、プロシージャの実行フローを制御することができます。これらのステートメントの詳細については、この章の後半の SQL 制御ステートメントを参照してください。

ストアドプロシージャと位置付け更新の例を次に示します。

ストアドプロシージャを定義するには、CREATE PROCEDURE ステートメントを使用します。

ストアドプロシージャ名の最大サイズは 30 文字です。パラメーターリストの両側には小かっこを付ける必要があり、パラメーター名は有効な SQL 識別子とすることができます。

ストアドプロシージャは、辞書内で一意名を持っていなければなりません。

CREATE PROCEDURE ステートメントの構文の詳細については、『SQL Engine Reference』で CREATE PROCEDURE を参照してください。

ストアドプロシージャを定義するには、CREATE ステートメントを使用します。

すべてのパラメーターに値を定義する必要があります。CALL ステートメントで関連する引数を使用するか、または CREATE PROCEDURE ステートメントの関連するデフォルトの句を使用して、パラメーターに値を指定することができます。CALL ステートメント内のパラメーターの引数値は、関連するデフォルト値に優先します。

以下の 2 つの方法のいずれかで、CALL ステートメントに呼び出し値を指定できます。

•場所引数－プロシージャが作成されたときのリスト内のパラメーターの序数の位置に基づいて、パラメーター値を暗黙に指定することができます。

•キーワード引数－値が割り当てられているパラメーターの名前を使用して、パラメーター値を明示的に指定できます。

場所またはキーワードの引数リストでは、パラメーター値を 2 回指定できません。同一呼び出しで場所引数とキーワード引数の両方を使用する場合、キーワード引数は場所引数を介して値を受け取るパラメーターを参照することはできません。キーワード引数を使用する場合、同じパラメーター名を 2 度使用することはできません。

CALL ステートメントの構文の詳細については、『SQL Engine Reference』で CALL を参照してください。

ストアドプロシージャを削除するには、DROP PROCEDURE ステートメントを使用します。

このステートメントの構文の詳細については、『SQL Engine Reference』で DROP PROCEDURE を参照してください。

SQL 変数ステートメントを使用して、ステートメント間で内部的に値を格納することができます。SQL 変数ステートメントには、以下のステートメントがあります。

これらのステートメントはストアドプロシージャ内で使用できます。

ストアドプロシージャ内で定義する SQL 変数は、プロシージャ所有の変数です。その適用範囲は、SQL 変数が定義されるプロシージャです。したがって、そのプロシージャ内でしかその変数を参照できません。プロシージャが別のプロシージャを呼び出す場合、呼び出し側プロシージャのプロシージャ所有の変数は被呼び出し側プロシージャで直接使用できません。その代わり、その変数をパラメーターで渡す必要があります。同じストアドプロシージャでプロシージャ所有の変数を 2 回以上宣言することはできません。

ストアドプロシージャの本体が複合ステートメントの場合、そのプロシージャで宣言された SQL 変数名は、そのプロシージャのパラメーターリスト内のパラメーター名と同じにすることはできません。複合ステートメントの詳細については、複合ステートメントを参照してください。

代入ステートメントは、SQL 変数の値を初期化または変更します。値の式は、定数、演算子、この SQL 変数またはほかの SQL 変数に関連する計算された式とすることができます。

値の式は、SELECT ステートメントとすることもできます。

このステートメントの構文の詳細については、『SQL Engine Reference』で SET を参照してください。

ストアドプロシージャの本体内でのみ制御ステートメントを使用できます。これらのステートメントは、プロシージャの実行を制御します。制御ステートメントは以下のとおりです。

複合ステートメントは、ほかのステートメントをグループ化します。

BEGIN
DECLARE :NumEnrolled INTEGER;
DECLARE :MaxEnrollment INTEGER;

DECLARE :failEnrollment CONDITION
FOR SQLSTATE '09000';

SET :NumEnrolled = (SELECT COUNT (*)
FROM Enrolls
WHERE Class_ID = classId);

SET :MaxEnrollment = (SELECT Max_Size
FROM Class
WHERE ID = classId);

IF (:NumEnrolled >= :MaxEnrollment) THEN
SIGNAL :failEnrollment ELSE
SET :NumEnrolled = :NumEnrolled + 1;
END IF;

ストアドプロシージャまたはトリガーの本体内で複合ステートメントを使用できます。トリガーの詳細については、SQL トリガーを参照してください。

ほかの複合ステートメント内に複合ステートメントをネストできますが、最も外側の複合ステートメントだけに DECLARE ステートメントを取り込むことができます。

複合ステートメントの構文の詳細については、『SQL Engine Reference』で BEGIN [ATOMIC] を参照してください。

IF ステートメントは、条件の真の値に基づいて条件付き実行を行います。

IF ステートメントの構文の詳細については、『SQL Engine Reference』で IF を参照してください。

LEAVE ステートメントは、複合ステートメントまたは Loop ステートメントから離れることによって実行を続けます。

LEAVE ステートメントは、ラベル付き複合ステートメント内またはラベル付き Loop ステートメント内に現れるはずです。LEAVE ステートメントからのステートメントラベルは、LEAVE を含むラベル付きステートメントのラベルと同じでなければなりません。このラベルは、対応ラベルと呼ばれています。

メモ：複合ステートメントには、Loop ステートメントを取り込むことができます。Loop ステートメントを埋め込むことができるので、LEAVE ステートメントのステートメントラベルは埋め込みループのラベルまたはストアドプロシージャの本体のラベルに一致します。

LEAVE ステートメントの構文の詳細については、『SQL Engine Reference』で LEAVE を参照してください。

LOOP ステートメントは、ステートメントブロックの実行を繰り返します。

LOOP
FETCH NEXT cRooms INTO CurrentCapacity;

IF (:counter = :NumRooms) THEN
LEAVE FETCH_LOOP;
END IF;

SET :counter = :counter + 1;
SET :TotalCapacity = :TotalCapacity + :CurrentCapacity;

SQL ステートメントリスト内の各ステートメントがエラーなく実行され、また、PSQL に LEAVE ステートメントが発生しないか、ハンドラーを呼び出す場合は、LOOP ステートメントの実行が繰り返されます。LOOP ステートメントは、与えられた条件が真である間に実行が継続されるという点で、WHILE ステートメントに似ています。

LOOP ステートメントに開始ラベルがある場合、このステートメントはラベル付き LOOP ステートメントと呼ばれます。終了ラベルを指定する場合、そのラベルは開始ラベルと同じでなければなりません。

LOOP ステートメントの構文の詳細については、『SQL Engine Reference』で LOOP を参照してください。

WHILE ステートメントは、指定された条件が真である間にステートメントブロックの実行を繰り返します。

PSQL は、ブール値の式を評価します。その値が真であれば、PSQL は SQL ステートメントリストを実行します。SQL ステートメントリスト内の各ステートメントがエラーなく実行され、また、LEAVE ステートメントが発生しない場合は、Loop ステートメントの実行が繰り返されます。ブール値の式が偽または不明である場合、PSQL は Loop ステートメントの実行を終了します。

WHILE ステートメントに開始ラベルがある場合、このステートメントはラベル付き WHILE ステートメントと呼ばれます。終了ラベルを指定する場合、そのラベルは開始ラベルと同じでなければなりません。WHILE ステートメントの構文の詳細については、『SQL Engine Reference』で WHILE を参照してください。

トリガーは、データベースに対して一貫性のある規則を強制するために、テーブルに定義されたアクションのことです。トリガーは、ユーザーがそのテーブルで SQL データ変更ステートメントを実行するときに、DBMS に対して実行するアクションが適切かどうか確認する辞書オブジェクトです。

トリガーを宣言するには、CREATE TRIGGER ステートメントを使用します。

トリガー名の最大サイズは 30 文字です。

トリガーを削除するには、DROP TRIGGER ステートメントを使用します。

トリガーを直接呼び出すことはできません。トリガーは、関連するトリガーを持つテーブル上の INSERT、UPDATE または DELETE アクションの結果として呼び出されます。これらのステートメントの構文の詳細については、『SQL Engine Reference』で以下のトピックを参照してください。

メモ：トリガーが回避されるのを防止するため、PSQL はトリガーを含むデータファイルをバウンドデータファイルとして区別します。これにより、Btrieve ユーザーのアクセスが制限され、PSQL データベースでトリガーを発生させるアクションが実行されないようにします。詳細については、『SQL Engine Reference』を参照してください。

トリガーは所定のイベントに対して自動的に実行するので、いつどのような順序でトリガーを実行するかを指定できることが大切です。トリガーを作成するときはその時機と順序を指定します。

トリガーに関連するイベントが発生すると、そのトリガーはイベントの前または後にトリガーを実行しなければなりません。たとえば、INSERT ステートメントがトリガーを呼び出した場合、トリガーは INSERT ステートメントの実行前または後に実行しなければなりません。

トリガーアクションの時機として BEFORE または AFTER を指定してください。トリガーアクションは、行ごとに 1 回実行します。BEFORE を指定すると、トリガーは行オペレーションの前に実行します。AFTER を指定すると、トリガーは行オペレーションの後に実行します。

メモ： PSQL は、RI の制約を設定することによってはトリガーを呼び出しません。また、RI の制約によってシステムがテーブル上でカスケードされた削除を行う可能性もある場合、テーブルでは DELETE トリガーが定義されないことがあります。

イベントが同じ指定時機に複数のトリガーを呼び出す場合があります。たとえば、INSERT ステートメントは、その実行後に実行するよう定義された複数のトリガーを呼び出すことができます。これらのトリガーは同時に実行できないので、トリガーの実行順序を指定する必要があります。

以下の CREATE TRIGGER ステートメントが 1 番を指定しているので、テーブルに対して定義された以降の BEFORE INSERT トリガーはすべて 1 より大きい一意の番号を得なければなりません。

符号なし整数で順序の値を指定しますが、この整数はそのテーブル、時間およびイベントに対して一意でなければなりません。現在の順序に新しいトリガーを追加する可能性がある場合は、これに適応できるように、番号に空きを残しておくようにします。

トリガーの順序を指定しないと、トリガーはそのテーブル、時間およびイベントに対して現在定義されているトリガーの順序の値よりも大きい一意の順序の値で作成されます。

トリガーアクションは、行ごとに 1 回実行します。トリガーアクションの構文は以下のとおりです。

トリガーアクションに WHEN 句が含まれている場合、ブール式が真であれば、トリガーされた SQL ステートメントが実行します。式が真でなければ、トリガーされた SQL ステートメントは実行しません。WHEN 句が存在しないと、トリガーされた SQL ステートメントは無条件で実行します。

トリガーされた SQL ステートメントは、ストアドプロシージャ呼び出し（CALL procedure_name）などの単一の SQL ステートメントか、複合ステートメント（BEGIN...END）とすることができます。

トリガーアクションで、古い行イメージの列（DELETE または UPDATE の場合）または新規の行イメージの列（INSERT または UPDATE の場合）を参照しなければならない場合は、以下のようにトリガー宣言に REFERENCING 句を挿入する必要があります。